Running SQLAnywhere 11.01, I am executing a relatively simple select statement against a narrow table with approximately 500 million rows, inner joined to some smaller tables (one with 3.5 million rows and the others with only about 2000 rows each,) using the iSQL client. The first query returns in less than 5 seconds. Subsequent queries that are similar, but for which I have changed one of the parameters, take approximately 40 seconds.

The parameter that I am changing is in one of the secondary tables, but the foreign key is part of the primary key as well as a clustered index on the primary table.

I have tried changing various connection parameters (optimization_goal, optimization_level, max_client_statements_cached and max_plans_cached). All of these are currently at the default. Changing them did not change the discrepancy between performance of my first statement execution and subsequent executions.

I wonder if there is an option I can set that might eliminate this difference in performance in favor of the speed I see on the first query run?

I'm happy to provide more information if that would be helpful.

asked 19 Jul '11, 15:40

jill's gravatar image

jill
106229
accept rate: 0%

I'd suggest to use graphical plans to compare the different runs.

(19 Jul '11, 16:04) Volker Barth
Replies hidden

Can you provide table structure and select the select statement in question? This would be helpfull.

(20 Jul '11, 02:29) Thomas Dueme...

In this case, it might be worth to compare "optimizer estimates only" plans with the "detailed statistics plans"

(20 Jul '11, 07:14) Martin

Thank you for the suggestion Volker.

The graphical plans between the two different executions don't look notably different. Overall statistics are the same within fractions of seconds, as is the general plan.

I changed the query to select based on the foreign_key value itself (happens to be a foreign key to a date dimension), which is part of the primary key and clustered index, rather than on the value from the secondary table. In addition to improving performance, the differences in the first and subsequent execution times disappeared. So it seems to have something to do with using a secondary table value in my selection criteria. The good news is that we're in the proof-of-concept phase so its no problem for me to adjust the schema accordingly.

permanent link

answered 19 Jul '11, 18:44

jill's gravatar image

jill
106229
accept rate: 0%

Check in your plans what is written next to the keyword "Selectivity" and also what is the difference between "estimated" "RowsReturned" and "Actual (Total)". I guess you will see a diskrepancy like estimated=1, actual=a huge number.

Such a diskrepancy is something which I have experienced, then the optimizer seems to just take into account the statistics of finding one foreign key in the primary table, which results in the join of secondary (or in your case probably the 3rd table) with primary first before the search criteria is applied to the join result. In contrast the optimal strategy would have been to apply first the search criteria to limit the secondary table matches and then do the join.

You might be able to influcence the behavior with a user estimate where ("criteria" , 0.00001) or something similar.

permanent link

answered 20 Jul '11, 07:34

Martin's gravatar image

Martin
9.0k130166257
accept rate: 14%

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:

×274

question asked: 19 Jul '11, 15:40

question was seen: 1,717 times

last updated: 20 Jul '11, 07:34