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.

Performance Problem using Sybase 9.0.2

2 posts in General Discussion Last posting was on 2006-07-31 12:03:01.0Z
Julius Haman Posted on 2006-07-31 06:52:17.0Z
From: Julius Haman <NoSpamXXL@gmx.net>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Performance Problem using Sybase 9.0.2
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: www.merkarion.de
X-Original-NNTP-Posting-Host: www.merkarion.de
Message-ID: <44cda8a1$1@forums-1-dub>
Date: 30 Jul 2006 23:52:17 -0700
X-Trace: forums-1-dub 1154328737 217.91.15.61 (30 Jul 2006 23:52:17 -0700)
X-Original-Trace: 30 Jul 2006 23:52:17 -0700, www.merkarion.de
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5538
Article PK: 1577

Hi,

we have got a serious performance problem (using Sybase 9.0.2, builds
3182 / 3198 / 3320 / 3347)
concerning the following select-statement:

SELECT a.aktv_id
FROM aktivitaet a
JOIN ereignis
ON ergn_id = aktv_id
JOIN ereignisobjekt e
ON e.ergn_id = a.aktv_id
JOIN klientenknoten k1
ON e.object_id = k1.klkn_id
JOIN klientenknoten k2
ON k2.klkn_orgidx LIKE k1.klkn_orgidx||'%'
AND k2.klst_id = k1.klst_id
JOIN klientposition p
ON k2.klie_id = p.klie_id
WHERE p.zpos_id IN ( 'ZPOS1NH397YM5W0KZ001', 'ZPOS1NK23ELN9K0VZ001' )
AND (ERGN_ZEITRAUM_A - ERGN_PLANVORLAUF) BETWEEN '1899-12-29 00.00.00'
AND '2006-10-29 23.59.59' AND ERGN_ZEITRAUM_B BETWEEN '2006-04-08
00.00.00' AND '2101-01-01 00.00.00'

Rows per table:
aktivitaet 349
ereignis 5388
ereignisobjekt 34045
klientenknoten 38009
klientposition 1538

This select is now executed using Interactive SQL. It returns results
after 5 seconds. But when this statement is executed a second time, it
lasts more than one hour before it returns any results. During this time
the database engine caches a large amount of space on the hard disk.

Using Sybase 9.0.2.2451 it works fine, but using newer versions (builds
3182, 3198, 3320 and 3347) the described error occurs.


Mark


Breck Carter [Team iAnywhere] Posted on 2006-07-31 12:03:01.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Performance Problem using Sybase 9.0.2
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <8srrc25nk5sfh0o101bptl84cpn11mta4h@4ax.com>
References: <44cda8a1$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
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 31 Jul 2006 04:54:47 -0700, bcarter.sentex.ca
Lines: 60
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Jul 2006 04:54:48 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Jul 2006 05:03:01 -0700
X-Trace: forums-1-dub 1154347381 10.22.108.75 (31 Jul 2006 05:03:01 -0700)
X-Original-Trace: 31 Jul 2006 05:03:01 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5540
Article PK: 1579

Try using the LogExpensiveQueries feature to capture the graphical
plans for both executions of select. You can send me the *.XML files
if you want, by email.

See this document for more information: LogExpensiveQueries: A Little
Bit Of Jasper In 9.0.2
http://www.ianywhere.com/developer/sql_anywhere_developer_corner/logexpensivequeries_jasper.html

Breck

On 30 Jul 2006 23:52:17 -0700, Julius Haman <NoSpamXXL@gmx.net> wrote:

>Hi,
>
>we have got a serious performance problem (using Sybase 9.0.2, builds
>3182 / 3198 / 3320 / 3347)
>concerning the following select-statement:
>
>SELECT a.aktv_id
>FROM aktivitaet a
>JOIN ereignis
> ON ergn_id = aktv_id
>JOIN ereignisobjekt e
> ON e.ergn_id = a.aktv_id
>JOIN klientenknoten k1
> ON e.object_id = k1.klkn_id
>JOIN klientenknoten k2
> ON k2.klkn_orgidx LIKE k1.klkn_orgidx||'%'
> AND k2.klst_id = k1.klst_id
>JOIN klientposition p
> ON k2.klie_id = p.klie_id
>WHERE p.zpos_id IN ( 'ZPOS1NH397YM5W0KZ001', 'ZPOS1NK23ELN9K0VZ001' )
>AND (ERGN_ZEITRAUM_A - ERGN_PLANVORLAUF) BETWEEN '1899-12-29 00.00.00'
>AND '2006-10-29 23.59.59' AND ERGN_ZEITRAUM_B BETWEEN '2006-04-08
>00.00.00' AND '2101-01-01 00.00.00'
>
>Rows per table:
>aktivitaet 349
>ereignis 5388
>ereignisobjekt 34045
>klientenknoten 38009
>klientposition 1538
>
>This select is now executed using Interactive SQL. It returns results
>after 5 seconds. But when this statement is executed a second time, it
>lasts more than one hour before it returns any results. During this time
>the database engine caches a large amount of space on the hard disk.
>
>Using Sybase 9.0.2.2451 it works fine, but using newer versions (builds
>3182, 3198, 3320 and 3347) the described error occurs.
>
>
>Mark

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