SQL Anywhere performance

[eric_verhorstert]

SQL Anywhere version 12.0.1.3797

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

Database is started on a server with 16Gb of internal memory.

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

Now, some data (about 100 records) is entered and we can't retrieve the report anymore. After 30 minutes we shut down the program. In the stored procedures I rewrote the "joins" to "apply", the "apply" to a "lateral" etc. The problem stays the same.

Furthermore i tried sa_flush_cache(), dropped and created the statistics. Nothing seems to work.

Looks like if I stop the database I can retrieve the reports. As soon as new data is entered retrieval of this report fails until, the database is restarted.

Is there anything I can test? Would a upgrade to version 17 solve this problem?

[former_member630957]

Hi There

I am experiencing the exact same problem but we are running SQL Anywhere 17.

We have a few stored procedures that usually run in under 10 seconds. We have another program that processes and uploads data from csv files into the database, after running this program the stored procedure take roughly 3-7 min to complete until a restart of the DB. Sometimes a call to sa_flush_cache works to speed up the stored procedures again.

This slowdown also seems to happen after dbremote inserts content from another remote database....

Should we be running sa_flush_cache from the crontab or after each upload to the DB?

[chris_keating]

It might be that you do not have enough cache so pages useful for the queries are no longer in cache (the cache is evicts pages on a least recently used basis) although flushing the cache means no pages are in cache which is counter intuitive. Comparing the graphical plans with statistics of the problem procedures when running fast and then when running slow would likely provide guidance.

[former_member630957]

Hey Chris
Our DB is running on a server with 80GB of ram with 16 threads with 45GB assigned to only the DB and the rest for the Operating system all on Dorado Flash storage array, previously when we where on Sybase SQL Anywhere 12 32bit with 4GB of ram we did not have this issue, it only started after upgrading to SQL Anywhere 17 a month ago.

I don't think this is a caching issue

[chris_keating]

And what insights have the plans indicated?