Minoru Yoshida wrote:

> Hi,
>
> I have INDEX performance problem with FB superserver V2.1 on WinXP.
>
> I'm using the IBObjects ordering-change mechenism.
> The foward INDEX ordering is fast, but backward INDEX ordering is slow.
>
> This problem suddenly happened between 160 and 170 VARCHAR size of UTF8
> Charcter set.
> I get the following result in backward INDEX ordering test case.
>
> Size: min - max
> VARCHAR(100): 0.51 - 0.60(s)
> VARCHAR(150): 0.78 - 0.84(s)
> VARCHAR(160): 0.79 - 0.86(s)
> VARCHAR(170): 3.51 - 3.59(s)
> VARCHAR(200): 4.30 - 5.51(s)
> VARCHAR(250): 6.25 - 6.44(s)
>
> Is this a performance limit of V2.1?
> Please any suggestion.
>
>
> Firebird.conf settings:
>
> - DefaultDbCachePages = 16384
> - TempBlockSize = 3200000 (or 6400000)
>
> Test sequences:
>
> 1. Creation table and index
>
> CREATE TABLE Test (
> I INTEGER NOT NULL
> , V VARCHAR(nnn) CHARACTER SET UTF8
> , CONSTRAINT TS1PK PRIMARY KEY (I));
>
> CREATE INDEX IDX01 ON Test(I);
>
> 2. Insert 100,000 records
>
> INSERT INTO Test(I,V) VALUES(:I,:V);
>
> 3. Change ordering :SQL as bellow.
>
> - Forward
>
> SELECT * FROM Test
> ORDER BY V ASC
> PLAN (Test ORDER IDX01)
>
> - Backward
>
> SELECT * FROM Test
> ORDER BY V DESC
> PLAN SORT ((Test NATURAL))
>
> 　
> --
> Regards,
> Minoru
>

Measure the time of a fetch all...

First plan will be fast to retrieve the first rows...

An ASC index could not be used to do a indexed retrieval (Index Walking)
for a DESC order by clause.

No FB version would use a plan like

PLAN (Test ORDER IDX01)


for

SELECT * FROM Test
ORDER BY V DESC


without a DESC index on test.v


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br