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]

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.