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

4 posts in General Discussion Last posting was on 2007-06-09 11:23:42.0Z
Sybase Posted on 2007-05-15 18:33:15.0Z
From: "Sybase" <iedhoran@hotmail.com>
Newsgroups: ianywhere.public.general
Subject: SA9.0.2 is very slow
Lines: 35
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: <4649fceb$1@forums-1-dub>
Date: 15 May 2007 11:33:15 -0700
X-Trace: forums-1-dub 1179253995 216.74.253.132 (15 May 2007 11:33:15 -0700)
X-Original-Trace: 15 May 2007 11:33:15 -0700, 216.74.253.132
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6004
Article PK: 2464

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


Stephen Rice Posted on 2007-05-17 13:24:06.0Z
From: "Stephen Rice" <sriceATianywhereDOTcom>
Newsgroups: ianywhere.public.general
References: <4649fceb$1@forums-1-dub>
Subject: Re: SA9.0.2 is very slow
Lines: 65
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: srice-xp2.sybase.com
X-Original-NNTP-Posting-Host: srice-xp2.sybase.com
Message-ID: <464c5776$1@forums-1-dub>
Date: 17 May 2007 06:24:06 -0700
X-Trace: forums-1-dub 1179408246 10.25.98.210 (17 May 2007 06:24:06 -0700)
X-Original-Trace: 17 May 2007 06:24:06 -0700, srice-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6011
Article PK: 2470

Hi

- I suggest you post this kind of question is
sybase.public.sqlanywhere.general as that is where most of the SQL Anywhere
folks hang out.

- The best place to start is to examine the query plan with statistics.
Instructions for doing this are included in the manual in the section
ASA SQL User's Guide > Query Optimization and Execution. I suggest you
review the entire section.

If you are still puzzled by what you see then re-post in the
.sqlanywhere.general and make sure you attached the query plan with
statistics

--
/steve
Stephen Rice
Sybase iAnywhere

"Sybase" <iedhoran@hotmail.com> wrote in message
news:4649fceb$1@forums-1-dub...
>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
>
>


Ani Nica Posted on 2007-06-08 20:07:08.0Z
From: "Ani Nica" <anica@sybase.com>
Newsgroups: ianywhere.public.general
References: <4649fceb$1@forums-1-dub>
Subject: Re: SA9.0.2 is very slow
Lines: 75
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: anica-xp.sybase.com
X-Original-NNTP-Posting-Host: anica-xp.sybase.com
Message-ID: <4669b6ec$1@forums-1-dub>
Date: 8 Jun 2007 13:07:08 -0700
X-Trace: forums-1-dub 1181333228 10.25.99.90 (8 Jun 2007 13:07:08 -0700)
X-Original-Trace: 8 Jun 2007 13:07:08 -0700, anica-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6070
Article PK: 2528

I have two suggestions for solving this issue:

1. Rewrite your query to the equivalent query (given that the PK-FK
relationship "transactions.deal_id = deal.deal_id" is defined ):


Select transactions.deal_id , transactions.year_month,
transactions.action_date,
transactions.volume from deals, transactions
where IS NOT NULL and transactions.year_month =
200705;


OR
2. declare your query READ ONLY and the SA engine will rewrite the original
query for you (i.e., the table 'deals' will be eliminated).

In 5.0 the engine will rewrite the original query always which was
incorrect. The table 'deals' cannot be eliminated if the query is not READ
ONLY.

--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering
~~~~~
* Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day and
4-day options for SQL Anywhere technical training. Sessions will focus on
data management, data movement, and SQL Anywhere 10. Visit
www.sybase.com/techwave
~~~~~

"Sybase" <iedhoran@hotmail.com> wrote in message
news:4649fceb$1@forums-1-dub...
>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-06-09 11:23:42.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: SA9.0.2 is very slow
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <r83l631fepagrq66m66ofdrgm1bjap6odl@4ax.com>
References: <4649fceb$1@forums-1-dub> <4669b6ec$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: 208.253.124.99
X-Original-NNTP-Posting-Host: 208.253.124.99
Date: 9 Jun 2007 04:23:42 -0700
X-Trace: forums-1-dub 1181388222 208.253.124.99 (9 Jun 2007 04:23:42 -0700)
X-Original-Trace: 9 Jun 2007 04:23:42 -0700, 208.253.124.99
Lines: 17
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6071
Article PK: 2529

I must *really* be suffering from early-morning-coffee-deficit because
I'm drawing a blank on this...

>Select transactions.deal_id , transactions.year_month,
>transactions.action_date,
> transactions.volume from deals, transactions
> where IS NOT NULL and transactions.year_month =
> 200705;

Breck

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