SQL Anywhere 12 performance

[former_SQLA_member1694909]

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

[Breck_Carter]

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

[VolkerBarth]

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

[Breck_Carter]

> 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