Question Re: Query optimization issue in Technology Q&A

Query optimization issue

Former Member — Tue, 25 Jun 2013 05:42:59 GMT

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?
Here are attached exec plans (before and after my changes).

Note: before running this query, I created statistics on all involved tables.

Re: Query optimization issue

Former Member — Wed, 26 Jun 2013 03:58:29 GMT

to download your plans, 4shared.com demand registration. Is it possible to use another hosting?

Re: Query optimization issue

Former Member — Wed, 26 Jun 2013 04:25:42 GMT

Sorry about that, this is a new link on megafileupload

Re: Query optimization issue

MarkCulp — Wed, 26 Jun 2013 09:13:45 GMT

Note that you can always upload your plans to this site - just use the 'paperclip' icon to attach a file.

Re: Query optimization issue

jeff_albion — Wed, 26 Jun 2013 09:35:04 GMT

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.

The graphical plan with statistics, though more expensive to generate, provides the actual query execution statistics as monitored by the database server when the query is executed, and permits direct comparison between the estimates used by the optimizer in constructing the access plan with the actual statistics monitored during execution. Significant differences between actual and estimated statistics might indicate that the optimizer does not have enough information to correctly estimate the query's cost, which may result an inefficient execution plan.

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.

Re: Query optimization issue

Former Member — Wed, 26 Jun 2013 09:42:19 GMT

Thank you for your replay. I will recheck my queries, seem like I missed something.

Re: Query optimization issue

VolkerBarth — Wed, 26 Jun 2013 09:54:33 GMT

...if you have enough rep points (100, according to the FAQ), which John has not yet - or is that restriction obsolete?

Re: Query optimization issue

MarkCulp — Wed, 26 Jun 2013 10:51:36 GMT

Good point which I had forgotten. The restriction is still at 100 reps.