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]

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.