SQL Anywhere version 12.0.1.3797

I have a performance problem with one of the databases of our customers. The query has about 30 "left outer joins" to stored procedures. For this report about 10 tables are involved with about 4.000.000 rows per table.

Database is started on a server with 16Gb of internal memory. PageSize of the database is 4096.

Tested the report over and over again and it finished in 3 minutes. This is good enough.

After entering some data (about 100 records) retrieval of the report fails. After 30 minutes we shut down the program. In the stored procedures I rewrote the "joins" to "apply", the "apply" to a "lateral" etc. Reorganized the tables. Tried sa_flush_cache(), dropped and created the statistics. Nothing seems to work.

Looks like if I stop the database, drop and create the procedures I can retrieve the reports. As soon as new data is entered retrieval of this report fails. To find out what exactly happened I used in each procedure the message command to show the start and end time and found a procedure that messed up the performance.

This particular procedure if just something like "select sum( no_sold ) from ... where delivery_date > ... and delivery_date < ....)". There is an index on delivery date. Tried to use with(index()) and thought the problem was solved. But, the next day the report failed again.

How can I find out why this happens and how can I optimize this report so that it works in all cases.

Thanks Eric

asked 24 Nov '20, 02:48

ontsnapt's gravatar image

ontsnapt
957713
accept rate: 0%

edited 24 Nov '20, 09:23

The procedure profiler is an excellent resource for finding procedure Queries From Hell like this, but you've probably already found the culprit.

Chances are good that SQL Anywhere chooses a different plan for different executions of this query, and sometimes it chooses a Very Bad Plan.

This happens occasionally, perhaps 0.00001% of the time, which is quite often enough to ruin your day... folks only remember the one car accident, not the thousands of safe trips to the grocery store.

You can use the Database Profiler to watch for the Very Bad Plan, or do it yourself.

Here's a reading list: http://sqlanywhere.blogspot.com/2011/05/graphical-plan-reading-list.html

(24 Nov '20, 10:46) Breck Carter
1

Does the particular procedure when called on its own also show those varying run time? If so, comparing its query plans might help. (And sometimes I just "blindly" test whether intra-query parallelism (setting via max_query_tasks option) does make a difference...)

(25 Nov '20, 05:04) Volker Barth
Replies hidden

> setting via max_query_tasks option

Yes, of course! ...especially for SQL Anywhere 12!

See The New MaxBPs=768: Set MAX_QUERY_TASKS = '1' http://sqlanywhere.blogspot.com/2011/08/new-maxbps768-set-maxquerytasks-1.html

(25 Nov '20, 09:00) Breck Carter
Be the first one to answer this question!
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:

×263

question asked: 24 Nov '20, 02:48

question was seen: 137 times

last updated: 25 Nov '20, 09:02