--- In firebird-support@yahoogroups.com, Robert martin wrote:

> Hi
>
> I am reposting this as I never got it back from the list yesterday
> (although I did get 1 off list response!)........
>
> Hi all
>
> I have the following SQL that when run on a largish dataset is quite
> slow.
>
> SELECT T."Date", T."Time", Client.ClientCode, Client.ClientName,
> Staff.Name As StaffName, Region.RegionName,
> Machine.Name AS MachineName, T.TransRef, tn.Note
> FROM Transactions T
> LEFT JOIN Client ON Client.EntityRef = Transactions.EntityRef
> JOIN Staff ON Staff.EntityRef = Transactions.SEntityRef
> JOIN Region ON Region.RegionRef = Transactions.RegionRef
> JOIN Machine ON Machine.EntityRef = Transactions.MEntityRef
> LEFT JOIN TransactionNote tn ON tn.TransRef = T.TransRef
> WHERE Transactions.Transource = 'P'
> ORDER BY T.TransRef DESC
>
> This takes 13.75s to run. If I remove the DESC it only takes 0.44s.
> Attached below is the plan for this SQL. There is a DESC index on
> the T.Transref field (which is also the PK), what more can I do to
> improve performance? I really want the grid that displays this data
> to sit on the most recent tranasactions to avoid the user scrolling
> to the end!
>
>
> PLAN SORT (JOIN (JOIN (JOIN (T NATURAL,CLIENT INDEX
> (CLIENT_ENTITYREF)),JOIN (STAFF INDEX (STAFF_ENTITYREF),
> MACHINE INDEX (MACHINE_ENTITYREF),REGION INDEX (REGION_REGIONREF))),
> TN INDEX (TRANSACTIONNOTE_TRANSREF)))

To me, this seems like a good plan, so I'm sorry to say that I think
the biggest problem is that your where clause doesn't restrict the
result set sufficiently (I suspect that the difference between 0.44s
and 13.75s is only the time needed to return the first few records and
that returning all records would be more or less equal).

Firebird isn't (or at least, wasn't) good at using descending indexes.
If transref is a number of some kind, I guess you could add another
field to your table and use triggers to populate that field with the
negative equivalent of transref. Then you could sort on this new
column in ascending order and get the same result as with a descending
order on transref.

HTH,
Set