October 12, 2012 at 5:45 am
Hi, I'm trying to figure out how to get the nearest date from table2 using the date from table1.
Here's an example:
CREATE TABLE [#mySchedules](
[id] [int] NOT NULL,
[recorddate] [datetime] NULL,
[name] [nvarchar](255) NULL
) ON [PRIMARY]
GO
CREATE TABLE [#myPeriods](
[id] [int] NOT NULL,
[startdate] [datetime] NULL,
[span] INT NULL
) ON [PRIMARY]
GO
INSERT INTO [#mySchedules] (id, recorddate, name)
SELECT 1,'2010-06-01','schedule1' UNION ALL
SELECT 2,'2010-07-20','schedule2'
INSERT INTO [#myPeriods] (id, [startdate], [span])
SELECT 1,'2010-05-01',2 UNION ALL
SELECT 2,'2010-05-25',2 UNION ALL
SELECT 3,'2010-07-01',2 UNION ALL
SELECT 4,'2010-07-25',2 UNION ALL
SELECT 5,'2010-07-05',2
The result should be:
when #mySchedules.id=1 then return #myPeriods.id=2
when #mySchedules.id=2 then return #myPeriods.id=4
Thanks for your time.
October 12, 2012 at 6:06 am
I'm on my lunch waiting in the car for something, so this is untested and coded together using the text editor on my phone.
Would this work for you?
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x CROSS JOIN CTE4 y)
SELECT a.id, recorddate, name, b.id
FROM [#mySchedules] a
CROSS APPLY (SELECT TOP 1 id, [startdate], [span], dd
FROM [#myPeriods]
CROSS APPLY tally
CROSS APPLY (SELECT DATEADD(DD,N,[startdate])
UNION ALL SELECT DATEADD(DD,-N,[startdate])) dates(dd)
WHERE a.recorddate = dd
ORDER BY N) b;
October 12, 2012 at 6:26 am
This is quite a bit different to Craig's, it would be interesting to discover the performance of them both:
SELECT s.*,
ID= CASE WHEN x.lag < y.lag THEN x.ID ELSE y.ID END,
startdate= CASE WHEN x.lag < y.lag THEN x.startdate ELSE y.startdate END,
span= CASE WHEN x.lag < y.lag THEN x.span ELSE y.span END
FROM #mySchedules s
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,p.startdate,s.recorddate)
FROM #myPeriods p
WHERE p.startdate < s.recorddate
ORDER BY p.startdate DESC
) x
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,s.recorddate,p.startdate)
FROM #myPeriods p
WHERE p.startdate > s.recorddate
ORDER BY p.startdate ASC
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 6:28 am
Cadavre (10/12/2012)
I'm on my lunch waiting in the car for something, so this is untested and coded together using the text editor on my phone.Would this work for you?
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x CROSS JOIN CTE4 y)
SELECT a.id, recorddate, name, b.id
FROM [#mySchedules] a
CROSS APPLY (SELECT TOP 1 id, [startdate], [span], dd
FROM [#myPeriods]
CROSS APPLY tally
CROSS APPLY (SELECT DATEADD(DD,N,[startdate])
UNION ALL SELECT DATEADD(DD,-N,[startdate])) dates(dd)
WHERE a.recorddate = dd
ORDER BY N) b;
Heh now that's dedication 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 6:31 am
I'll check when I get back from lunch. My gut feeling is that mine will be heavier on reads whilst yours is heavier on scans.
Not a clue about which will be quicker. . . but I think mine will run in to problems when the dates are far apart due to the DATEADD function hitting 1753-01-01 and attempting to keep going.
ChrisM@Work (10/12/2012)
Heh now that's dedication 😀
Felt I needed to stress how untested it was 😛
October 12, 2012 at 6:35 am
Cadavre (10/12/2012)
I'll check when I get back from lunch. My gut feeling is that mine will be heavier on reads whilst yours is heavier on scans.Not a clue about which will be quicker. . . but I think mine will run in to problems when the dates are far apart due to the DATEADD function hitting 1753-01-01 and attempting to keep going.
ChrisM@Work (10/12/2012)
Heh now that's dedication 😀Felt I needed to stress how untested it was 😛
It works with the sample data and I don't see why it shouldn't work with real data, it looks sound.
I'm working on another project right now and won't get the opportunity to test until later this afternoon. It's impossible to say from the actual plans of course, two or three rows is pretty meaningless.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 6:44 am
I'm not back yet, but will be leaving soon as it looks like they're nearly finished so can test in 30 minutes or so. But could your code not be reduced to this: -
SELECT s.*, x.id
FROM #mySchedules s
OUTER APPLY (SELECT TOP 1 p.id
FROM #myPeriods p
ORDER BY CASE WHEN DATEDIFF(DAY, p.startdate, s.recorddate) < 0
THEN DATEDIFF(DAY, p.startdate, s.recorddate) * - 1
ELSE DATEDIFF(DAY, p.startdate, s.recorddate) END
) x;
October 12, 2012 at 7:25 am
Cadavre (10/12/2012)
I'm not back yet, but will be leaving soon as it looks like they're nearly finished so can test in 30 minutes or so. But could your code not be reduced to this: -
SELECT s.*, x.idFROM #mySchedules s
OUTER APPLY (
SELECT TOP 1 p.id
FROM #myPeriods p
CROSS APPLY (SELECT lag = DATEDIFF(DAY, p.startdate, s.recorddate)) y
ORDER BY
CASE WHEN s.recorddate < p.startdate THEN y.lag * - 1 ELSE y.lag END
) x;
It works with the sample data, and it apparently cuts the cost in half. Nice work.
Also, a little easier to relate to:
SELECT s.*, x.id
FROM #mySchedules s
OUTER APPLY (
SELECT TOP 1 p.id
FROM #myPeriods p
ORDER BY ABS(DATEDIFF(DAY, p.startdate, s.recorddate))
) x;
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 7:35 am
OK, I'm back in the office 🙂
IF object_id('tempdb..#mySchedules') IS NOT NULL
BEGIN
DROP TABLE #mySchedules;
END;
SELECT TOP 1000000
[id], DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366) + 1),CAST('2010' AS DATE)) AS [recorddate],
'schedule'+CAST(a.id AS VARCHAR(7)) AS [name]
INTO #mySchedules
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
) a(id);
CREATE CLUSTERED INDEX cl_mySchedules_id ON #mySchedules(id);
IF object_id('tempdb..#myPeriods') IS NOT NULL
BEGIN
DROP TABLE #myPeriods;
END;
SELECT TOP 1000000
IDENTITY(INT,1,1) AS [id],
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366) + 1),CAST('2010' AS DATE)) AS [startdate],
2 AS [span]
INTO #myPeriods
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
CREATE CLUSTERED INDEX cl_myPeriods_id ON #myPeriods(id);
/*To take display time out of the equation*/
DECLARE @HOLDER_VARIABLE INT, @HOLDER_VARIABLE_2 INT;
PRINT '========== CADAVRE ==========';
SET STATISTICS TIME, IO ON;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x CROSS JOIN CTE4 y)
SELECT @HOLDER_VARIABLE = a.id, @HOLDER_VARIABLE_2 = b.id
FROM [#mySchedules] a
CROSS APPLY (SELECT TOP 1 id, [startdate], [span], dd
FROM [#myPeriods]
CROSS APPLY tally
CROSS APPLY (SELECT DATEADD(DD,N,[startdate])
UNION ALL SELECT DATEADD(DD,-N,[startdate])) dates(dd)
WHERE a.recorddate = dd
ORDER BY N) b;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== CHRIS ==========';
SET STATISTICS TIME, IO ON;
SELECT @HOLDER_VARIABLE = s.id, @HOLDER_VARIABLE_2 = CASE WHEN x.lag < y.lag THEN x.ID ELSE y.ID END
FROM #mySchedules s
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,p.startdate,s.recorddate)
FROM #myPeriods p
WHERE p.startdate < s.recorddate
ORDER BY p.startdate DESC
) x
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,s.recorddate,p.startdate)
FROM #myPeriods p
WHERE p.startdate > s.recorddate
ORDER BY p.startdate ASC
) y;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== REDUCED CHRIS'' SOLUTION ==========';
SET STATISTICS TIME, IO ON;
SELECT @HOLDER_VARIABLE = s.id, @HOLDER_VARIABLE_2 = x.id
FROM #mySchedules s
OUTER APPLY (SELECT TOP 1 p.id
FROM #myPeriods p
ORDER BY CASE WHEN DATEDIFF(DAY, p.startdate, s.recorddate) < 0
THEN DATEDIFF(DAY, p.startdate, s.recorddate) * - 1
ELSE DATEDIFF(DAY, p.startdate, s.recorddate) END
) x;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== REDUCED CHRIS'' SOLUTION 2 ==========';
SET STATISTICS TIME, IO ON;
SELECT @HOLDER_VARIABLE = s.id, @HOLDER_VARIABLE_2 = x.id
FROM #mySchedules s
OUTER APPLY (SELECT TOP 1 p.id
FROM #myPeriods p
ORDER BY ABS(DATEDIFF(DAY, p.startdate, s.recorddate))
) x;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
(1000000 row(s) affected)
(1000000 row(s) affected)
========== CADAVRE ==========
Table '#mySchedules'. Scan count 5, logical reads 4954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 16964, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 4, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3392 ms, elapsed time = 1485 ms.
================================================================================
========== CHRIS ==========
Table '#mySchedules'. Scan count 5, logical reads 4954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 732, logical reads 7656043, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 2, logical reads 4968, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 205767 ms, elapsed time = 60343 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION ==========
Table '#mySchedules'. Scan count 5, logical reads 4954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 366, logical reads 909144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 254999 ms, elapsed time = 65055 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION 2 ==========
Table '#mySchedules'. Scan count 5, logical reads 4954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 366, logical reads 909144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 194248 ms, elapsed time = 52025 ms.
================================================================================
Looking at the results suggests to me that I did something wrong in the testing 😀
If not though, then the huge difference is extremely surprising.
October 12, 2012 at 8:28 am
Cadavre (10/12/2012)
OK, I'm back in the office 🙂...
Looking at the results suggests to me that I did something wrong in the testing 😀
If not though, then the huge difference is extremely surprising.
It's a huge difference.
I've just tried cheating by putting an ordinary index on startdate and the results are a little weird - possibly because my poor lappy is getting it's a$$ kicked.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 9:21 am
ChrisM@Work (10/12/2012)
It's a huge difference.I've just tried cheating by putting an ordinary index on startdate and the results are a little weird - possibly because my poor lappy is getting it's a$$ kicked.
Not sure I'd call that cheating 🙂
OK, I've added the following the script above: -
CREATE NONCLUSTERED INDEX nc_myPeriods_startdate ON #myPeriods([startdate]);
CREATE NONCLUSTERED INDEX nc_mySchedules_recorddate ON #mySchedules([recorddate]);
New results: -
(1000000 row(s) affected)
(1000000 row(s) affected)
========== CADAVRE ==========
Table 'Worktable'. Scan count 1, logical reads 3381566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 1, logical reads 1610, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2112, physical reads 0, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 122750 ms, elapsed time = 123038 ms.
================================================================================
========== CHRIS ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 732, logical reads 377253, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1453 ms, elapsed time = 1449 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 366, logical reads 591090, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 206500 ms, elapsed time = 207039 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION 2 ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 366, logical reads 591090, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 153062 ms, elapsed time = 153687 ms.
================================================================================
So the indexes have increased the timing for my code by 2 minutes. . . from approx 1 and a half seconds to 2 minutes 3 seconds. Your original code has dropped from 1 minute to 1 and a half seconds. The reduced forms of your code now take a huge amount of time. . .
I believe it's because mine is no longer producing a parallel query plan. Could do with some investigation at some point, but it's Friday. . . 😀
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x CROSS JOIN CTE4 y)
SELECT @HOLDER_VARIABLE = a.id, @HOLDER_VARIABLE_2 = b.id
FROM [#mySchedules] a
CROSS APPLY (SELECT TOP 1 id, [startdate], [span], dd
FROM [#myPeriods] WITH (INDEX(0))
CROSS APPLY tally
CROSS APPLY (SELECT DATEADD(DD,N,[startdate])
UNION ALL SELECT DATEADD(DD,-N,[startdate])) dates(dd)
WHERE a.recorddate = dd
ORDER BY N) b;
It's a massive improvement.
IF object_id('tempdb..#mySchedules') IS NOT NULL
BEGIN
DROP TABLE #mySchedules;
END;
SELECT TOP 1000000
[id], DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366) + 1),CAST('2010' AS DATE)) AS [recorddate],
'schedule'+CAST(a.id AS VARCHAR(7)) AS [name]
INTO #mySchedules
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
) a(id);
CREATE CLUSTERED INDEX cl_mySchedules_id ON #mySchedules(id);
IF object_id('tempdb..#myPeriods') IS NOT NULL
BEGIN
DROP TABLE #myPeriods;
END;
SELECT TOP 1000000
IDENTITY(INT,1,1) AS [id],
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 366) + 1),CAST('2010' AS DATE)) AS [startdate],
2 AS [span]
INTO #myPeriods
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
CREATE CLUSTERED INDEX cl_myPeriods_id ON #myPeriods(id);
CREATE NONCLUSTERED INDEX nc_myPeriods_startdate ON #myPeriods([startdate]);
CREATE NONCLUSTERED INDEX nc_mySchedules_recorddate ON #mySchedules([recorddate]);
/*To take display time out of the equation*/
DECLARE @HOLDER_VARIABLE INT, @HOLDER_VARIABLE_2 INT;
PRINT '========== CADAVRE ==========';
SET STATISTICS TIME, IO ON;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE4 x CROSS JOIN CTE4 y)
SELECT @HOLDER_VARIABLE = a.id, @HOLDER_VARIABLE_2 = b.id
FROM [#mySchedules] a
CROSS APPLY (SELECT TOP 1 id, [startdate], [span], dd
FROM [#myPeriods] WITH (INDEX(0))
CROSS APPLY tally
CROSS APPLY (SELECT DATEADD(DD,N,[startdate])
UNION ALL SELECT DATEADD(DD,-N,[startdate])) dates(dd)
WHERE a.recorddate = dd
ORDER BY N) b;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== CHRIS ==========';
SET STATISTICS TIME, IO ON;
SELECT @HOLDER_VARIABLE = s.id, @HOLDER_VARIABLE_2 = CASE WHEN x.lag < y.lag THEN x.ID ELSE y.ID END
FROM #mySchedules s
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,p.startdate,s.recorddate)
FROM #myPeriods p
WHERE p.startdate < s.recorddate
ORDER BY p.startdate DESC
) x
OUTER APPLY (
SELECT TOP 1 *, lag = DATEDIFF(DAY,s.recorddate,p.startdate)
FROM #myPeriods p
WHERE p.startdate > s.recorddate
ORDER BY p.startdate ASC
) y;
SET STATISTICS TIME, IO OFF;
PRINT REPLICATE('=',80);
========== CADAVRE ==========
Table 'Worktable'. Scan count 1, logical reads 5432, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2113, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 662 ms.
================================================================================
========== CHRIS ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#myPeriods'. Scan count 732, logical reads 377253, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#mySchedules'. Scan count 1, logical reads 2113, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1547 ms, elapsed time = 1541 ms.
================================================================================
October 12, 2012 at 9:29 am
Here are the timings from a slender 1 million row Periods table, with an ordinary index on startdate:
========== CADAVRE ==========
Table 'Worktable'. Scan count 1, logical reads 254686, physical reads 0, read-ahead reads 0,
Table '#myPeriods'. Scan count 1, logical reads 161, physical reads 0, read-ahead reads 0,
Table '#mySchedules'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 7,
SQL Server Execution Times:
CPU time = 23962 ms, elapsed time = 25219 ms.
================================================================================
========== CHRIS ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0,
Table '#myPeriods'. Scan count 732, logical reads 251501, physical reads 0, read-ahead reads 0,
Table '#mySchedules'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0,
SQL Server Execution Times:
CPU time = 1747 ms, elapsed time = 1943 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0,
Table '#myPeriods'. Scan count 366, logical reads 91134, physical reads 0, read-ahead reads 0,
Table '#mySchedules'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0,
SQL Server Execution Times:
CPU time = 33883 ms, elapsed time = 35126 ms.
================================================================================
========== REDUCED CHRIS' SOLUTION 2 ==========
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0,
Table '#myPeriods'. Scan count 366, logical reads 91134, physical reads 0, read-ahead reads 0,
Table '#mySchedules'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0,
SQL Server Execution Times:
CPU time = 23510 ms, elapsed time = 24115 ms.
================================================================================
ps Craig, if you're in the Hob at 6.15, I'll buy you a pint 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 9:37 am
ChrisM@Work (10/12/2012)
ps Craig, if you're in the Hob at 6.15, I'll buy you a pint 😉
That the one on Broad Street? I'm in the process of moving house, we couldn't afford Reading so we've headed to Basingstoke so no more evenings in the pub for me. I'm on the lookout for a "new local" 😀
October 12, 2012 at 9:52 am
Cadavre (10/12/2012)
ChrisM@Work (10/12/2012)
ps Craig, if you're in the Hob at 6.15, I'll buy you a pint 😉That the one on Broad Street? I'm in the process of moving house, we couldn't afford Reading so we've headed to Basingstoke so no more evenings in the pub for me. I'm on the lookout for a "new local" 😀
That's the one, very handy it is too. Don't know many pubs in Basingstoke - the Millstone's quite good, also the Way Inn, handy for the station. Good luck with your move, we were lucky finding this place.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply