특정 유형의 sql 쿼리, 보조 표 숫자임 매우 유용할 수 있습니다. 이 때 많은 행뿐만 테이블로 생성할 수 있는 특정 작업을 할 수 있는 사용자 정의 함수 또는 형태로 되돌려줍니다 필요한 행뿐만 각 질의입니다.
이러한 기능을 만들 수 있는 최적의 방법은 무엇입니까?
이젠 아니야. 미안해, 이렇게 늦게 응답하는 등 기존 게시물로의 I& # 39 m, 그래, 나는 가장 인기 있는 오토메이티드 목격하고서야 했다 때문에 (당시 14 개의 다른 방법을 통해 반복 스테 오토메이티드 https://partner. microsoft.) 이 마음이, 흠. 기껏해야 성능 도전했다.
첫째, 14 개의 다른 솔루션뀉뀉뀉뀉 정상입니다 있었어요새롭게 다른 만드는 방법을 사용한 기사 한 표 즉석에서 이 글에서 지적한 대로 탤리를 번호 / 하지만, 또한 매우 중요한 쿼트에 a # 39 의 there& 스레드할 들었다.
>. " 대한 제안, 효율성 및 >. 성능 종종 주관적인 것이다. >. 어떻게 기술입니까 질의입니다 상관없이 >. 사용하는 실제 구현 >. 의 효율성을 결합할지 질의입니다. >. 따라서 의존하지 않고 >. 바이어스됨 지침, 것이 중요합니다 >. 쿼리하고 테스트하려면 확인할 수 있는 >. 어느쪽이야 향상합니다 better.".
아이러니하게도 이 많이 들어 있으며, 그 주관적인 명령문입니다 " 바이어스됨 guidelines". 재귀 스테 " 등 여러 efficiently&quo , , 열거합니다 생성할 수 있으며, 이는 꽤 효과적인 방법을 사용하여 및 " , * from a while 루프를 통해 글을 이치크 Ben-Gen" 뉴스그룹 (I& 게시하기를 비교를 위해, 그는 그냥 # 39 m 합니다 어떤 목적). 월요일 c& # 39, 여러분. 그냥 좋은 이름을 거론하며 Itzik& # 39 의 일부) 를 유발할 수 있는 실제 사용 케이블링의 진흙땅 끔찍한 메서드입니다. 저자는 practice what %hskb), 그는 설교 및 성능 테스트를 할 때는 먼저 잘못된 명령문입니다 특히 침묵이군 터무니없이 작은 어떻게해야합니다 등 모든 스칼리브릴리티.
실제로 어떤 생각을 하고 있는 몇 가지 테스트 코드를 만들기 전에 모든 사람을, 아니,, 주관적 청구용 의심하였더라 높여줍니까 " likes" here& # 39 의 일부 코드를 통해 자신의 테스트 할 수 있습니다. # 39 에서 설치 및 실행, re you& 프로필러 스피드 대한 테스트를 위한 체크아웃합니다 구하겠죠 just do a # 39 " Search& n& # 39, Replace";; 1 000 000 단축시킵니다 수 " favorite"; 번호를 참조).
--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
WITH Tally (N) AS
(
SELECT 1 UNION ALL
SELECT 1 + N FROM Tally WHERE N < 1000000
)
SELECT N
INTO #Tally1
FROM Tally
OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
CREATE TABLE #Tally2 (N INT);
SET NOCOUNT ON;
DECLARE @Index INT;
SET @Index = 1;
WHILE @Index <= 1000000
BEGIN
INSERT #Tally2 (N)
VALUES (@Index);
SET @Index = @Index + 1;
END;
GO
--===== Traditional CROSS JOIN table method
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
INTO #Tally3
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT N
INTO #Tally4
FROM cteTally
WHERE N <= 1000000;
GO
--===== Housekeeping
DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO
SPID TextData Dur(ms) CPU Reads Writes
---- ---------------------------------------- ------- ----- ------- ------
51 --===== Test for 100 rows ============== 8 0 0 0
51 --===== Traditional RECURSIVE CTE method 16 0 868 0
51 --===== Traditional WHILE LOOP method CR 73 16 175 2
51 --===== Traditional CROSS JOIN table met 11 0 80 0
51 --===== Itzik's CROSS JOINED CTE method 6 0 63 0
51 --===== Housekeeping DROP TABLE #Tally 35 31 401 0
51 --===== Test for 1000 rows ============= 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 47 47 8074 0
51 --===== Traditional WHILE LOOP method CR 80 78 1085 0
51 --===== Traditional CROSS JOIN table met 5 0 98 0
51 --===== Itzik's CROSS JOINED CTE method 2 0 83 0
51 --===== Housekeeping DROP TABLE #Tally 6 15 426 0
51 --===== Test for 10000 rows ============ 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 434 344 80230 10
51 --===== Traditional WHILE LOOP method CR 671 563 10240 9
51 --===== Traditional CROSS JOIN table met 25 31 302 15
51 --===== Itzik's CROSS JOINED CTE method 24 0 192 15
51 --===== Housekeeping DROP TABLE #Tally 7 15 531 0
51 --===== Test for 100000 rows =========== 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 4143 3813 800260 154
51 --===== Traditional WHILE LOOP method CR 5820 5547 101380 161
51 --===== Traditional CROSS JOIN table met 160 140 479 211
51 --===== Itzik's CROSS JOINED CTE method 153 141 276 204
51 --===== Housekeeping DROP TABLE #Tally 10 15 761 0
51 --===== Test for 1000000 rows ========== 0 0 0 0
51 --===== Traditional RECURSIVE CTE method 41349 37437 8001048 1601
51 --===== Traditional WHILE LOOP method CR 59138 56141 1012785 1682
51 --===== Traditional CROSS JOIN table met 1224 1219 2429 2101
51 --===== Itzik's CROSS JOINED CTE method 1448 1328 1217 2095
51 --===== Housekeeping DROP TABLE #Tally 8 0 415 0
이에 따라 때는기대어 표시줄에는 스미슨씨 데니 절대적으로 별색 생일날이야 올바르게 크기의 영구적입니다 번호 또는 탤리를 테이블은 길을 갈 대부분의 전지전능하심이라 올바른 크기의 무엇을 의미합니까? 뭐, 대부분의 사람들은 탤리를 테이블을 사용할 수 있는 못하며창조된 분할됩니다 발령합니다 날짜 또는 VARCHAR (8000). 만드는 경우 11,000 행일 있는 표에 대한 올바른 클러스터링된 인덱스화할 " N" you& 탤리를 # 39, ll,, 만들 수 있는 충분한 행뿐만 30 년 이상 규모의 날짜 (내가 작업할 수 있어 매우 중요한 가져다줄래요 공정한 약어입니다 주택 30 년) 과 충분히 처리할 수 있도록 한 것은 분할합니다 VARCHAR (8000). 이유는 바로 sizing" "; 그렇게 중요한? 손쉽게 사용할 수 있도록 하는 경우, 엄청나게 많은 탤리를 테이블 없이 압력을 훨씬 빠른 메모리 캐시에 it at all.
당연하면서도 중요한 사실은 모든 사람이 알고 만들면 영구적입니다 탤리를 테이블 얻게된다면 doesn& # 39, t 많은 문제를 어떤 방법을 사용하든 it 환경을 구축할 수 있기 때문에 1), s # 39 만 it& 될 브라운아저씨의 후 2) 의 경우 모든 방법을 등과 같은 it& # 39 표, 뭔가 좋은 enough" " 실행하십시오 11,000 행일 할 것 같습니다;;). 왜 내 세에서의 에 대한 모든 인디지나션 가능 사용할 방법은??
답은 잘 모르는 일부 케이블링의 담당이잖아요 / gal 누굴요 doesn& # 39, 그냥 자신의 업무를 볼 수도 있다는 데 필요한 모든 확보하십시오 반복 사용할지 결정할 수 있는 방법 및 스테 무언가를 위해 이 같은 일을 훨씬 더 자주 사용되는 구축하는 것보다 훨씬 크고, m # 39 표 및 I& 탤리를 영구 보호할 하는 사람들은 그들의 코드를 실행됩니, 회사 서버에서 데이터를 소유하는 그 서버에서 . 그래. # 39 의 큰 it& 계약을 체결했다. 그 사람들이 해야 할 경우, 잘 알려져 있다. 대신 좋은 길을 가르쳐 to do things enough" ";). 몇 가지 테스트 후 게시하기 전에 뭔가 사용하거나 또는 책. 특히 5월 저장할 수 있도록 자신의 생명을 사실 그렇게 생각한다면 재귀 스테 운행에서어떠한 이 같은 일이 거의 다 있다. -)
청취해 주셔서 대단히 감사합니다.
이 문서 에 논의를 통해 14 개의 다른 해결책을 각. 중요한 점은.
>. 이에 대해 효율성 및 제안 >. 성능 종종 주관적인 것이다. >. 어떻게 기술입니까 질의입니다 상관없이 >. 사용하는 실제 구현 >. 의 효율성을 결합할지 질의입니다. >. 따라서 의존하지 않고 >. 바이어스됨 지침, 것이 중요합니다 >. 쿼리하고 테스트하려면 확인할 수 있는 >. 어느쪽이야 향상합니다 좋습니다.
개인적으로 좋아했잖아:
WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
이 관점은 int '초고속' 에 모든 양수 값.
CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
, Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
, Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
, Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
, Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
, Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
FROM Int32
GO
Sql server 2016년 을 사용하여 테이블을 생성하는 데 사용할 수 있습니다 '+' 숫자 '오픈제슨':
-- range from 0 to @max - 1
DECLARE @max INT = 40000;
SELECT rn = CAST([key] AS INT)
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));
<, kbd>, ['리베디모'] (https://data.stackexchange.com/stackoverflow/query/481961) < /kbd>;
< hr>; Idea 에서 발췌 [어떻게 사용할 수 있는 일련의 번호를 오픈제슨 발령합니다?] (https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/generate-serie-of-numbers-in-sql-server-2016-using-openjson/)
편집: # 39 의 주석문입니다 Conrad& 볼 수 있습니다.
내가 조금 빠름 postgres (40ms vs 100ms) 는 here 에 다른 방법으로 postgres 맞게 적용되었다.
WITH
E00 (N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b
LIMIT 11000 -- end record 11,000 good for 30 yrs dates
), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)
SELECT N
FROM Tally
As I am SQL Server 의 세계, 더 나은 방법으로 숨길까요 Postgres 가질 수 있는 테이블을 누락했습니다 탤리를 있는 플랫폼. 정수 ()? 시퀀스 ()?
나중에, & # 39 와 같은 I& 여전히 큰 기여를 약간 다른 # 39 traditional& # 39;;; d 스테 (언약보다는 내려받습니다 볼륨입니다 터치 베이스 표를 행):
--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL 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)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand
이 기간 따라 수에 따라 크게 코어 (맥스도프) 이지만, on my 8core 지속적으로 감소 (기간 (ms) 가 빨리 다음 다른 질의입니다.
나는 사용:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
windows Server 2012년 R2 에, 32 GB, 제온® X3450 @2.67Ghz, 4 개의 httechnology 활성화되었습니다.