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

[former_SQLA_member1694874]

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' )