• 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:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

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?
 
Master Rancher
Posts: 3677
40
  • 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
Master Rancher
Posts: 3677
40
  • 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
Master Rancher
Posts: 3677
40
  • 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!