Migration to ASE15 - How to remove the compatibility mode in ASE15? Ideas …

[Former Member]

Documentation

http://scn.sap.com/docs/DOC-43406

http://scn.sap.com/docs/DOC-36047

Context:

When testing the performance of our most important stored procedures (SP) to migrate from ASE12.5 to ASE15.5, we realized that their performance were worse with the new ASE15.5.

Because of a lack of time and resources (rewriting 4000+ stored procedures is not easy and focusing on the most important SPs is also time-consuming…), we migrated to ASE15.5 in activating the server-wide compatibility mode (compatibility mode ON, statement cache OFF, literal autoparam OFF) to keep the old ASE12.5 way of executing SPs. This mode did not allow us to benefit from all the new ASE15 features (such as enabling the statement cache that improves the query performance and boost the replication to an ASE replica) but at least the ASE performance was as good as before.

After reading the Sybase documentation (that basically says “update your statistics” even for non-indexed columns), running a lot of tests (comparing text query plans with different formatting and graphical query plans in InteractiveSQL), opening Sybase cases, I realized the importance of the remark “the ASE optimizer has been re-written in ASE15” when updating the statistics of all the columns of all the tables in lots of my SPs would not help the optimizer to pick the same index as in ASE12.5 or giving more procedure cache would not improve the performance at all.

To summarize, we have 2 situations:

• We are lucky and your query plan in ASE15 is almost the same as in ASE12.5 and updating your statistics will help the optimizer to perform as efficiently as before
• We are not lucky and the optimizer will have a completely different query plan: in that case, you have to review your code and update your statistics

So how to disable the server-wide compatibility mode to use the ASE15 “native mode”?

My current method:

1. Use MDA tables or application tools to determine the most used and/or the most important SPs => your Top 20 SPs
2. Use QPTune to identify the longuest queries in your ‘Top 20’ SPs. Pay attention to sub-SPs.
3. Identify the missing statistics by using QPTune or the option “set option show_missing_stats on”
4. If missing statistics, update the statistics to see if the performance are better in native mode (out of 11 analyzed SPs it only works once for me)
5. If no improvement, try to use different optimization level (option “set plan optlevel ase_current” worked for me)
6. If no improvement, it is time to re-think the code and test it with compatibility mode off. You can start by identifying ways to use less temporary tables. One of the most efficient ways is to use SQL User-Defined Functions (UDF). They are fast and help to simplify the code. Be aware of the fact your SPs won’t be able to run in “full compatibility mode” once you introduce UDFs in your code. Be aware of the UDFs have limitations (you cannot use non-deterministic functions, no delete/insert/update).
7. Rewriting the code can also involve simplifying the logics of queries, replace slow cursors with other techniques (while loop,…), optimizing the subqueries by testing the flattening (converting the subquery to a join) or materializing (storing the subquery results in a worktable) or review the ones with derived tables from the parent query
8. Other ideas….
9. All the SPs that we can not optimize are modified individually to be forced in compatibility mode (set compatibility_mode on)
10. Take some performance measurement BEFORE
11. Change the server-wide configuration setting “compatibility mode” to OFF
12. Take some performance measurement AFTER

I am still working on that project. I keep you posted on my new findings and thoughts.