Simple SQL challenge - who can solve

Question

Simple SQL challenge - who can solve

Viewing 8 posts - 31 through 37 (of 37 total)

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Alan.B (5/11/2016)
I think my performance test proves that a calendar table is a viable high-performing alternative. In the end I would go with a tally table solution because of the proper date range situation. I presented a calendar table solution because it is also a viable option and a great tool for the set-based thinker.

The calendar table is indeed a good method for solving this kind of problems;-)

😎

Alan's fine test harness with slight modification and two additional functions, an inline tally table function with variable number of months in the output and a constant scan only (union all) version returning 12 months.

/****************************************************************************************

1. Create and populate sample calendar table date range 2010 to 2017 (simplified)

****************************************************************************************/

IF OBJECT_ID(N'dbo.LastNMonth_Luis') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Luis ;

IF OBJECT_ID(N'dbo.LastNMonth_Alan') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Alan ;

IF OBJECT_ID(N'dbo.LastNMonth_Variable') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Variable;

IF OBJECT_ID(N'dbo.LastNMonth_Fixed') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Fixed ;

IF OBJECT_ID(N'dbo.dim_date') IS NOT NULL DROP TABLE dbo.dim_date ;

CREATE TABLE dbo.dim_date

(

calDate date primary key,

calDay tinyint not null

);

INSERT dbo.dim_date

SELECT TOP (2557) -- enough days to get me from 2010 to 2017

CAST(DATEADD(DAY,number,'20100101') AS date),

DAY(DATEADD(DAY,number,'20100101'))

FROM master..spt_values

WHERE type = 'p';

-- filtered covering index for first of the month

CREATE NONCLUSTERED INDEX nc_FirstOfMonth ON dbo.dim_date(calDate,calDay)

WHERE calDay = 1;

GO

/****************************************************************************************

2. Create the functions

****************************************************************************************/;

-- Function #1: Inline Tally table solution

CREATE FUNCTION dbo.LastNMonth_Luis (@inputDate date)

RETURNS TABLE WITH SCHEMABINDING AS RETURN

SELECT Mo = DATEADD(MM, DATEDIFF(MM, 0, @inputDate)-n, 0)

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))x(n);

GO

-- Function #2: Same functionality using a calendar table

CREATE FUNCTION dbo.LastNMonth_Alan (@inputDate date)

RETURNS TABLE WITH SCHEMABINDING AS RETURN

SELECT TOP (12) calDate AS mo

FROM dbo.dim_date

WHERE calDay = 1 AND calDate <= @inputdate

ORDER BY calDate DESC;

GO

-- Function #3: Inline Tally table solution with variable number of dates

CREATE FUNCTION dbo.LastNMonth_Variable

(

@INPUT_DATE DATE

,@MONTH_COUNT INT

)

RETURNS TABLE WITH SCHEMABINDING AS RETURN

WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

, NUMS(N) AS (SELECT TOP(@MONTH_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)

SELECT

DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE)-(nm.N -1), 0) AS MONTH_VAL

FROM NUMS NM;

GO

-- Function #4: Fixed Constant Scan Function

CREATE FUNCTION dbo.LastNMonth_Fixed

(

@INPUT_DATE DATE

)

RETURNS TABLE WITH SCHEMABINDING AS RETURN

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE), 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 1, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 2, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 3, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 4, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 5, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 6, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 7, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 8, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) - 9, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) -10, 0) AS MONTH_VAL UNION ALL

SELECT DATEADD(MM, DATEDIFF(MM, 0, @INPUT_DATE) -11, 0) AS MONTH_VAL;

GO

--SELECT * FROM dbo.LastNMonth_Luis(getdate());

--SELECT * FROM dbo.LastNMonth_Alan(getdate());

;

/****************************************************************************************

3. Populate #tmp with sample data (and ID and a date)

****************************************************************************************/;

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;

CREATE TABLE #tmp

(

id int primary key,

SomeDate date not null

);

INSERT #tmp

SELECT TOP (100000)

DateID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

SomeDate = CAST(DATEADD(DAY,CHECKSUM(newid())%500,'20130702') AS date)

FROM sys.all_columns a, sys.all_columns b;

GO

/****************************************************************************************

4. Performance test - run the function for each ID

****************************************************************************************/;

DECLARE @timer TABLE (T_TEXT VARCHAR(30) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME()));

DECLARE @INT_BUCKET INT = 0;

DECLARE @TINYINT_BUCKET TINYINT = 0;

DECLARE @DATE_BUCKET DATE = CONVERT(DATE,GETDATE(),0);

INSERT INTO @timer(T_TEXT) VALUES('DRY RUN #tmp');

SELECT

@INT_BUCKET = T.id

,@DATE_BUCKET = T.SomeDate

FROM #tmp T;

INSERT INTO @timer(T_TEXT) VALUES('DRY RUN #tmp');

INSERT INTO @timer(T_TEXT) VALUES('DRY RUN dbo.dim_date');

SELECT

@TINYINT_BUCKET = T.calDay

,@DATE_BUCKET = T.calDate

FROM dbo.dim_date T;

INSERT INTO @timer(T_TEXT) VALUES('DRY RUN dbo.dim_date');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Luis 01');

SELECT

@DATE_BUCKET = LL.Mo

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Luis(T.SomeDate) LL

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Luis 01');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Variable 01');

SELECT

@DATE_BUCKET = LV.MONTH_VAL

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Variable(T.SomeDate,12) LV

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Variable 01');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Fixed 01');

SELECT

@DATE_BUCKET = LV.MONTH_VAL

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Fixed(T.SomeDate) LV

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Fixed 01');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Alan 01');

SELECT

@DATE_BUCKET = LA.mo

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Alan(T.SomeDate) LA

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Alan 01');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Luis 02');

SELECT

@DATE_BUCKET = LL.Mo

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Luis(T.SomeDate) LL

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Luis 02');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Variable 02');

SELECT

@DATE_BUCKET = LV.MONTH_VAL

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Variable(T.SomeDate,12) LV

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Variable 02');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Fixed 02');

SELECT

@DATE_BUCKET = LV.MONTH_VAL

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Fixed(T.SomeDate) LV

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Fixed 02');

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Alan 02');

SELECT

@DATE_BUCKET = LA.mo

FROM #tmp T

CROSS APPLY dbo.LastNMonth_Alan(T.SomeDate) LA

INSERT INTO @timer(T_TEXT) VALUES('dbo.LastNMonth_Alan 02');

SELECT

T.T_TEXT

,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

FROM @timer T

GROUP BY T.T_TEXT

ORDER BY DURATION;

Results on 2nd Gen i5 laptop, Microsoft SQL Server 2014 - 12.0.4213.0 (X64)

T_TEXT DURATION

------------------------------ --------

DRY RUN dbo.dim_date 0

DRY RUN #tmp 20000

dbo.LastNMonth_Alan 01 310000

dbo.LastNMonth_Alan 02 310001

dbo.LastNMonth_Fixed 02 340000

dbo.LastNMonth_Fixed 01 340001

dbo.LastNMonth_Luis 01 590001

dbo.LastNMonth_Luis 02 600001

dbo.LastNMonth_Variable 01 640001

dbo.LastNMonth_Variable 02 650001

Results on 3rd Gen i7, Microsoft SQL Server 2016 (RC2) - 13.0.1300.275 (X64)

T_TEXT DURATION

------------------------------ --------

DRY RUN dbo.dim_date 0

DRY RUN #tmp 22128

dbo.LastNMonth_Fixed 02 285153

dbo.LastNMonth_Fixed 01 300779

dbo.LastNMonth_Alan 02 347657

dbo.LastNMonth_Alan 01 416662

dbo.LastNMonth_Luis 01 700629

dbo.LastNMonth_Variable 02 770511

dbo.LastNMonth_Luis 02 802078

dbo.LastNMonth_Variable 01 848965

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Alan.B: You have escaped the primary performance problem of chewing through unnecessary rows of each month on the date dim table with the filtered index. Well done. That was a nice trick - something I have not seen done before. That's a phrase I don't utter very often in SQL Server land! 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 3

Thanks to everyone so far for their innovative solutions; I thought it might trigger quite a bit of interest.

drew.allen SSC Guru Points: 76996 More actions · Answer 4

TheSQLGuru (5/11/2016)
Drew.Allen: if you want to better test server performance on a given query that does lots of rows SELECT, just put the column(s) into variable(s). Voila - no more timing temp table operations or spooling large numbers of rows to a client when that timing isn't germane to the test.

Yes, I thought of that last night right after I posted, but I didn't have time to redo it, because I needed to leave for a class that I was teaching.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Neeraj Pal Valued Member Points: 52 More actions · Answer 5

This Query will Return current and Previous 12 month Start Date..

==========================================

DECLARE @month int=0

DECLARE @table TABLE (LastYearDate DATETIME)

WHILE @month<12

BEGIN

INSERT @table

( LastYearDate )

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@month,0)

SET @month+=1

END

SELECT * from @table

==================================

I hope this query will Help you..

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

Neeraj Pal (5/13/2016)
This Query will Return current and Previous 12 month Start Date..
==========================================
DECLARE @month int=0
DECLARE @table TABLE (LastYearDate DATETIME)
WHILE @month<12
BEGIN
INSERT @table
( LastYearDate )
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-@month,0)
SET @month+=1
END
SELECT * from @table
==================================
I hope this query will Help you..

Strongly suggest NOT using this method, absolutely horrible performance, hundred times slower than the other.

😎

The code as a function

CREATE FUNCTION dbo.LastNMonth_neerajprayag

(

@INPUT_DATE DATE

)

RETURNS @table TABLE (LastYearDate DATETIME) WITH SCHEMABINDING

AS

BEGIN

DECLARE @month int=0

WHILE @month<12

BEGIN

INSERT @table ( LastYearDate )

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@INPUT_DATE)-@month,0)

SET @month+=1

END

RETURN

END

The previous test harness with the while loop multi statement table valued function

/****************************************************************************************

1. Create and populate sample calendar table date range 2010 to 2017 (simplified)

****************************************************************************************/

IF OBJECT_ID(N'dbo.LastNMonth_Luis') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Luis ;

IF OBJECT_ID(N'dbo.LastNMonth_Alan') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Alan ;

IF OBJECT_ID(N'dbo.LastNMonth_Variable') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Variable ;

IF OBJECT_ID(N'dbo.LastNMonth_Fixed') IS NOT NULL DROP FUNCTION dbo.LastNMonth_Fixed ;

IF OBJECT_ID(N'dbo.LastNMonth_neerajprayag') IS NOT NULL DROP FUNCTION dbo.LastNMonth_neerajprayag;

IF OBJECT_ID('dbo.dim_date') IS NOT NULL DROP TABLE dbo.dim_date ;