I have an sql that is refusing to play fairly.
- tuned affected tables
- made sure I'm on the latest version (2016.1)
- got enough global buffers
- tried forcing the sql to use outliers correctly
Still nothing. here's the SQL
Its the consolidateID=0 that's the problem, It's a similar issue talked of in https://community.intersystems.com/post/global-summit-2016-find-and-fix-slow-query the issue talked of "closed cases" - in my case, I there are many more values of zero than other values (ratio 500,000:150)
I get a rowcount of 31 and , performance of 4.625 and global counts of just over 1,000,990 using that sql
the plan clearly shows it's dropping down the consolidateID index first, then the dealerid->routeid, then the statusCode but has to drop down 500,000+ consolidateID's and then find the dealers and stautsCodes.
I've looked at the indicies (mostly bitmaps) and the selectivities after tuning tables,
and all the selectivities suggest that the consolidationID (0.0357%) should be used last as the other selectivities are much higher.
I though I would follow the advice and change the wehere clause to
AND ((consolidateID = 0)) by using the double brackets but all that did getter a slightly better speed (probably because the data's in the buffers now) and the global count's went down to 1,000,831 (a drop of about 100) and it's still dropping down the consolidateID first (wrongly)
I use this SQL inside a fetch chunk of code and so to make it so much more efficient, I deleted the
AND ((consolidateID = 0)) clause and the fetched just the 30-50 rows and ignored those where consolidateID<>0
so of course, the question is:
how do I force the
AND ((consolidateID = 0)) to be used as the LAST filter, not the first