The T.TRANSREF order wouldn't use the TRANSACTIONNOTE_TRANSREF index, so
the dummy-zero math wouldn't change that.
I think you may be SOL. The planner is using the two indexes (probably)
generated by the foreign key (another guess) between
Transactions.TransRef and TransactionNote.TransRef, which are always
ascending.
I don't think the planner can use two indexes on a stream, unless they
are ORed together.
Nevermind, I see it's going through all of Transactions.
Is the plan different for the fast case? If so, change the index it is
using for ORDER to the descending one.

-----Original Message-----
From: Robert martin [mailto:rob@...]
Sent: Tuesday, March 08, 2005 4:28 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query performance / DESC order


Tried + 0 no improvement.

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd

>Could you try and change the "order by" to something like:
> order by T.TRANSREF+0 desc (or T.TRANSREF||'' if TRANSREF is

char/varchar)

>in an attempt to avoid the "INDEX (TRANSACTIONNOTE_TRANSREF)" in the

plan.

>
>
>--
>Aage J.
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

Yahoo! Groups Links