High performance TSQL to retrieve data

Question

I have two tables with below structure

Person(ID, Name, ...)
Action(ID, FirstPersonId, SecondPersonId, Date)

I wanna retrieve this data for each person:

Number of action that a person be on second person from last action that be on first person

Current query

Select  Result.Id ,
        (Select Count(*)
            From    Action
            Where   SecondPersonId = Result.Id 
                    AND Date > Result.LastAction)
    From    
            (Select ID ,
                    (   
                        Select Top 1 Date
                            From    Action
                            Where   Action.FirstPersonId = Person.Id
                    ) as LastAction
                From    Person ) As Result

this query has bad performance and i need very better one.

Please tell us a bit about your input tables and what is the output you expect. Also, how many rows do the tables have ? — Erran Morad, Oct 12 '14 at 06:53
Do you want `Top 1 Date` to get the last action date of the first person? — Jaugar Chang, Oct 12 '14 at 06:56

Jaugar Chang · Accepted Answer · 2014-10-12T08:01:11.497

with lastActionPerson as  -- last action for every first person
(select FirstPersonId , max([Date]) as LastActionDate
   from Action
)
select a.SecondPersonId ,count(*)
  from lastActionPerson lap
  join Action a
    on a.SecondPersonId = lap.FirstPersonId --  be on second person
   and a.[Date] > lap.lastActionDate  
 -- you could continue to right join person table to show the person without actions
 group by a.SecondPersonId

High performance TSQL to retrieve data

1 Answers1