반응형
숫자 테이블을 만들고 채우는 가장 좋은 방법은 무엇입니까?
숫자 테이블을 만들고 채우는 다양한 방법을 보았습니다. 그러나 하나를 만들고 채우는 가장 좋은 방법은 무엇입니까? "최고"는 가장 중요한 것부터 가장 낮은 것까지 정의됩니다.
- 최적의 인덱싱으로 생성 된 테이블
- 가장 빠르게 생성 된 행
- 만들고 채우는 데 사용되는 간단한 코드
숫자 표가 무엇인지 모르는 경우 여기를 참조하십시오. 보조 숫자 표 사용을 고려해야하는 이유는 무엇입니까?
다음은 웹과이 질문에 대한 답변에서 가져온 몇 가지 코드 예제입니다.
각 메서드에 대해 원본 코드를 수정하여 각각 동일한 테이블과 열 (NumbersTest 및 Number)을 10,000 개 행 또는 가능한 한 이에 가깝게 사용하도록 수정했습니다. 또한 원산지 링크를 제공했습니다.
여기에서 방법 1은 여기에서 매우 느린 루핑 방법입니다 .
평균 13.01 초가
가장 높게 3 번 제거되었습니다. 다음은 시간 (초)입니다. 12.42, 13.60
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest(Number INT IDENTITY(1,1))
SET NOCOUNT ON
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 100000
BEGIN
INSERT dbo.NumbersTest DEFAULT VALUES
END
SET NOCOUNT OFF
-- Add a primary key/clustered index to the numbers table
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
방법 2는 여기에서 훨씬 빠른 루프 하나입니다 여기에
1.1658 초 평균
1.117, 1.140, 1.203, 1.170, 1.173, 1.156, 1.203, 1.153, 1.173, 1.170 : 여기에 초 배 11 배 높은 제거 실행
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number INT NOT NULL);
DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.NumbersTest(Number) VALUES (@i);
SELECT @i = @i + 1;
END;
SET NOCOUNT OFF
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
방법 3 여기에서 코드를 기반으로 한 단일 INSERT가 있습니다.
평균 488.6 밀리 초가
11 번 가장 많이 제거되었습니다. 여기에는 686, 673, 623, 686,343,343,376,360,343,453이 있습니다.
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;WITH Nums(Number) AS
(SELECT 1 AS Number
UNION ALL
SELECT Number+1 FROM Nums where Number<10000
)
insert into NumbersTest(Number)
select Number from Nums option(maxrecursion 10000)
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
여기서 METHOD 4는 여기 에서 평균 348.3 밀리 초 (코드 중간의 "GO"로 인해 좋은 타이밍을 얻기가 어려웠습니다. 어떤 제안이라도 감사 할 것 입니다)에서 "반 루핑"방법 이
가장 높게 11 번 제거되었습니다. 시간 (밀리 초) : 356, 360, 283, 346, 360, 376, 326, 373, 330, 373
DROP TABLE NumbersTest
DROP TABLE #RunDate
CREATE TABLE #RunDate (RunDate datetime)
INSERT INTO #RunDate VALUES(GETDATE())
CREATE TABLE NumbersTest (Number int NOT NULL);
INSERT NumbersTest values (1);
GO --required
INSERT NumbersTest SELECT Number + (SELECT COUNT(*) FROM NumbersTest) FROM NumbersTest
GO 14 --will create 16384 total rows
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
SELECT CONVERT(varchar(20),datediff(ms,RunDate,GETDATE()))+' milliseconds' FROM #RunDate
SELECT COUNT(*) FROM NumbersTest
여기에서 방법 5 는 Philip Kelley의 답변 에서 단일 INSERT입니다.
평균 92.7 밀리 초가
가장 많이 11 번 제거되었으며 다음은 밀리 초 단위의 시간입니다 : 80, 96, 96, 93, 110, 110, 80, 76, 93, 93
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;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
--I removed Pass5, since I'm only populating the Numbers table to 10,000
Tally as (select row_number() over(order by C) as Number from Pass4)
INSERT NumbersTest
(Number)
SELECT Number
FROM Tally
WHERE Number <= 10000
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
여기에 방법 6 은 Mladen Prajdic 응답 의 단일 INSERT입니다.
평균 82.3 밀리 초가
11 번 가장 많이 제거되었으며 다음은 밀리 초 단위의 시간입니다 : 80, 80, 93, 76, 93, 63, 93, 76, 93, 76
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
INSERT INTO NumbersTest(Number)
SELECT TOP 10000 row_number() over(order by t1.number) as N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number);
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 7 여기에서의 코드에 기초하여 단일 INSERT이며 여기
56.3 밀리 평균
63, 50, 63, 46, 60, 63, 63, 46, 63, 46 : 여기 밀리 배 11 배 높은 제거 달렸다
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO NumbersTest
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
이 모든 방법을 살펴본 결과, 가장 빠르고 코드도 상당히 간단한 방법 7이 정말 마음에 듭니다.
나는 지옥처럼 빠른 이것을 사용합니다.
insert into Numbers(N)
select top 1000000 row_number() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2
SQL Server Management Studio 또는 sqlcmd에서이 작업을 수행하는 경우 배치 구분 기호를 사용하여 배치를 반복 할 수 있다는 사실을 사용할 수 있습니다.
CREATE TABLE Number (N INT IDENTITY(1,1) PRIMARY KEY NOT NULL);
GO
INSERT INTO Number DEFAULT VALUES;
GO 100000
그러면 Numbers테이블에 100000 개의 레코드가 삽입됩니다 .
느립니다. @KM.의 답변에서 METHOD 1과 비교하여 가장 느린 예입니다. 그러나 그것은 코드가 가볍습니다. 배치 삽입 후에 기본 키 제약 조건을 추가하여 속도를 다소 높일 수 있습니다.
나는 Itzik Ben-Gan의 루틴의 수많은 인쇄물에서 파생 된 다음 템플릿으로 시작합니다.
;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 from Tally where Number <= 1000000
"WHERE N <= 1000000"절은 출력을 1 백만에서 1 백만으로 제한하고 원하는 범위로 쉽게 조정할 수 있습니다.
이것은 WITH 절이므로 다음과 같이 INSERT ... SELECT ...로 작업 할 수 있습니다.
-- Sample use: create one million rows
CREATE TABLE dbo.Example (ExampleId int not null)
DECLARE @RowsToCreate int
SET @RowsToCreate = 1000000
-- "Table of numbers" data generator, as per Itzik Ben-Gan (from multiple sources)
;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)
INSERT Example (ExampleId)
select Number
from Tally
where Number <= @RowsToCreate
테이블을 만든 후 인덱싱하는 것이 가장 빠른 인덱싱 방법입니다.
아, "Tally"테이블이라고하겠습니다. 나는 이것이 일반적인 용어라고 생각하며 인터넷 검색을 통해 많은 트릭과 예제를 찾을 수 있습니다.
Azure 솔루션을 찾는 모든 사람
SET NOCOUNT ON
CREATE TABLE Numbers (n bigint PRIMARY KEY)
GO
DECLARE @numbers table(number int);
WITH numbers(number) as (
SELECT 1 AS number
UNION all
SELECT number+1 FROM numbers WHERE number<10000
)
INSERT INTO @numbers(number)
SELECT number FROM numbers OPTION(maxrecursion 10000)
INSERT INTO Numbers(n) SELECT number FROM @numbers
출처 : SQL Azure 팀 블로그 http://azure.microsoft.com/blog/2010/09/16/create-a-numbers-table-in-sql-azure/
이 스레드가 오래되었고 답변을 받았지만 방법 7에서 약간의 추가 성능을 끌어낼 수있는 방법이 있습니다.
대신에 (본질적으로 방법 7이지만 사용하기 쉬움) :
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.co
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
이 시도:
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T32]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T64]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T128]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T256]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T512]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T1024]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2048]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4096]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8192]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16384]
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
내 서버에서는 sys.objects에서 선택할 때 ~ 16-20ms와 달리 ~ 10ms가 걸립니다. 또한 sys.objects에있는 개체 수에 의존하지 않는 추가 이점이 있습니다. 꽤 안전하지만 기술적으로는 종속성이고 다른 하나는 어쨌든 더 빠릅니다. 변경하면 속도 향상이 BIT를 사용하는 것으로 간주됩니다.
DECLARE @BIT AS BIT = 0
에:
DECLARE @BIT AS BIGINT = 0
내 서버의 총 시간에 ~ 8-10ms를 추가합니다. 즉, 최대 1,000,000 개의 레코드로 확장 할 때 BIT 대 BIGINT는 더 이상 내 쿼리에 큰 영향을주지 않지만 sys.objects에서 약 680ms 대 ~ 730ms로 실행됩니다.
다음은 SQL Server 2008에 도입 된 Table Valued Constructors 를 활용 한 짧고 빠른 메모리 내 솔루션입니다 .
1,000,000 개의 행을 반환하지만 CROSS JOIN을 추가 / 제거하거나 TOP 절을 사용하여 수정할 수 있습니다.
;WITH v AS (SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) v(z))
SELECT N FROM (SELECT ROW_NUMBER() OVER (ORDER BY v1.z)-1 N FROM v v1
CROSS JOIN v v2 CROSS JOIN v v3 CROSS JOIN v v4 CROSS JOIN v v5 CROSS JOIN v v6) Nums
이는 즉석에서 빠르게 계산되거나 효율성 향상 을 위해 필드 의 기본 키와 함께 영구 테이블 ( 세그먼트 INTO뒤에 절 추가)에 저장 될 수 있습니다.SELECT NN
레코드 세트를 동적으로 생성하지 않고 주로 BIRT에서 보고서를 더 미화하기 위해 숫자 테이블을 사용합니다.
과거 10 년부터 미래 10 년까지 (더 자세한보고를위한 하루 중 시간) 테이블을 가지고 날짜도 똑같이합니다. '실제'데이터 테이블에 데이터가없는 경우에도 모든 날짜에 대한 값을 얻을 수 있다는 것은 깔끔한 트릭 입니다.
나는 이것을 만드는 데 사용하는 스크립트가 있습니다. (이것은 메모리에서 가져온 것입니다) :
drop table numbers; commit;
create table numbers (n integer primary key); commit;
insert into numbers values (0); commit;
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
행 수는 각 행마다 두 배가되므로 정말 거대한 테이블을 생성하는 데 많은 시간이 걸리지 않습니다.
한 번만 생성하기 때문에 빠르게 생성하는 것이 중요하다는 데 동의하지 않습니다. 그 비용은 그것에 대한 모든 액세스에 대해 상각되므로 그 시간은 상당히 중요하지 않습니다.
다음은 몇 가지 추가 방법입니다.
방법 1
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i int = 1;
INSERT INTO dbo.Numbers (Number)
VALUES (1),(2);
WHILE 2*@i < 1048576
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT Number + 2*@i
FROM dbo.Numbers;
SET @i = @@ROWCOUNT;
END
GO
SELECT COUNT(*) FROM Numbers AS RowCownt --1048576 rows
방법 2
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 0;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i <= 9
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT N.Number + POWER(4, @i) * D.Digit
FROM dbo.Numbers AS N
CROSS JOIN (VALUES(1),(2),(3)) AS D(Digit)
ORDER BY D.Digit, N.Number
SET @i = @i + 1;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
방법 3
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int identity NOT NULL PRIMARY KEY, T bit NULL);
WITH
T1(T) AS (SELECT T FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(T)) --10 rows
,T2(T) AS (SELECT A.T FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows
,T3(T) AS (SELECT A.T FROM T2 AS A CROSS JOIN T2 AS B CROSS JOIN T2 AS C) --1,000,000,000 rows
INSERT INTO dbo.Numbers(T)
SELECT TOP (1048576) NULL
FROM T3;
ALTER TABLE Numbers
DROP COLUMN T;
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
방법 4 , Alex Kuznetsov의 방어 데이터베이스 프로그래밍 책 에서 발췌
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 1 ;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i < 524289 --1048576
BEGIN;
INSERT INTO dbo.Numbers (Number)
SELECT Number + @i
FROM dbo.Numbers;
SET @i = @i * 2 ;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
방법 5 , SQL Server 2005 및 그 이상의 배열 및 목록에서 가져온 Erland Sommarskog 기사
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
WITH digits (d) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
SELECT 0)
INSERT INTO Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --999999 rows
요약 :
이 5 가지 방법 중 방법 3이 가장 빠른 것 같습니다.
제안 된 방법 중 일부는 시스템 개체를 기반으로합니다 (예 : 'sys.objects'). 그들은 이러한 시스템 개체에 우리의 숫자를 생성하기에 충분한 레코드가 포함되어 있다고 가정합니다.
나는 내 응용 프로그램에 속하지 않고 내가 완전히 제어 할 수없는 어떤 것도 기반으로하지 않을 것입니다. 예를 들어, 이러한 sys 테이블의 내용이 변경 될 수 있으며 테이블은 새 버전의 SQL 등에서 더 이상 유효하지 않을 수 있습니다.
해결책으로 레코드가있는 자체 테이블을 만들 수 있습니다. 그런 다음 이러한 시스템 관련 개체 대신 해당 개체를 사용합니다 (범위를 미리 알고 있으면 모든 숫자가있는 테이블이 괜찮을 것입니다. 그렇지 않으면 교차 조인을 수행 할 수 있습니다).
CTE 기반 솔루션은 잘 작동하지만 중첩 루프와 관련된 제한이 있습니다.
이것은 받아 들여진 대답을 재 포장 한 것입니다. 그러나 그것들을 모두 서로 비교할 수있는 방식으로-상위 3 개의 알고리즘이 비교되고 (그리고 다른 방법이 제외 된 이유를 설명하는 주석) 당신은 자신의 설정에 대해 실행할 수 있습니다. 원하는 순서의 크기로 각각의 성능을 확인하십시오.
SET NOCOUNT ON;
--
-- Set the count of numbers that you want in your sequence ...
--
DECLARE @NumberOfNumbers int = 10000000;
--
-- Some notes on choosing a useful length for your sequence ...
-- For a sequence of 100 numbers -- winner depends on preference of min/max/avg runtime ... (I prefer PhilKelley algo here - edit the algo so RowSet2 is max RowSet CTE)
-- For a sequence of 1k numbers -- winner depends on preference of min/max/avg runtime ... (Sadly PhilKelley algo is generally lowest ranked in this bucket, but could be tweaked to perform better)
-- For a sequence of 10k numbers -- a clear winner emerges for this bucket
-- For a sequence of 100k numbers -- do not test any looping methods at this size or above ...
-- the previous winner fails, a different method is need to guarantee the full sequence desired
-- For a sequence of 1MM numbers -- the statistics aren't changing much between the algorithms - choose one based on your own goals or tweaks
-- For a sequence of 10MM numbers -- only one of the methods yields the desired sequence, and the numbers are much closer than for smaller sequences
DECLARE @TestIteration int = 0;
DECLARE @MaxIterations int = 10;
DECLARE @MethodName varchar(128);
-- SQL SERVER 2017 Syntax/Support needed
DROP TABLE IF EXISTS #TimingTest
CREATE TABLE #TimingTest (MethodName varchar(128), TestIteration int, StartDate DateTime2, EndDate DateTime2, ElapsedTime decimal(38,0), ItemCount decimal(38,0), MaxNumber decimal(38,0), MinNumber decimal(38,0))
--
-- Conduct the test ...
--
WHILE @TestIteration < @MaxIterations
BEGIN
-- Be sure that the test moves forward
SET @TestIteration += 1;
/* -- This method has been removed, as it is BY FAR, the slowest method
-- This test shows that, looping should be avoided, likely at all costs, if one places a value / premium on speed of execution ...
--
-- METHOD - Fast looping
--
-- Prep for the test
DROP TABLE IF EXISTS [Numbers].[Test];
CREATE TABLE [Numbers].[Test] (Number INT NOT NULL);
-- Method information
SET @MethodName = 'FastLoop';
-- Record the start of the test
INSERT INTO #TimingTest(MethodName, TestIteration, StartDate)
SELECT @MethodName, @TestIteration, GETDATE()
-- Run the algorithm
DECLARE @i INT = 1;
WHILE @i <= @NumberOfNumbers
BEGIN
INSERT INTO [Numbers].[Test](Number) VALUES (@i);
SELECT @i = @i + 1;
END;
ALTER TABLE [Numbers].[Test] ADD CONSTRAINT PK_Numbers_Test_Number PRIMARY KEY CLUSTERED (Number)
-- Record the end of the test
UPDATE tt
SET
EndDate = GETDATE()
FROM #TimingTest tt
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
-- And the stats about the numbers in the sequence
UPDATE tt
SET
ItemCount = results.ItemCount,
MaxNumber = results.MaxNumber,
MinNumber = results.MinNumber
FROM #TimingTest tt
CROSS JOIN (
SELECT COUNT(Number) as ItemCount, MAX(Number) as MaxNumber, MIN(Number) as MinNumber FROM [Numbers].[Test]
) results
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
*/
/* -- This method requires GO statements, which would break the script, also - this answer does not appear to be the fastest *AND* seems to perform "magic"
--
-- METHOD - "Semi-Looping"
--
-- Prep for the test
DROP TABLE IF EXISTS [Numbers].[Test];
CREATE TABLE [Numbers].[Test] (Number INT NOT NULL);
-- Method information
SET @MethodName = 'SemiLoop';
-- Record the start of the test
INSERT INTO #TimingTest(MethodName, TestIteration, StartDate)
SELECT @MethodName, @TestIteration, GETDATE()
-- Run the algorithm
INSERT [Numbers].[Test] values (1);
-- GO --required
INSERT [Numbers].[Test] SELECT Number + (SELECT COUNT(*) FROM [Numbers].[Test]) FROM [Numbers].[Test]
-- GO 14 --will create 16384 total rows
ALTER TABLE [Numbers].[Test] ADD CONSTRAINT PK_Numbers_Test_Number PRIMARY KEY CLUSTERED (Number)
-- Record the end of the test
UPDATE tt
SET
EndDate = GETDATE()
FROM #TimingTest tt
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
-- And the stats about the numbers in the sequence
UPDATE tt
SET
ItemCount = results.ItemCount,
MaxNumber = results.MaxNumber,
MinNumber = results.MinNumber
FROM #TimingTest tt
CROSS JOIN (
SELECT COUNT(Number) as ItemCount, MAX(Number) as MaxNumber, MIN(Number) as MinNumber FROM [Numbers].[Test]
) results
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
*/
--
-- METHOD - Philip Kelley's algo
-- (needs tweaking to match the desired length of sequence in order to optimize its performance, relies more on the coder to properly tweak the algorithm)
--
-- Prep for the test
DROP TABLE IF EXISTS [Numbers].[Test];
CREATE TABLE [Numbers].[Test] (Number INT NOT NULL);
-- Method information
SET @MethodName = 'PhilKelley';
-- Record the start of the test
INSERT INTO #TimingTest(MethodName, TestIteration, StartDate)
SELECT @MethodName, @TestIteration, GETDATE()
-- Run the algorithm
; WITH
RowSet0 as (select 1 as Item union all select 1), -- 2 rows -- We only have to name the column in the first select, the second/union select inherits the column name
RowSet1 as (select 1 as Item from RowSet0 as A, RowSet0 as B), -- 4 rows
RowSet2 as (select 1 as Item from RowSet1 as A, RowSet1 as B), -- 16 rows
RowSet3 as (select 1 as Item from RowSet2 as A, RowSet2 as B), -- 256 rows
RowSet4 as (select 1 as Item from RowSet3 as A, RowSet3 as B), -- 65536 rows (65k)
RowSet5 as (select 1 as Item from RowSet4 as A, RowSet4 as B), -- 4294967296 rows (4BB)
-- Add more RowSetX to get higher and higher numbers of rows
-- Each successive RowSetX results in squaring the previously available number of rows
Tally as (select row_number() over (order by Item) as Number from RowSet5) -- This is what gives us the sequence of integers, always select from the terminal CTE expression
-- Note: testing of this specific use case has shown that making Tally as a sub-query instead of a terminal CTE expression is slower (always) - be sure to follow this pattern closely for max performance
INSERT INTO [Numbers].[Test] (Number)
SELECT o.Number
FROM Tally o
WHERE o.Number <= @NumberOfNumbers
ALTER TABLE [Numbers].[Test] ADD CONSTRAINT PK_Numbers_Test_Number PRIMARY KEY CLUSTERED (Number)
-- Record the end of the test
UPDATE tt
SET
EndDate = GETDATE()
FROM #TimingTest tt
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
-- And the stats about the numbers in the sequence
UPDATE tt
SET
ItemCount = results.ItemCount,
MaxNumber = results.MaxNumber,
MinNumber = results.MinNumber
FROM #TimingTest tt
CROSS JOIN (
SELECT COUNT(Number) as ItemCount, MAX(Number) as MaxNumber, MIN(Number) as MinNumber FROM [Numbers].[Test]
) results
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
--
-- METHOD - Mladen Prajdic answer
--
-- Prep for the test
DROP TABLE IF EXISTS [Numbers].[Test];
CREATE TABLE [Numbers].[Test] (Number INT NOT NULL);
-- Method information
SET @MethodName = 'MladenPrajdic';
-- Record the start of the test
INSERT INTO #TimingTest(MethodName, TestIteration, StartDate)
SELECT @MethodName, @TestIteration, GETDATE()
-- Run the algorithm
INSERT INTO [Numbers].[Test](Number)
SELECT TOP (@NumberOfNumbers) row_number() over(order by t1.number) as N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
ALTER TABLE [Numbers].[Test] ADD CONSTRAINT PK_Numbers_Test_Number PRIMARY KEY CLUSTERED (Number)
-- Record the end of the test
UPDATE tt
SET
EndDate = GETDATE()
FROM #TimingTest tt
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
-- And the stats about the numbers in the sequence
UPDATE tt
SET
ItemCount = results.ItemCount,
MaxNumber = results.MaxNumber,
MinNumber = results.MinNumber
FROM #TimingTest tt
CROSS JOIN (
SELECT COUNT(Number) as ItemCount, MAX(Number) as MaxNumber, MIN(Number) as MinNumber FROM [Numbers].[Test]
) results
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
--
-- METHOD - Single INSERT
--
-- Prep for the test
DROP TABLE IF EXISTS [Numbers].[Test];
-- The Table creation is part of this algorithm ...
-- Method information
SET @MethodName = 'SingleInsert';
-- Record the start of the test
INSERT INTO #TimingTest(MethodName, TestIteration, StartDate)
SELECT @MethodName, @TestIteration, GETDATE()
-- Run the algorithm
SELECT TOP (@NumberOfNumbers) IDENTITY(int,1,1) AS Number
INTO [Numbers].[Test]
FROM sys.objects s1 -- use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 -- use sys.columns if you don't get enough rows returned to generate all the numbers you need
ALTER TABLE [Numbers].[Test] ADD CONSTRAINT PK_Numbers_Test_Number PRIMARY KEY CLUSTERED (Number)
-- Record the end of the test
UPDATE tt
SET
EndDate = GETDATE()
FROM #TimingTest tt
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
-- And the stats about the numbers in the sequence
UPDATE tt
SET
ItemCount = results.ItemCount,
MaxNumber = results.MaxNumber,
MinNumber = results.MinNumber
FROM #TimingTest tt
CROSS JOIN (
SELECT COUNT(Number) as ItemCount, MAX(Number) as MaxNumber, MIN(Number) as MinNumber FROM [Numbers].[Test]
) results
WHERE tt.MethodName = @MethodName
and tt.TestIteration = @TestIteration
END
-- Calculate the timespan for each of the runs
UPDATE tt
SET
ElapsedTime = DATEDIFF(MICROSECOND, StartDate, EndDate)
FROM #TimingTest tt
--
-- Report the results ...
--
SELECT
MethodName, AVG(ElapsedTime) / AVG(ItemCount) as TimePerRecord, CAST(AVG(ItemCount) as bigint) as SequenceLength,
MAX(ElapsedTime) as MaxTime, MIN(ElapsedTime) as MinTime,
MAX(MaxNumber) as MaxNumber, MIN(MinNumber) as MinNumber
FROM #TimingTest tt
GROUP by tt.MethodName
ORDER BY TimePerRecord ASC, MaxTime ASC, MinTime ASC
반응형
'IT박스' 카테고리의 다른 글
| 반짝이는 4 개의 작은 텍스트 입력 상자 나란히 (0) | 2020.11.29 |
|---|---|
| formGroup의 Angular2 설정 값 (0) | 2020.11.29 |
| Mongoose 문서를 json으로 변환 (0) | 2020.11.29 |
| Protobuf가 공유 라이브러리를 찾을 수 없습니다. (0) | 2020.11.29 |
| 정수 목록을 하나의 숫자로 변환 하시겠습니까? (0) | 2020.11.29 |