Performance Issue after database ugraded from SQL Anywhere 6 to SQL Anywhere 11

[Former Member]

We have upgraded our SQL Anywhere database from version 6.0 to 11.0.1. We have installed SQL Anywhere 11.0 on one of our client’s server. Now the reports that used to take a half a minute on Sybase 6 database takes approximately 2 to 3 minutes. They are also having slowness issue with data inserts and updates. We have verified this issue on their machine and noticed that they have more than 25 users connecting concurrently.

The machine configuration is as follows:

Operating system: Windows Server 2003 R2 CPU: Intel Xeon RAM: 24GB

We are using following parameters to start database server:

-n propserv -x tcpip{MyIP=192.168.10.10} -ti 480 -c 75p "D:dataAppDB1.db" "D:dataAppDB2.db" "D:dataAppDb3.db" "D:dataAppDB4.db" "D:dataAppDB5.db" "D:dataAppDB6.db" "D:dataAppDB7.db" "D:dataAppDB8.db" "D:dataAppDB9.db" "D:dataAppDB10.db" "D:dataAppDB11.db"

Please Note: we have not changed anything in frontend application. With the same set of concurrent users Sybase 6 used process results a half a minute.

[former_SQLA_member1694874]

That's pretty impressive moving from v6 to v11 and not having to do anything with the frontend, how is it connecting to Sybase ?

[Former Member]

We are using ODBC to connect to SQL anywhere database. We have to do few changes in SQL statements where we had used “=*” for left outer join.

[Former Member]

If you've replaced deprecated right outer join syntax with the words left outer join, you could unknowingly be working with a significantly larger result set.

[Breck_Carter]

Sharp eyes you have, young one!

[Former Member]

In the main, I think you should expect much improved performance with SQL Anywhere 11.0.1 versus ASA version 6.

I'm going to guess that you've done little if any performance analysis thus far on your freshly-reloaded 11.0.1 database.

My first question: what is the value of the PUBLIC setting of the optimization_goal option?

Secondly: can you post the graphical plan for a query whose performance you find problematic with 11.0.1? And can you post the text plan from version 6 for the same statement?

Aside: TSQL outer joins still exist in 11.0.1, but they are disabled by default because TSQL outer joins are deprecated. You can enable them with the tsql_outer_joins option.

[Former Member]

Thank you for providing more information to resolve speed issue.

We have used following statement to verify optimization_goal option and it returned “NULL”

SQL statement start

Select property(‘optimization_goal’)

SQL statement end

Please note, we are run report which used to run in about 5 seconds and now taking ~75 to 80 seconds. the application runs 4 stored procedures to generate data for the report. Due to this, I am unable to do graphical query plan. However, I used query plan option for one of the SQL statement present. The SQL statement ran fast in Sybase 6 and took time in Sybase 11. Please review following comments and advise:

This is the Query and Result of both query which run in Sybase Central 6 and Sybase Central 11.

1) Sybase Central 6 query and Output

select plan('select owner_id,tenant_id,vendor_id, t.transaction_amount,account_num,bank_account_number, transaction_source,transaction_type,transaction_date, ta.property_id,ta.debit_account_num, ta.credit_account_num,ta.amount from trnsactn as t,transamt as ta where ta.transaction_id = t.transaction_id and ta.property_id = 520');

Result -: 'Estimate 6143 I/O operations (best of 2 plans considered)x0AScan transamt AS ta using index tr1x0Afor rows where property_id equals 520x0A Estimate getting here 3208 timesx0A Scan TRNSACTN AS t using primary keyx0A for rows where TRANSACTION_ID equals transamt.transaction_idx0A Estimate getting here 3208 timesx0A'.

2) Sybase 11query and Output

select plan('select owner_id,tenant_id,vendor_id, t.transaction_amount,account_num,bank_account_number, transaction_source,transaction_type,transaction_date, ta.property_id,ta.debit_account_num, ta.credit_account_num,ta.amount from trnsactn as t,transamt as ta where ta.transaction_id = t.transaction_id and ta.property_id = 520');

Result -: '( Plan [ Total Cost Estimate: 0.49752, Costed Best Plans: 1, Costed Plans: 16, Optimization Time: 0.00047679, Estimated Cache Pages: 63334 ] x0Dx0A ( NestedLoopsJoinx0Dx0A ( IndexScan ( transamt ta ) tr1[ ta.property_id = 520 : 0.42146% Statistics ] )x0Dx0A ( IndexScan ( TRNSACTN t ) TRNSACTN[ ta.transaction_id = t.TRANSACTION_ID : 0.00017417% Statistics ] )x0Dx0A )x0Dx0A)x0Dx0A'.

I look forward to your valuable suggestions to resolve speed issue.

[Breck_Carter]

Try SELECT CONNECTION_PROPERTY ( 'optimization_goal' )

[Former Member]

Since it's the logic within your stored procedures that is involved, you can use procedure profiling to narrow down the procedure, or statements within a particular procedure, to determine where your application is spending the greatest amount of elapsed time. Once you've narrowed it down to a statement or statements, then you can use SQL Anywhere's application profiling functionality in Sybase Central to log the statements (and their graphical plans) that were executed, and then use this information as the basis for your problem determination.

There are a large number of differences between Version 6 and Version 11. The server has a multitude of improvements in query optimization and execution, and will now cache the plans of SQL queries used within procedures. One thing you could do immediately is to re-test your application with plan caching disabled (set the option MAX_PLANS_CACHED to zero) to see if plan caching is having an adverse affect on your application's performance.

There are a large number of other performance indicators and counters that may be relevant to you for debugging this problem, but using procedure and application profiling first will likely help to narrow down where the issue occurs.

[Former Member]

thank you for your help. I have setup the MAX_PLANS_CACHED to zero and it reduced the time taken by 15 to 20 seconds. I will try procedure profiling and let you know its result.

[Former Member]

Thank you for all you help. I am able to figure out why SQL Anywhere 11 was not able to process reports much faster. I did not select one of the check box which says “Sysobjects and Syscolumns” while creating database. I recreated that database with this option checked and reloaded the data. Now, reports are working much faster.

Thank you again for all your help.

[Former Member]

Try CREATE STATISTICS

[Former Member]

I would think that the existing database probably has the wrong query optimization goal setting as well, given our experience with upgrading ASA9 db's to SA11.

Try SET OPTION public.optimization_goal = 'All-rows'

[Former Member]

Thank you for letting this know. I tried this option, however there is no improvement in speed.