Which is the most efficient way to run a particular select query?
I'd probably use
SELECT TOP (1) WITH TIES PolicyNumber,
decpageid,
Risk
FROM StatRiskDecpages
WHERE PolicyNumber = 'AR-0000301132-04'
ORDER BY decpageid DESC
Assuming the covering index on (PolicyNumber, decpageid) INCLUDE(Risk)
this will give you a plan like
Here's how I refactored your query. (first I'll post your query with your execution plan, and then follows is my new query and the corresponding query plan)
Your Query
Select
t1.PolicyNumber,
t2.DecpageID,
t2.Risk
from
(
select
PolicyNumber,
MAX(decpageid) as decpageid
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber
) as t1
left join StatRiskDecpages as t2
on t1.PolicyNumber = t2.PolicyNumber
and t1.decpageid = t2.DecpageID
The execution plan for your query is as follows:
And then I wrote the query like this, giving the same desired results:
My Query
select
p.PolicyNumber,
p.decpageid,
p.Risk
from StatRiskDecpages p
where decpageid in
(
select max(decpageid)
from StatRiskDecpages
where PolicyNumber = p.PolicyNumber
)
This shows an execution plan of this:
Notice the single Table Scan (as opposed to your two Table Scans), as well as the lack of the Nested Loops for the JOIN.
As for what looks better and more maintainable, I think they are both relatively workable. Just some ideas and graphical representation of an alternative.
@ypercube makes a good point that an elegant looking query does not necessarily mean it will be more efficient. I have no idea about your indexes or data size but you may want to compare the following window function version against your existing queries (I would check both duration and the quality of the plans):
;WITH x AS
(
SELECT PolicyNumber, decpageid, Risk,
dr = DENSE_RANK() OVER (ORDER BY decpageid DESC)
FROM dbo.StatRiskDecpages
WHERE PolicyNumber = 'AR-0000301132-04'
)
SELECT PolicyNumber, decpageid, Risk
FROM x
WHERE dr = 1;
You may also consider leaving the PolicyNumber out of the result set. You already know what it is, since you passed it in, and it's the same redundant data on every row.