May 11, 2016 at 9:38 pm
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
May 11, 2016 at 10:08 pm
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
May 12, 2016 at 5:33 am
Thanks to everyone so far for their innovative solutions; I thought it might trigger quite a bit of interest.
May 12, 2016 at 7:56 am
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
May 13, 2016 at 12:07 am
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..
May 13, 2016 at 1:01 am
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 ;
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
-- Function #5 While Loop
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
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_neerajprayag 01');
SELECT
@DATE_BUCKET = LN.LastYearDate
FROM #tmp T
CROSS APPLY dbo.LastNMonth_neerajprayag(T.SomeDate) LN
INSERT INTO @TIMER(T_TEXT) VALUES('dbo.LastNMonth_neerajprayag 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');
INSERT INTO @TIMER(T_TEXT) VALUES('dbo.LastNMonth_neerajprayag 02');
SELECT
@DATE_BUCKET = LN.LastYearDate
FROM #tmp T
CROSS APPLY dbo.LastNMonth_neerajprayag(T.SomeDate) LN
INSERT INTO @TIMER(T_TEXT) VALUES('dbo.LastNMonth_neerajprayag 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;
select @@VERSION
Results
T_TEXT DURATION
------------------------------ -----------
DRY RUN dbo.dim_date 0
DRY RUN #tmp 20000
dbo.LastNMonth_Alan 01 330000
dbo.LastNMonth_Alan 02 390001
dbo.LastNMonth_Fixed 02 420000
dbo.LastNMonth_Fixed 01 540001
dbo.LastNMonth_Luis 02 700001
dbo.LastNMonth_Luis 01 710001
dbo.LastNMonth_Variable 01 770001
dbo.LastNMonth_Variable 02 880002
dbo.LastNMonth_neerajprayag 01 22714035
dbo.LastNMonth_neerajprayag 02 22794035
May 13, 2016 at 12:43 pm
TheSQLGuru (5/11/2016)
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! 😀
Things like Alan's trick makes it so much worth while attending to this site, mind you not the first time he comes up with a cleaver solution.
😎
May 13, 2016 at 10:30 pm
Eirikur Eiriksson (5/13/2016)
TheSQLGuru (5/11/2016)
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! 😀Things like Alan's trick makes it so much worth while attending to this site, mind you not the first time he comes up with a cleaver solution.
😎
Thanks you Eirikur 😎
-- Itzik Ben-Gan 2001
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply