|
I have a Select from 3 tables with a subselect in the result set and in the where condition. Tables contain between 30 and 90'000 rows.
Any idea why the optimizer could behave like this? Thanks Arthur Using: SQL Anywhere 12.0.1 |
|
If the optimizer assumes, that more or less all rows of a table will be visited by the query it tends to use the table scan instead of index scan. It also prefers the table scan, when the data is not fitting into the database cache and IO is needed to get the data. Have you checked your cache misses for the second call? One other thing might be corrupt statistics: Create statistics on the tables and retry your statements. Cache hits are quite good. Recreating the statistics solves the problem but it will appear after a couple of days. I might have to disable automatic statistics for some tables and force the creation manually.
(19 May '14, 08:37)
Arthur Hefti
|
|
Without details showing the exact differences even the experts will just be hypothesizing as to what the cause may be. Please upload the graphical plans, as Mikel has requested, so we can offered informed opinions. I tried to upload the PDFs but the the dialog just states "No file chosen". Any limitation of file sizte? My question is more basically about why this can happen (gives quite troubles when trying to solve performance issues on customer installations) and not how to e.g. change the statement to make it faster.
(21 May '14, 00:38)
Arthur Hefti
There does seem to be an issue uploading files. I'm checking into that
(21 May '14, 13:54)
Nick Elson S...
There was a problem earlier but it seems to be working now. It is not obvious how to do this from a comment. Attaching files to the opening question or to an Answer is more obvious. Click on the 'paper clip' button (below), browse to the file on your system, after selecting click [Open], and then click [OK] even though you don't see the filename in that dialog. You can then past the information from the "Your answer" to your comment as below.
(21 May '14, 14:09)
Nick Elson S...
Replies hidden
So do send those plans but as .saplan files. Please do not embedded them into some other file or in a different format.
(21 May '14, 14:10)
Nick Elson S...
That's what I usually do to add a comment with an attachment (not really knowing the according Markdown syntax...): I usually start to add an answer and use that to upload the attachment with the according "paper clip" button. Then I copy the generated text to my new comment and cancel the answer. - Or I post an answer with the attachment and convert that into a comment lateron (which requires a particular number of rep points).
(22 May '14, 03:21)
Volker Barth
|
Hi Arthur, could you upload the detailed plans for each run? What happens the third time you run the query, does it return to running quickly? Does the problem still occur with plan caching turned off (
SET OPTION max_plans_cached = 0)?Mikel, 3rd time is as bad as 2nd time and it's not a cached plan issues as the problem persists whether I set them to 0 or try it with a 2nd connection. Problem are the bad table statistics