Running SQLAnywhere 11.01, I'm trying to optimize a query. After some changes on initial query, I get an execution plan that seems to be faster (RunTime - old value = 1415.1, new value = 614.07), but when I run this query in my application, the total execution time increased from ~2 min to ~4 min.
Can anyone explain why this happens and can I do something to receive a better application run time for this query?
Note: before running this query, I created statistics on all involved tables.
asked 25 Jun '13, 05:42
The plans you have uploaded are only providing estimates for all operations, but especially in terms of both rows returned:
Before: RowsReturned 1.0689e+06
After: RowsReturned 2.4637e+05
and run times:
Before: RunTime 1415.1
After: RunTime 614.07
This is done before the engine actually goes to execute the queries and is not the actual run time in the engine. You need to instead gather a graphical plan with statistics, so that the engine actually runs the SQL statement and monitors how long each operation actually takes.
This is likely the cause of your run time cost discrepancy. Notably, the estimates for the amount of rows being returned is large (1068900 vs 246370), so I would start reviewing how many rows are actually being returned in each of these cases to see if the estimates are off or not.
answered 26 Jun '13, 09:35