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

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.