This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Journey To Enterprise Agility and have Daryl Kulak & Hong Li on-line!
See this thread for details.
Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Replace nested counts with JOINs - is this possible?  RSS feed

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



Is there a way to replace/optimize this with JOIN?
 
Rancher
Posts: 3595
39
  • 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
Rancher
Posts: 3595
39
  • 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
Rancher
Posts: 3595
39
  • 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
Boost this thread!