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