Performance worse after upgrading from 9 to 11

[Former Member]

We have a commercial application that was running pretty well in SQL Anywhere 9.02. We have been upgrading our customers to 11.01 and have found that several (Crystal) reports, queries, and bulk updates are running far slower on the new engine. We discovered that setting max_query_tasks=1 helped considerably (a shame, that) but we still have lots of customer complaints related to performance degradation after upgrading.

I realize that this is a pretty general question, but we are wondering if there are any other db options besides Max_Query_Tasks we should be looking at, or if we should be making some kind of change to the way we structure our queries that would allow us to recover at least the level of performance we enjoyed with 9.02.

Seth Krieger www.sosoft.com

[Breck_Carter]

I am 100% sure you will get a LOT of help here, lots of suggestions from the folks in Engineering as well as fellow developers.

...but, alas, Engineering is located in Canada, and today is Canada Day, so you might have to wait a bit.

[Former Member · Accepted solution]

A followup on this question I posted several weeks ago...

As a couple of folks mentioned, the issue appears to be the optimization_goal option. All of our customer databases, including the empty one that goes out with new product, started with an SA 5.5 database that has been upgraded numerous time over the years, and retained legacy option setting "optimization_goal='First-row'". Changing this setting made a significant difference in performance on the majority of databases that we checked. At first I wasn't too sure, because I would have expected better performance on ALL the db's and I had one that remained quite slow regardless of the setting. Further investigation revealed that at least half the accounts in that db had 10 times as many transactions as is typical amoung our customers. As a result, it made sense that our stored procedure that calculates aging was running much slower as well.

I guess SA 9 was more tolerant of the First-row setting than SA 11, so it was not an issue before.

Thanks to everyone who offered suggestions!

• Seth

[Breck_Carter]

Check out the Application Profiling Wizard, in particular the Database Tracing component.

If you have trouble understanding how to use the Database Tracing Wizard (some people do, like me, and all my clients), the following article was written to help: Database Tracing In 50 Easy Steps.

Also check out the Index Consultant.

If you know of (or find) a query that is slow, look at the graphical plan. You can find some more information on graphical plans here.

If you want to monitor performance, you might try using the SQL Anywhere Monitor but some folks (like me, and my clients) prefer Foxhound... the SQL Anywhere Monitor doesn't claim to be a "performance monitor" and it isn't.

If you specify the dbsrv11 -zl option or call sa_server_option to turn on 'RememberLastStatement', Foxhound will display your Queries From Hell in the connection pane of the Monitor window... well, it will probably display your queries; see How do I get something to show up in the Last Statement column?

Foxhound also displays database and table-level "curiosities" which might address your question about other database options. The Application Profiling Wizard does some of that too: it "provides recommendations for improving database performance when it is complete".

Last but not least, you can hire a Performance and Tuning consultant, like someone from Sybase Professional Services ... or me 🙂

[MCMartin]

@Breck: Great Summary!

[Former Member]

Verify the setting of the "optimization_goal" database option.

http://dcx.sybase.com/index.html#1101/en/dbadmin_en11/optimization-goal-options.html

For the vast majority of workloads (particularly for reporting or bulk operations), the recommended configuration is to have the database option set to "All-rows" (default for newer engines) and then selectively override it using either the connection property or the FASTFIRSTROW query hint. Unfortunately, databases created on older engines (I believe it is v9 and older) have this option set to "First-row" by default, which usually causes the engine to choose plans with nested loop joins.

If you find that setting optimization_goal to "All-rows" changes the performance, then you will likely want to re-evaluate your max_query_tasks setting, since both of these options affect plan selection and therefore there may be sizable interplay between them.

[VolkerBarth]

FWIW, I guess it was in 8.0.2 when the default for optimization_goal was changed from "First-row" to "All-rows".

Don't know how v9 is affected as both versions had some "parallel" MRs...

[Former Member]

Volker is correct about that change happening at 8.0.2 instead of v9. Sorry, my mistake. Still worth verifying that option setting, but likely not the source of the problem here.

[VolkerBarth]

Claiming one's belief is never a mistake:)

It could still be the source in case the current database has been migrated from an older version to v9 in the past...

But needless to speculate without knowing the option's value:)

[MCMartin]

Just one thing we recognized during upgrade:

One disrupting change for us was in version 11, that the optimizer is using only the first 32 chars of varchar fields to estimate the frequency of a value (which influences if an index will be used and which strategy will be used) so for long char fields which have similar beginnings it might be usefull to drop the statistics so that the optimizer instead uses a general assumption instead of the histogram values.

In general my recommendation would be to compare the execution plans of affected queries between the two versions and identify the differences.