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.

  • I run the select from ISQL a 1st time and it takes about 3 seconds.
  • I run the select from ISQL a 2nd time and it takes over 300 seconds (no inserts or updates between both runs). This is caused by the query plan switching from an index scan on one table to a table scan.

Any idea why the optimizer could behave like this?

Thanks Arthur

Using: SQL Anywhere 12.0.1

asked 18 May '14, 11:32

Arthur%20Hefti's gravatar image

Arthur Hefti
1668816
accept rate: 0%

edited 18 May '14, 11:33

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)?

(18 May '14, 14:53) Mikel Rychliski
Replies hidden

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

(19 May '14, 08:35) Arthur Hefti

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.

permanent link

answered 19 May '14, 07:22

Martin's gravatar image

Martin
8.6k119151237
accept rate: 14%

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.

permanent link

answered 20 May '14, 10:44

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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.

attached file copied from the Your Answer fields

(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...

It is not obvious how to do this from a comment. Attaching files to the opening question or to an Answer is more obvious.

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
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:

×248
×27

question asked: 18 May '14, 11:32

question was seen: 597 times

last updated: 22 May '14, 03:21