Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Max or Top 1 more efficient Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 11:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
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
Post #1446127
Posted Wednesday, April 24, 2013 11:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 20,799, Visits: 32,718
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1446132
Posted Wednesday, April 24, 2013 2:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 1,037, Visits: 6,951
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?



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1446227
Posted Wednesday, April 24, 2013 4:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:20 PM
Points: 35,552, Visits: 32,148
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1446257
Posted Wednesday, April 24, 2013 5:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 3:44 PM
Points: 2,266, Visits: 3,419
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1446258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse