Query optimization issue

[Former Member]

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.

[Former Member]

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

[Former Member]

Sorry about that, this is a new link on megafileupload

[MarkCulp]

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

[VolkerBarth]

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

[MarkCulp]

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

[jeff_albion]

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.

[Former Member]

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