Poor performing query with a sebquery in the select 1

[smn198]

Hi all,

I'm trying to improve the performance of a very slow running query. I'm linking master records to child or payment records via an intermediate table. Diagram:

http://tinypic.com/view/?pic=ny7lf9

For each master record there may be many entries (or 0) in the linking table and for each entry in the linking table there will be one child or payment record.

To get the most recent child or payment record for each master record I am using the following SQL. Can anyone thing of a more efficient way of doing this?

select
  rga.RecurringGiftID,
  (
    select top 1
      p.RESystemId
    from
      dbo.WH_Gift p
    where
      p.RESystemID = rga.PaymentId
    order by
      p.date desc) LastPaymentID
from
  RGA_Activity rga

TIA

 

[PHV]

You may try this:
SELECT rga.RecurringGiftID, p.LastPaymentID
FROM RGA_Activity rga INNER JOIN (
SELECT RESystemID, MAX(PaymentId) LastPaymentID FROM dbo.WH_Gift GROUP BY RESystemID
) p ON rga.PaymentId = p.RESystemID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smn198]

I didn't explicitly state it be a greater payment id doesn't nessisarily mean that it is a later gift (which is what I'm after).

RGA_Activity rga table
RecurringGiftID field
PaymentID field (fk to WH_Gift.RESystemID)

WH_Gift p table
RESystemID field
Date field

For each
RGA_Activity.RecurringGiftID I want the most recent WH_Gift.RESystemID

Thanks,
Steve