Stumped over performance issues after upgrading from ASA10 to ASA16

[Former Member]

I've been having a tough time with testing two interconnected ASA10 databases I've rebuild in version 16. On much of the testing I've done, the databases perform fine, even sometimes better than on 10. But certain queries run almost impossibly slow after the upgrade.

I've read about and modified the max_query_tasks values and rebuilt statistics to no avail. For example, a query that normally takes 6 minutes on the version 10 database has been running since 10:55AM and hasn't finished yet. So something is very off.

Is there something special I need to know about upgrading remotely connected databases? The machine I'm running the testing on has 32GB of RAM, and a 4Ghz processor, as well as both databases running on a solid state drive using the current EBF of ASA16.

[VolkerBarth]

Are the performance problems only related to queries involving remote data access? (Not that I'm aware of particular issues with v16 in that respect.)

As usually, when particular queries run worse in newer versions, it's helpful to store and compare the according plans (or supply them here)...

[Breck_Carter]

There is nothing special you need to know about upgrading.

With every major release of ever commercial RDBMS (not just SQL Anywhere) there comes the possibility that some former Angelic Queries will become Queries From Hell. You have upgraded across three major releases (10 to 11 to 12 to 16) so it is not surprising that some queries which worked well before now perform badly. The engineers do not (and cannot) know the specific needs of every singly query in existence, so in some cases changes are made that have unintended consequences.

You have two choices: Go back to version 10, or tackle each Query From Hell as a fresh problem: Obtain a Graphical Plan With Statistics and post the *.saplan file here. If it is easy, do that with both version 10 and 16, but only the version 16 plan is absolutely required.

There's lots in the Help about graphical plans, and on my blog sqlanywhere.blogspot.com

[Former Member]

The performance issues are not only with the remote database connections. I suspect Breck is right and we'll need to do some performance tuning before we can follow through with the upgrade.

I'm looking into obtaining the *.saplan file for some of these problem queries.

Thanks