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.

How to optimize a search by date

2 posts in PHP Last posting was on 2009-06-03 15:25:37.0Z
Ingolf Seltrecht Posted on 2009-06-02 14:25:49.0Z
From: Ingolf Seltrecht <Ingolf.Seltrecht@Diedrich-GmbH.de>
Subject: How to optimize a search by date
Newsgroups: Advantage.PHP
User-Agent: Pan/0.133 (House of Butterflies)
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 217.6.219.242
Message-ID: <4a25285d@solutions.advantagedatabase.com>
Date: 2 Jun 2009 07:25:49 -0700
X-Trace: 2 Jun 2009 07:25:49 -0700, 217.6.219.242
Lines: 24
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!217.6.219.242
Xref: solutions.advantagedatabase.com Advantage.PHP:1023
Article PK: 1135507

Hello,

i have got a new problem.
My table has 337450 row. I think that is not to mutch.
I have got an index like:
REC_FIRMA+rec_typ+bk_kdnr+bk_nr+dtos(rec_datum)

My sqlstatement is:
SELECT * FROM $WWSBK WHERE
REC_FIRMA=' 1' and
REC_TYP='V' and
BK_DATUM>='2008-06-02' and
BK_KDNR='1234567' and
BK_NR>='1234567' and
BK_NR<='7654321'
order by BK_DATUM

To fetch all rows for the first time it takes over 36 seconds.
A search for the second time and fetch all rows tak's only 0.5 seconds.

How can i optimize that?

Best Regards Ingolf


Mark Wilkins Posted on 2009-06-03 15:25:37.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.PHP
References: <4a25285d@solutions.advantagedatabase.com>
Subject: Re: How to optimize a search by date
Date: Wed, 3 Jun 2009 09:25:37 -0600
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 10.24.38.228
Message-ID: <4a2695bd@solutions.advantagedatabase.com>
X-Trace: 3 Jun 2009 09:24:45 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.PHP:1024
Article PK: 1135508

Hi Ingolf,

Without having the data, it is not possible to say for sure. But it seems
to me that an index on BK_DATUM would help. That part of the WHERE clause
cannot be optimized otherwise. If the rest of the WHERE clause does not
restrict the data very much, then a large number of records would have to be
scanned in order to resolve the query. That index will only be used for
optimizing the REC_FIRMA, REC_TYPE, and BK_KDNR portion of the WHERE clause.
The portion involving BK_NR will not use that index. It may help to have a
separate index on BK_NR. The help file contains a section titled AOF
Optimization that may help explain it.

http://devzone.advantagedatabase.com/dz/webhelp/Advantage9.1/advantage.htm#aof_optimization.htm

Mark Wilkins
Advantage R&D

"Ingolf Seltrecht" <Ingolf.Seltrecht@Diedrich-GmbH.de> wrote in message
news:4a25285d@solutions.advantagedatabase.com...
> Hello,
>
> i have got a new problem.
> My table has 337450 row. I think that is not to mutch.
> I have got an index like:
> REC_FIRMA+rec_typ+bk_kdnr+bk_nr+dtos(rec_datum)
>
> My sqlstatement is:
> SELECT * FROM $WWSBK WHERE
> REC_FIRMA=' 1' and
> REC_TYP='V' and
> BK_DATUM>='2008-06-02' and
> BK_KDNR='1234567' and
> BK_NR>='1234567' and
> BK_NR<='7654321'
> order by BK_DATUM
>
> To fetch all rows for the first time it takes over 36 seconds.
> A search for the second time and fetch all rows tak's only 0.5 seconds.
>
> How can i optimize that?
>
> Best Regards Ingolf
>