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


--
Rob Martin
Software Engineer

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

Wild Software Ltd




--
Rob Martin
Software Engineer

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

Wild Software Ltd