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

Sample data, desired results, and an explanation of what the queries does would help. Similarly, table size and layout information is helpful as well an information about current run times. — Gordon Linoff
– Gordon Linoff, Commented Oct 17, 2020 at 11:49
Images of the plan don't fully help us, use paste the plan (linked above). Though that image implies there is very little data involved, so if this is running slow I suspect it is not the query itself and something else; such as a bandwidth issue or a server with very low specs. — Thom A
– Thom A ♦, Commented Oct 17, 2020 at 12:05
Now we see your code is paging the resultset, which makes your original post very deceiving. And without DDL for the tables it is just a game of guessing. I can tell you that your left join in absolutely pointless. Did you throw DISTINCT in your query for a reason? — SMor
– SMor, Commented Oct 17, 2020 at 12:33

SteveC · Accepted Answer · 2020-10-17 12:25:45Z

2

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;

edited Oct 17, 2020 at 12:25

answered Oct 17, 2020 at 12:06

SteveC

6,0252 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related