Is there a way to replace/optimize this with JOIN?
No point optimising something that doesn't actually need it.
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.
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 wrote:all we have tested are observable qualities like execution time on large data returns. what else could we look at?
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.