September 12, 2015 at 10:31 pm
I'm wondering if there is a better way to accomplish this task. @BoxesNeeded will change every time it runs. This method works, but something tells me there must be a shorter way to accomplish the same thing.
Thanks for reading.
declare @i as int,
@BoxesNeeded as int
select @i = 0,
@BoxesNeeded = 15
--drop table #temp
select @i as DDL
into #temp where 1=2
while @BoxesNeeded > @i begin
set @i = @i + 1
insert into #temp
select @i
end
select DDL
from #temp
September 12, 2015 at 11:16 pm
Quick suggestion with inline tally table
😎
DECLARE @BoxesNeeded INT = 15;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@BoxesNeeded) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6)
SELECT
NM.N
FROM NUMS NM;
September 13, 2015 at 4:10 am
Same concept as Eirikur but here is an alternative for the inline tally table. It doesn't seem to hurt performance to define all the "passes" up to 4,294,967,296 if you then only use a few of them ... but if you want all 4,294,967,296 rows then that WILL take a while 🙂
DECLARE @BoxesNeeded int
SELECT @BoxesNeeded = 15
; WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
SELECT number as DDL
INTO #temp
FROM Tally
WHERE Number <= @BoxesNeeded
SELECT *
FROM #temp
DROP TABLE #temp
September 13, 2015 at 6:08 am
There is a subtle difference between these two queries, when measured back to back on 10^6 numbers then the former is around 25% faster than the latter although the difference varies depending on the number of rows generated. It is possible to speed it up even further by increasing the number of values in the initial seeding but the gain is so small that normally I don't bother with it.
😎
Test harness (no temp table)
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @BoxesNeeded int
SELECT @BoxesNeeded = 1000000;
DECLARE @INT_BUCKET INT = 0;
DECLARE @TIMER TABLE (T_TEXT VARCHAR(50) NOT NULL,SET_SIZE INT NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 1',@BoxesNeeded);
; WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
SELECT @INT_BUCKET = number
FROM Tally
WHERE Number <= @BoxesNeeded;
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 1',@BoxesNeeded);
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 2',@BoxesNeeded);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@BoxesNeeded) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9,T T10)
SELECT
@INT_BUCKET = NM.N
FROM NUMS NM;
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 2',@BoxesNeeded);
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 3',@BoxesNeeded);
;WITH T(N) AS (SELECT N FROM (VALUES
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
) AS X(N))
, NUMS(N) AS (SELECT TOP(@BoxesNeeded) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5)
SELECT
@INT_BUCKET = NM.N
FROM NUMS NM;
INSERT INTO @TIMER(T_TEXT,SET_SIZE) VALUES ('Method 3',@BoxesNeeded);
SELECT
T.T_TEXT
,T.SET_SIZE
,DATEDIFF(MICROSECOND,MIN(T_TS),MAX(T.T_TS)) AS DURATION
FROM @TIMER T
GROUP BY T.T_TEXT
,T.SET_SIZE
ORDER BY T.SET_SIZE
,DURATION;
Results (2nd gen. i5 laptop)
T_TEXT SET_SIZE DURATION
---------- ----------- ---------
Method 3 1000000 168009
Method 2 1000000 175010
Method 1 1000000 222013
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply