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.

asked 24 Oct '11, 10:48

Ashlesh's gravatar image

Ashlesh
16113
accept rate: 0%

edited 24 Oct '11, 12:38

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

(24 Oct '11, 11:41) Daz Liquid

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.

(24 Oct '11, 12:40) Ashlesh
Replies hidden
3

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.

(24 Oct '11, 13:36) carolstone

Sharp eyes you have, young one!

(25 Oct '11, 14:48) Breck Carter

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.

permanent link

answered 24 Oct '11, 16:10

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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.

(25 Oct '11, 13:32) Ashlesh
Replies hidden

Try SELECT CONNECTION_PROPERTY ( 'optimization_goal' )

(25 Oct '11, 14:52) Breck Carter

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.

permanent link

answered 25 Oct '11, 14:44

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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.

(27 Oct '11, 08:17) Ashlesh

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.

permanent link

answered 22 Nov '11, 09:26

Ashlesh's gravatar image

Ashlesh
16113
accept rate: 0%

Try CREATE STATISTICS

permanent link

answered 25 Oct '11, 03:26

Marsel's gravatar image

Marsel
185256
accept rate: 33%

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'

permanent link

answered 25 Oct '11, 11:18

Seth_Krieger's gravatar image

Seth_Krieger
116127
accept rate: 25%

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

(25 Oct '11, 13:24) Ashlesh
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×239
×137
×51
×7

question asked: 24 Oct '11, 10:48

question was seen: 2,139 times

last updated: 22 Nov '11, 09:26