Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Replace nested counts with JOINs - is this possible?

 
alrem mashayekhi
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



Is there a way to replace/optimize this with JOIN?
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first thing to do would be to see whether there is a problem with the query you currently have.
No point optimising something that doesn't actually need it.
 
alrem mashayekhi
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
we have a database query similar to this which slows down (and i mean really slows down) on large volumes of data. just curious if there is still a way to optimize this by replacing it with JOIN statements
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
alrem mashayekhi wrote:we have a database query similar to this which slows down (and i mean really slows down) on large volumes of data. just curious if there is still a way to optimize this by replacing it with JOIN statements


Whatever style you use will still require (in essence) two counts.
You could:

Something along those lines (that won't work as written), but I wouldn't expect it to help too much.

In any case, my original question still stands.
Have you analysed your query and seen where the problem lies?
 
alrem mashayekhi
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
all we have tested are observable qualities like execution time on large data returns. what else could we look at?
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
alrem mashayekhi wrote:all we have tested are observable qualities like execution time on large data returns. what else could we look at?


Execution plans.
Those will show you what indexes are being used (or not used, which is just as important), and what sort of scans are being done on the tables. Depending on the db they may also show costs for different steps.
If indexes are being skipped then you might need to look at the cardinality on them.

All fairly standard things.
Otherwise you are simply guessing as to the cause.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic