Max or Top 1 more efficient

Question

Post reply

Max or Top 1 more efficient

tshad

SSCertifiable

Points: 5905
More actions
April 24, 2013 at 11:12 am

#276884

I have a table with a month value and year value both ints.
I need two things, the latest date and the date 24 months before.
I came up with these to get the latest date and trying to find the most efficient.
SELECT MAX(CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar))) AS ValueDate
FROM dbo.myTable
This gets me a plan of
Stream Aggregate (Aggregate) -Cost 7%
Compute Scalar - Cost 1%
Clustered Index Scan - Cost 91%
The other option is:
SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC
Sort (top N Sort) -Cost 92%
Compute Scalar - Cost 0%
Clustered Index Scan - Cost 8%
Which one is the most efficient? The costs seem equivelant.
The whole query (depending on which select I use) would be:
DECLARE @ValueDate smalldatetime
SELECT @ValueDate =
(SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC)
SELECT DATEADD(month, -23, @ValueDate)
Thanks,
Tom

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Or this:

with MaxDate as (

select

max(dateadd(month, MonthValue - 1, dateadd(year, YearValue - 1900, 0))) as MaxDate

from

dbo.DealerForecastSegmentValue

)

select

md.MaxDate,

dateadd(month, -23, md.MaxDate) as MinDate

from

MaxDate;

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

How long do they take to run?

How long does a potentially SARGable SELECT MAX(CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +

CAST(YearValue AS varchar))) AS ValueDate

FROM (

SELECT TOP 1 YearValue, MonthValue

FROM dbo.myTable

ORDER BY YearValue DESC, MonthValue DESC

) d

query take?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

tshad (4/24/2013)
I have a table with a month value and year value both ints.
I need two things, the latest date and the date 24 months before.
I came up with these to get the latest date and trying to find the most efficient.
SELECT MAX(CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar))) AS ValueDate
FROM dbo.myTable
This gets me a plan of
Stream Aggregate (Aggregate) -Cost 7%
Compute Scalar - Cost 1%
Clustered Index Scan - Cost 91%
The other option is:
SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC
Sort (top N Sort) -Cost 92%
Compute Scalar - Cost 0%
Clustered Index Scan - Cost 8%
Which one is the most efficient? The costs seem equivelant.
The whole query (depending on which select I use) would be:
DECLARE @ValueDate smalldatetime
SELECT @ValueDate =
(SELECT TOP 1
CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' +
CAST(YearValue AS varchar)) AS ValueDate
FROM dbo.myTable
ORDER BY ValueDate DESC)
SELECT DATEADD(month, -23, @ValueDate)
Thanks,
Tom

A better thing to do would be to add a persisted calculated column to do the datetime conversion and then index that. Things will be much faster that way.

As a bit of a sidebar, a more efficient method for doing the calculation would be to get rid of all the VARCHAR conversions and stick to integer math. It's pretty simple, too.

DateTimeValue = DATEADD(mm,YearValue*12-22801+MonthValue,0)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 4

Just in case, IF you already have an index on ( YearValue, MonthValue ), then this should be extremely fast:

SELECT

MAX(YearValue) AS YearValue, MAX(MonthValue) AS MonthValue --or some computation to turn this into a datetime

FROM dbo.myTable

WHERE

YearValue = ( SELECT MAX(YearValue) FROM dbo.myTable )

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.