July 11, 2016 at 9:55 am
Eliminated that pesky spill:
== JLS1 ==========================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'testtable'. Scan count 34, logical reads 40917, 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 'Workfile'. 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.
SQL Server Execution Times:
CPU time = 49011 ms, elapsed time = 7503 ms.
== CJM2 ==========================================================================
Table 'testtable'. Scan count 17, logical reads 25666, 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 'Workfile'. 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.
SQL Server Execution Times:
CPU time = 61357 ms, elapsed time = 5140 ms.
PRINT '== CJM2 =========================================================================='
SET STATISTICS IO, TIME ON
;WITH C2 AS (
SELECT
d.code ,
d.ts,
cnt = SUM(type) OVER(PARTITION BY d.code ORDER BY d.ts, d.type DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - d.sub
FROM testtable m
CROSS APPLY (
VALUES
(CAST(m.Code AS VARCHAR(500)), CAST(m.StartDate AS DATETIME), CAST(1 AS SMALLINT), CAST(1 AS TINYINT)),
(CAST(m.Code AS VARCHAR(500)), CAST(m.EndDate AS DATETIME), CAST(-1 AS SMALLINT), CAST(0 AS TINYINT))
) d (code, ts, type, sub)
),
C3 AS (
SELECT code, ts,
grpnum = ((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2)
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
FROM C3
GROUP BY code, grpnum
ORDER BY code;
SET STATISTICS IO, TIME OFF -- 2782
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
July 11, 2016 at 10:02 am
Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀
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
July 11, 2016 at 10:05 am
ChrisM@Work (7/11/2016)
Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀
I'll tell you what I'll do Chris......as its only a home laptop that I am using...I'll restart the sql service and begin again from the top.
now ...please confirm what indexes you have on the table.
cheers
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2016 at 10:11 am
J Livingston SQL (7/11/2016)
ChrisM@Work (7/11/2016)
Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀I'll tell you what I'll do Chris......as its only a home laptop that I am using...I'll restart the sql service and begin again from the top.
now ...please confirm what indexes you have on the table.
cheers
You shouldn't have to do that! Oh, you're over there...does it run on batteries or steam?
The clustered index plus the two original ordinary indexes, and a new index on Code, EndDate and StartDate - definition above.
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
July 11, 2016 at 10:21 am
ChrisM@Work (7/11/2016)
J Livingston SQL (7/11/2016)
ChrisM@Work (7/11/2016)
Assuming you're in a playpen, CHECKPOINT then dbcc dropcleanbuffers. You might free up a few kb 😀I'll tell you what I'll do Chris......as its only a home laptop that I am using...I'll restart the sql service and begin again from the top.
now ...please confirm what indexes you have on the table.
cheers
You shouldn't have to do that! Oh, you're over there...does it run on batteries or steam?
The clustered index plus the two original ordinary indexes, and a new index on Code, EndDate and StartDate - definition above.
now now...dont be sarcastic! I know we have only just got "lectric" here...and just the one tap and an outside loo....but hey its good.:-P
will take a look in a bit....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2016 at 12:00 pm
sql restarted
10 runs each
comment ms
CJM2 18514
IBG 7228
IBG no traceflag9936
complete script applied below
--test harness adpated from here http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle
SET NOCOUNT ON;
USE tempdb;
---- helper function GetNums.....
-- jls: altenative use your own tally table/function
--IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL
-- DROP FUNCTION dbo.GetNums;
--GO
--CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
--AS
--RETURN
-- WITH
-- L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
-- L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
-- L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
-- L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
-- L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
-- L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
-- Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
-- SELECT TOP (@n) n FROM Nums ORDER BY n;
--GO
IF OBJECT_ID('dbo.testtable') IS NOT NULL DROP TABLE dbo.testtable;
CREATE TABLE dbo.testtable
(
id INT NOT NULL IDENTITY(1, 1),
Code VARCHAR(14) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
CONSTRAINT PK_Sessions PRIMARY KEY(id),
CONSTRAINT CHK_endtime_gteq_starttime CHECK (enddate >= startdate)
);
-- code to create and populate the table testtable with 5,000,000 rows
DECLARE
@num_code AS INT = 1000,
@intervals_per_code AS INT = 5000,
@start_period AS DATETIME = '20110101',
@end_period AS DATETIME = '20110114',
@max_duration_in_ms AS INT = 3600000; -- 60 min
--TRUNCATE TABLE dbo.testtable;
WITH C AS
(
SELECT 'Code' + RIGHT('00' + CAST(U.n AS VARCHAR(10)), 10) AS code,
DATEADD(ms, ABS(CHECKSUM(NEWID())) % 86400000,
DATEADD(day, ABS(CHECKSUM(NEWID())) % DATEDIFF(day, @start_period, @end_period), @start_period)) AS startdate
FROM dbo.GetNums(@num_code) AS U
CROSS JOIN dbo.GetNums(@intervals_per_code) AS I
)
INSERT INTO dbo.testtable WITH (TABLOCK) (code, startdate, enddate)
SELECT code,
startdate,
DATEADD(ms, ABS(CHECKSUM(NEWID())) % (@max_duration_in_ms + 1), startdate) AS enddate
FROM C;
-- indexes
CREATE UNIQUE INDEX idx_user_start_id ON dbo.testtable(code, startdate, id);
CREATE UNIQUE INDEX idx_user_end_id ON dbo.testtable(code, enddate, id);
CREATE UNIQUE NONCLUSTERED INDEX [idx_Code_Dates] ON [dbo].[testtable] ([Code] ASC,[enddate] ASC,[startdate] ASC) -- CM required
IF OBJECT_ID('#results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
Comment VARCHAR(20)
, StartTime DATETIME
, EndTime DATETIME
, Duration INT
)
GO
--- IBG----------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
WITH C1 AS
(
SELECT code, startdate AS ts, +1 AS type, 1 AS sub
FROM dbo.testtable
UNION ALL
SELECT code, enddate AS ts, -1 AS type, 0 AS sub
FROM dbo.testtable
),
C2 AS
(
SELECT C1.*,
SUM(type) OVER(PARTITION BY code ORDER BY ts, type DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - sub AS cnt
FROM C1
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #ibgdump
FROM C3
GROUP BY code, grpnum
ORDER BY code
OPTION (RECOMPILE, QUERYTRACEON 8649)
DROP TABLE #ibgdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'IBG', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
-- CJM2-----------------------------------------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
;WITH C2 AS
(
SELECT d.*, cnt = CAST(SUM(type) OVER(PARTITION BY d.code ORDER BY ts, type DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - sub AS TINYINT) -- 853760
FROM testtable m
CROSS APPLY (
VALUES
(CAST(m.Code AS VARCHAR(10)), CAST(StartDate AS DATETIME), CAST(1 AS SMALLINT), CAST(1 AS TINYINT)),
(CAST(m.Code AS VARCHAR(10)), CAST(EndDate AS DATETIME), CAST(-1 AS SMALLINT), CAST(0 AS TINYINT))
) d (Code, ts, type, sub)
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #cmdump
FROM C3
GROUP BY code, grpnum
ORDER BY code;
DROP TABLE #cmdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'CJM2', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
--IBG no trace--------------------------------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
WITH C1 AS
(
SELECT code, startdate AS ts, +1 AS type, 1 AS sub
FROM dbo.testtable
UNION ALL
SELECT code, enddate AS ts, -1 AS type, 0 AS sub
FROM dbo.testtable
),
C2 AS
(
SELECT C1.*,
SUM(type) OVER(PARTITION BY code ORDER BY ts, type DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - sub AS cnt
FROM C1
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #ibgdump
FROM C3
GROUP BY code, grpnum
ORDER BY code
DROP TABLE #ibgdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'IBG no traceflag', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
/*get average results */
SELECT comment
, AVG(duration) AS ms
FROM #Results
GROUP BY comment
--SELECT * FROM #Results
DROP TABLE #Results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2016 at 12:58 pm
J Livingston SQL (7/11/2016)
sql restarted10 runs each
comment msCJM2 18514
IBG 7228
IBG no traceflag9936
complete script applied below
--test harness adpated from here http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle
SET NOCOUNT ON;
USE tempdb;
---- helper function GetNums.....
-- jls: altenative use your own tally table/function
--IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL
-- DROP FUNCTION dbo.GetNums;
--GO
--CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
--AS
--RETURN
-- WITH
-- L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
-- L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
-- L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
-- L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
-- L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
-- L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
-- Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
-- SELECT TOP (@n) n FROM Nums ORDER BY n;
--GO
IF OBJECT_ID('dbo.testtable') IS NOT NULL DROP TABLE dbo.testtable;
CREATE TABLE dbo.testtable
(
id INT NOT NULL IDENTITY(1, 1),
Code VARCHAR(14) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
CONSTRAINT PK_Sessions PRIMARY KEY(id),
CONSTRAINT CHK_endtime_gteq_starttime CHECK (enddate >= startdate)
);
-- code to create and populate the table testtable with 5,000,000 rows
DECLARE
@num_code AS INT = 1000,
@intervals_per_code AS INT = 5000,
@start_period AS DATETIME = '20110101',
@end_period AS DATETIME = '20110114',
@max_duration_in_ms AS INT = 3600000; -- 60 min
--TRUNCATE TABLE dbo.testtable;
WITH C AS
(
SELECT 'Code' + RIGHT('00' + CAST(U.n AS VARCHAR(10)), 10) AS code,
DATEADD(ms, ABS(CHECKSUM(NEWID())) % 86400000,
DATEADD(day, ABS(CHECKSUM(NEWID())) % DATEDIFF(day, @start_period, @end_period), @start_period)) AS startdate
FROM dbo.GetNums(@num_code) AS U
CROSS JOIN dbo.GetNums(@intervals_per_code) AS I
)
INSERT INTO dbo.testtable WITH (TABLOCK) (code, startdate, enddate)
SELECT code,
startdate,
DATEADD(ms, ABS(CHECKSUM(NEWID())) % (@max_duration_in_ms + 1), startdate) AS enddate
FROM C;
-- indexes
CREATE UNIQUE INDEX idx_user_start_id ON dbo.testtable(code, startdate, id);
CREATE UNIQUE INDEX idx_user_end_id ON dbo.testtable(code, enddate, id);
CREATE UNIQUE NONCLUSTERED INDEX [idx_Code_Dates] ON [dbo].[testtable] ([Code] ASC,[enddate] ASC,[startdate] ASC) -- CM required
IF OBJECT_ID('#results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
Comment VARCHAR(20)
, StartTime DATETIME
, EndTime DATETIME
, Duration INT
)
GO
--- IBG----------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
WITH C1 AS
(
SELECT code, startdate AS ts, +1 AS type, 1 AS sub
FROM dbo.testtable
UNION ALL
SELECT code, enddate AS ts, -1 AS type, 0 AS sub
FROM dbo.testtable
),
C2 AS
(
SELECT C1.*,
SUM(type) OVER(PARTITION BY code ORDER BY ts, type DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - sub AS cnt
FROM C1
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #ibgdump
FROM C3
GROUP BY code, grpnum
ORDER BY code
OPTION (RECOMPILE, QUERYTRACEON 8649)
DROP TABLE #ibgdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'IBG', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
-- CJM2-----------------------------------------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
;WITH C2 AS
(
SELECT d.*, cnt = CAST(SUM(type) OVER(PARTITION BY d.code ORDER BY ts, type DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - sub AS TINYINT) -- 853760
FROM testtable m
CROSS APPLY (
VALUES
(CAST(m.Code AS VARCHAR(10)), CAST(StartDate AS DATETIME), CAST(1 AS SMALLINT), CAST(1 AS TINYINT)),
(CAST(m.Code AS VARCHAR(10)), CAST(EndDate AS DATETIME), CAST(-1 AS SMALLINT), CAST(0 AS TINYINT))
) d (Code, ts, type, sub)
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #cmdump
FROM C3
GROUP BY code, grpnum
ORDER BY code;
DROP TABLE #cmdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'CJM2', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
--IBG no trace--------------------------------------------------------------------------
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME;
WITH C1 AS
(
SELECT code, startdate AS ts, +1 AS type, 1 AS sub
FROM dbo.testtable
UNION ALL
SELECT code, enddate AS ts, -1 AS type, 0 AS sub
FROM dbo.testtable
),
C2 AS
(
SELECT C1.*,
SUM(type) OVER(PARTITION BY code ORDER BY ts, type DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - sub AS cnt
FROM C1
),
C3 AS
(
SELECT code, ts,
((ROW_NUMBER() OVER(PARTITION BY code ORDER BY ts) - 1) / 2 + 1)
AS grpnum
FROM C2
WHERE cnt = 0
)
SELECT code, MIN(ts) AS startdate, max(ts) AS enddate
into #ibgdump
FROM C3
GROUP BY code, grpnum
ORDER BY code
DROP TABLE #ibgdump
SELECT @EndTime = getdate()
INSERT #Results
SELECT'IBG no traceflag', @StartTime, @EndTime ,DATEDIFF(ms,@StartTime,@EndTime)
GO 10
/*get average results */
SELECT comment
, AVG(duration) AS ms
FROM #Results
GROUP BY comment
--SELECT * FROM #Results
DROP TABLE #Results
Only one core in that home lappy by any chance?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 11, 2016 at 1:06 pm
nope
http://ark.intel.com/products/88195/Intel-Core-i7-6700K-Processor-8M-Cache-up-to-4_20-GHz
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 11, 2016 at 1:11 pm
J Livingston SQL (7/11/2016)
nopehttp://ark.intel.com/products/88195/Intel-Core-i7-6700K-Processor-8M-Cache-up-to-4_20-GHz
Has to be tested on steam-powered lappy here then. Dreckly.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 11, 2016 at 1:15 pm
Dreckly.
wonder how many other people here know the meaning of that 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply