Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.

asked 25 Jun '13, 05:42

JohnV's gravatar image

JohnV
31114
accept rate: 0%

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

(26 Jun '13, 03:58) AlexeyK77
Replies hidden

Sorry about that, this is a new link on megafileupload

(26 Jun '13, 04:25) JohnV

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

(26 Jun '13, 09:13) Mark Culp
Replies hidden
1

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

(26 Jun '13, 09:54) Volker Barth

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

(26 Jun '13, 10:51) Mark Culp

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.

permanent link

answered 26 Jun '13, 09:35

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

1

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

(26 Jun '13, 09:42) JohnV
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×18
×4

question asked: 25 Jun '13, 05:42

question was seen: 2,921 times

last updated: 26 Jun '13, 10:51