Optimize query with a subquery With Group BY MAX and JOINED with another TABLE

Question

I need help to optimize this SQL query, so that it would run much faster.

What I am trying to do is, get the latest values of DATA out of these tables:

TABLE: Quotes
ID QuoteNumber LastUpdated(inticks) PolicyId
1 C1000 1000000000000 100
1 D2000 1001111111110 200
2 A1000 1000000000000 300
2 B2000 1002222222222 400

TABLE: Policies
ID CustomerName Blah1(dummy column)
100 Mark someData
200 Lisa someData2
300 Brett someData3
400 Goku someData4

DESIRED RESULT:
LastUpdated Id(quoteId) QuoteNumber CustomerName
1001111111110- -1- -D2000- -Lisa
1002222222222- -2- -B2000- -Goku

Select DISTINCT subquery1.LastUpdated,
                q2.Id, 
                q2.QuoteNumber,
                p.CustomerName 
                FROM
                (Select q.id, 
                            Max(q.LastUpdated) from Quotes q
                            where q.LastUpdated > @someDateTimeParameter
                            and q.QuoteNumber is not null
                            and q.IsDiscarded = 0
                            GROUP BY q.id) as subquery1
LEFT JOIN Quotes q2
on q2.id = subquery1.id
and q2.LastUpdated = subquery1.LastUpdated
INNER JOIN Policies p
on p.id = q2.PolicyId
where p.blah1 = @someBlahParameter
ORDER BY subquery1.LastUpdated

Here is the actual execution plan: https://www.brentozar.com/pastetheplan/?id=SkD3fPdwD

Answer 1

I think you're looking for something like this

with q_cte as (
    select q.Id, q.QuoteNumber, q.LastUpdated, 
           row_number() over (partition by q.id order by q.LastUpdated desc) rn
    from Quotes q
    where q.LastUpdated>@someDateTimeParameter
          and q.QuoteNumber is not null
          and q.IsDiscarded=0)
select q.*, p.CustomerName 
from q_cte q
     join Policies p on q.PolicyId=p.id
where q.rn=1 /* Only the lastest date */
      and p.blah1=someBlahParameter
order by q.LastUpdated;