Hi,
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. |
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. 1
Thank you for your replay. I will recheck my queries, seem like I missed something.
(26 Jun '13, 09:42)
JohnV
|
to download your plans, 4shared.com demand registration. Is it possible to use another hosting?
Sorry about that, this is a new link on megafileupload
Note that you can always upload your plans to this site - just use the 'paperclip' icon to attach a file.
...if you have enough rep points (100, according to the FAQ), which John has not yet - or is that restriction obsolete?
Good point which I had forgotten. The restriction is still at 100 reps.