Win a copy of Kotlin for Android App Development this week in the Kotlin forum!
  • 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
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

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: 3887
45
  • 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: 3887
45
  • 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: 3887
45
  • 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.
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!