Update: The attached files can be viewed with ISQL version 11 (don't try using ISQL from version 16)...
fast-job5.saplan - the fast query that uses JOB_NO=5
slow-job4.saplan - the slow query that uses JOB_NO=4
Some of the text below has been edited to add line breaks to reduce the need to scroll left-right.
Using SQL Anywhere 11.0.1:
-- first list CALL SA_FLUSH_CACHE(); SELECT AUTOKEY FROM EVENT_TRANS WHERE PROCEDURE_CODE='DR' AND JOB_NO=5 ORDER BY AUTOKEY takes 1.86 seconds to return 1,378 rows
Obvious difference is first plan uses a work table and sort but I don't know why...and also why it takes SO LONG!
This is a great example of one of my tips from the Performance and Tuning talk at the #SQLAnySummit2014. The optimization_goal option is set to 'First-row' for both plans. The optimizer is trying hard to pick a plan that will let you pick the first few rows quickly from a cursor, but not to fetch all of the rows through the cursor.
The "slow" plan has a first row returned in 0.22661 seconds while the "fast" plan is 2.5468 seconds. Since you are optimizing for first-row instead of all-rows, this the "slow" plan is actually better at meeting the goal.
This is also a nice example of a query where Optimization Method is "Bypassed then optimized". The statement is simple enough to qualify for "bypass costed" but the overall cost was found to be higher than the cutoff, so the query was optimized using the full blown optimizer.
answered 05 Nov '14, 22:40
Ivan T. Bowman
The fast plan uses the JOB index to find all the rows with JOB_NO=5, then sorts them to satisfy the ORDER BY AUTOKEY.
The slow plan does not use the JOB index, presumably because the higher percentage 0.062254% meant there would be a larger number of rows with JOB_NO=4 (which there were) and that it would be inefficient to put those rows in a work table and sort them. Instead, the slow plan processes all the rows in AUTOKEY order (the primary key index) and keeps the ones with JOB_NO=4.
You have already tried forcing the statistics to be updated, but that might not have helped because 0.062254% looks about right (6207 / 9366023 rows = 0.0663%).
You could also try using a very small (small is good) explicit selectivity estimate to force SQL Anywhere 11 to use the JOB index; the following code has not been tested:
SELECT AUTOKEY FROM EVENT_TRANS WHERE PROCEDURE_CODE='DR' AND ( JOB_NO=4, 0.00001 ) ORDER BY AUTOKEY
SQL Anywhere 11 also supports index hints in the FROM clause (FORCE INDEX etcetera).
Note, both queries are not identical because the JOB_NO in the WHERE clause is different (which you are aware of, of course, but it's worth stating...). As such, and because both values have different estimates, I would think it's not unexpected that different cardinalities can (and possibly should) lead to different access plans. Needless to say, they should not lead to poor performance in one case...
In case the PROCEDURE_CODE is always the same for the given JOB_NOs (if I understand the plan statistics correctly), it might be helpful to add a particular index just on the JOB_NO instead of relying on the index created on the FK.
The two queries probably run at different speeds because the plans are quite different.
The text plan doesn't give as much information as the graphical plan so it is difficult to tell exactly why the plans are different, but huge difference in statistics may have something to do with it...
( EVENT_TRANS.JOB_NO = 5 : 0.014711% Statistics )
( EVENT_TRANS.JOB_NO = 4 : 0.066249% Statistics )
The statistics may be wrong, or SQL Anywhere may be making a bad decision, or something else... please send me the Graphical Plan With Statistic *.saplan (the WITH STATISTICS part is very important) so I can post them for you, and we can all have a look.
...or, we can all keep guessing about what is happening :)
The following plans were sent to me Fri, Nov 7, 2014 at 5:07 AM
They can be viewed by opening them in V1 ISQL.
If you don't rename the files to *.saplan, you will have to click on "SQL Anywhere plan" when ISQL asks "what is this thing you want me to display?"