July 22, 2014 at 10:50 am
ChrisM@home (7/22/2014)
@wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.
Sort of like this:
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) );
WITH e1(n) as (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) as (SELECT 1 FROM e2 a CROSS JOIN e2 b),
eTally(n) as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 n
FROM eTally;
END
You may want to compare the actual execution plans between your code and this code.
July 22, 2014 at 12:54 pm
Thank you for help.
From "20-(MAX([sequence number])%20)" I got an idea.
Finally, I use while loop to complete it.
July 23, 2014 at 4:21 am
That's a shame about the WHILE loop. I would encourage you to use one of the set-based options presented for you as they will scale much better.
How many claims do you have to process?
July 23, 2014 at 4:29 am
Lynn Pettis (7/22/2014)
ChrisM@home (7/22/2014)
@wBob, you might find a TOP() -limited IBG-style inline tally even more efficient.Sort of like this:
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) );
WITH e1(n) as (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) as (SELECT 1 FROM e2 a CROSS JOIN e2 b),
eTally(n) as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 n
FROM eTally;
END
You may want to compare the actual execution plans between your code and this code.
Almost. I meant inline as in the tally table is built on-the-fly:
-- Demonstrate a solution using the sample data
WITH
e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt (n)),
e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
SELECT
d.[Claim number],
[sequence number] = d.LastSequenceNumber + x.n
FROM (
SELECT
[Claim number],
RowsToConstruct = 20-(MAX([sequence number])%20),
LastSequenceNumber = MAX([sequence number])
FROM #SampleTable
GROUP BY [Claim number]
) d
CROSS APPLY (
SELECT TOP (RowsToConstruct)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM e4
) x
No more rows are constructed than are needed.
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 23, 2014 at 5:18 am
I did trial that and honestly there is not a lot in it, with solution 1 (mine) slightly faster 5-10% on average at scale of 1 million. My test rig:
------------------------------------------------------------------------------------------------
-- Setup START
-- http://www.sqlservercentral.com/Forums/Topic1594823-2799-2.aspx?Update=1
------------------------------------------------------------------------------------------------
USE tempdb
GO
IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims
CREATE TABLE #claims
(
claimNumberINT,
maxSequenceINT,
PRIMARY KEY ( claimNumber, maxSequence )
)
GO
;WITH cte AS
(
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO #claims ( claimNumber, maxSequence )
--VALUES ( 111, 17 ), ( 222, 38 ), ( 333, 20 )
SELECT rn, rn % 33
FROM cte
GO
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )
INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
END
GO
------------------------------------------------------------------------------------------------
-- Solution 1 START
------------------------------------------------------------------------------------------------
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- This was good at scale
SELECT claimNumber, maxSequence + x.x AS maxSequence
INTO #tmp1
FROM (
SELECT
claimNumber,
MAX(maxSequence) maxSequence,
( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]
FROM #claims
GROUP BY claimNumber
) c
CROSS JOIN dbo.numbers x
WHERE x.x <= [rollupTo] - maxSequence
-- Solution 1 END
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- Solution 2 START
-- ChrisM@Work
------------------------------------------------------------------------------------------------
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- Demonstrate a solution using the sample data
;WITH
e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt (n)),
e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
SELECT
d.claimNumber,
maxSequence = d.LastSequenceNumber + x.n
INTO #tmp2
FROM (
SELECT
claimNumber,
RowsToConstruct = 20-(MAX(maxSequence)%20),
LastSequenceNumber = MAX(maxSequence)
FROM #claims
GROUP BY claimNumber
) d
CROSS APPLY (
SELECT TOP (RowsToConstruct)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM e4
) x
GO
-- Solution 2 END
------------------------------------------------------------------------------------------------
See attachment for results. Call it a draw? : )
July 23, 2014 at 5:37 am
wBob (7/23/2014)
I did trial that and honestly there is not a lot in it, with solution 1 (mine) slightly faster 5-10% on average at scale of 1 million. My test rig:
-------------------------------------------------------------------------------------------------- Setup START
-- http://www.sqlservercentral.com/Forums/Topic1594823-2799-2.aspx?Update=1
------------------------------------------------------------------------------------------------
USE tempdb
GO
IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims
CREATE TABLE #claims
(
claimNumberINT,
maxSequenceINT,
PRIMARY KEY ( claimNumber, maxSequence )
)
GO
;WITH cte AS
(
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO #claims ( claimNumber, maxSequence )
--VALUES ( 111, 17 ), ( 222, 38 ), ( 333, 20 )
SELECT rn, rn % 33
FROM cte
GO
IF OBJECT_ID('dbo.numbers') IS NULL
BEGIN
CREATE TABLE dbo.numbers ( x INT, CONSTRAINT PK_numbers PRIMARY KEY ( x ) )
INSERT INTO dbo.numbers ( x )
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) x
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
END
GO
------------------------------------------------------------------------------------------------
-- Solution 1 START
------------------------------------------------------------------------------------------------
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- This was good at scale
SELECT claimNumber, maxSequence + x.x AS maxSequence
INTO #tmp1
FROM (
SELECT
claimNumber,
MAX(maxSequence) maxSequence,
( ( MAX(maxSequence) / 20 ) + 1 ) * 20 [rollupTo]
FROM #claims
GROUP BY claimNumber
) c
CROSS JOIN dbo.numbers x
WHERE x.x <= [rollupTo] - maxSequence
-- Solution 1 END
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- Solution 2 START
-- ChrisM@Work
------------------------------------------------------------------------------------------------
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- Demonstrate a solution using the sample data
;WITH
e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt (n)),
e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b),
e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b)
SELECT
d.claimNumber,
maxSequence = d.LastSequenceNumber + x.n
INTO #tmp2
FROM (
SELECT
claimNumber,
RowsToConstruct = 20-(MAX(maxSequence)%20),
LastSequenceNumber = MAX(maxSequence)
FROM #claims
GROUP BY claimNumber
) d
CROSS APPLY (
SELECT TOP (RowsToConstruct)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM e4
) x
GO
-- Solution 2 END
------------------------------------------------------------------------------------------------
See attachment for results. Call it a draw? : )
Apart from the tally table being hard or inline, they're logically identical queries.
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 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply