Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SA9.0.2 is very slow

2 posts in General Discussion (old) Last posting was on 2007-05-16 13:29:49.0Z
Sybase Posted on 2007-05-15 18:39:55.0Z
From: "Sybase" <iedhoran@hotmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: SA9.0.2 is very slow
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: 216.74.253.132
X-Original-NNTP-Posting-Host: 216.74.253.132
Message-ID: <4649fe7b$1@forums-1-dub>
Date: 15 May 2007 11:39:55 -0700
X-Trace: forums-1-dub 1179254395 216.74.253.132 (15 May 2007 11:39:55 -0700)
X-Original-Trace: 15 May 2007 11:39:55 -0700, 216.74.253.132
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:24
Article PK: 866479

I run simple query on the database that I used to run on SA 5.5 Anywhere 9
and now it takes over 4 times what it used to take in the old version. What
used to take 10-15 second, now takes 60-120 seconds. I tried to rebuild the
database, tried to change every option. Cannot get it to really do anything
better. Moved between servers (one processor or two) and installed locally.
No luck on making it speed up. Database size is just 2GB. I had it 4GB on
ASA5.5 and I split it to help with performance, but no great performance
obtained.

Sample of what I have. two tables
table 1= deals with columns: deal_id, year_month, start_date, stop_date,....
table 2= transactions with columns:trans_id, deal_id, year_month,
action_date, volume, ...

PK for deals is deal_id
PK for transactions trans_id
Index on deals: year_month, start_date, stop_date
Index on transactions deal_id, year_month, action_date
FK transactions.deal_id = deal.deal_id

table deals has about 400,000 records
table transactions has about 2,000,000 records

A query like this:
Select deals.deal_id, transactions.year_month, transactions.action_date,
transactions.volume from deals, transactions
where deals.deal_id = transactions.deal_id and transactions.year_month =
200705;

Please if any can tell why and what should be done to improve the
performance. I can't go back to ASA5.5, I wish I could.

Thanks


Breck Carter [Team iAnywhere] Posted on 2007-05-16 13:29:49.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: SA9.0.2 is very slow
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <dp1m431obbrcasi69tce14h270i39b4luh@4ax.com>
References: <4649fe7b$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 16 May 2007 06:29:49 -0700
X-Trace: forums-1-dub 1179322189 64.7.134.118 (16 May 2007 06:29:49 -0700)
X-Original-Trace: 16 May 2007 06:29:49 -0700, bcarter.sentex.ca
Lines: 85
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:25
Article PK: 866480

Any time you make as dramatic a change in RDBMS software as 5.5 to
9.0.2, some queries are going to suffer. Most will be faster, but the
only ones anyone really cares about are the slower ones.

So, there's something about your query that worked well, as-is, in
5.5, but not so much in 9.0.2. We need to find out what that is.

The best place to start is with a graphical plan; do the following and
send me the xml file by email:

- In version 9.0.2 dbisql, choose Tools - Options... - Plan.

- Check "Graphical plan with statistics".

- Check "Assume read-only cursor" if that applies.

- Click "Make Permanent".

- Run your query in dbisql (see Tip below).

- Do File - Save As - Save as type: XML (*.xml).

Tip: If you do use dbisql to execute a query and
capture a graphical plan with statistics, use
SQL - Get Plan (Shift+F5) instead of
SQL - Execute (F5 or F9). If you use Execute,
the query will be executed twice: once to
determine the result set and a second time to
capture the plan. This plan may be completely
different from the one used the first time the
query was executed, and not just because the
cache contains the rows. For example, if the
plan is for a DELETE, the first execution will
actually delete the rows so the plan may be for
the completely different "zero rows" case.
If you use Get Plan (Shift+F5) the query will
only be run once, and the plan will match.

Breck

On 15 May 2007 11:39:55 -0700, "Sybase" <iedhoran@hotmail.com> wrote:

>
>I run simple query on the database that I used to run on SA 5.5 Anywhere 9
>and now it takes over 4 times what it used to take in the old version. What
>used to take 10-15 second, now takes 60-120 seconds. I tried to rebuild the
>database, tried to change every option. Cannot get it to really do anything
>better. Moved between servers (one processor or two) and installed locally.
>No luck on making it speed up. Database size is just 2GB. I had it 4GB on
>ASA5.5 and I split it to help with performance, but no great performance
>obtained.
>
>Sample of what I have. two tables
>table 1= deals with columns: deal_id, year_month, start_date, stop_date,....
>table 2= transactions with columns:trans_id, deal_id, year_month,
>action_date, volume, ...
>
>PK for deals is deal_id
>PK for transactions trans_id
>Index on deals: year_month, start_date, stop_date
>Index on transactions deal_id, year_month, action_date
>FK transactions.deal_id = deal.deal_id
>
>table deals has about 400,000 records
>table transactions has about 2,000,000 records
>
>A query like this:
>Select deals.deal_id, transactions.year_month, transactions.action_date,
>transactions.volume from deals, transactions
>where deals.deal_id = transactions.deal_id and transactions.year_month =
>200705;
>
>Please if any can tell why and what should be done to improve the
>performance. I can't go back to ASA5.5, I wish I could.
>
>Thanks
>
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com