SQL, 보조 숫자 표
특정 유형의 SQL 쿼리의 경우 보조 숫자 표가 매우 유용할 수 있습니다.특정 태스크에 필요한 만큼의 행이 있는 테이블이나 각 쿼리에 필요한 행 수를 반환하는 사용자 정의 함수로 만들 수 있습니다.
이러한 기능을 만드는 최적의 방법은 무엇입니까?
아... 죄송합니다. 오래된 게시물에 답장이 너무 늦었습니다.그리고, 네, 이 스레드에서 가장 인기 있는 답변(당시 14가지 다른 방법에 대한 링크가 있는 재귀 CTE 답변)은 음...최고의 성능에 도전합니다.
먼저, 14가지 솔루션이 포함된 기사는 숫자/Taly 테이블을 만드는 다양한 방법을 즉시 확인할 수 있지만, 기사와 인용된 스레드에서 지적한 것처럼 매우 중요한 인용문이 있습니다.
"효율성과 성능에 대한 논의는 종종 주관적입니다.쿼리가 사용되는 방식에 관계없이 실제 구현에 따라 쿼리의 효율성이 결정됩니다.따라서 편향된 가이드라인에 의존하기보다는 쿼리를 테스트하고 어떤 것이 더 나은 성능을 발휘하는지 확인하는 것이 필수적입니다."
아이러니하게도, 기사 자체에는 많은 주관적인 진술과 "재귀적인 CTE는 꽤 효율적으로 숫자 목록을 생성할 수 있다", "이것은 Itzik Ben-Gen의 뉴스 그룹 게시물에서 WHY 루프를 사용하는 효율적인 방법이다"와 같은 "편향적인 지침"이 포함되어 있습니다.자, 여러분...Itzik의 좋은 이름을 언급하는 것만으로도 가난한 게으름뱅이가 실제로 그 끔찍한 방법을 사용하게 될지도 모릅니다.저자는 특히 확장성에 직면하여 터무니없이 부정확한 진술을 하기 전에 자신이 설교하는 것을 연습하고 약간의 성능 테스트를 해야 합니다.
코드가 무엇을 하는지 또는 누군가가 무엇을 좋아하는지에 대한 주관적인 주장을 하기 전에 실제로 몇 가지 테스트를 해야 한다는 생각으로, 여기 당신이 직접 테스트를 할 수 있는 몇 가지 코드가 있습니다.테스트를 실행할 SPID에 대한 프로파일러를 설정하고 직접 확인합니다."즐겨찾기" 번호에 대해 1000000의 "Search'n'Replace"를 입력하면...
--===== 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
다음은 SQL Profiler에서 얻은 100, 1000, 10000, 100000 및 1000000의 값입니다.
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
보다시피 재귀 CTE 방법은 기간 및 CPU에 대한 기간 루프 다음으로 최악이며 논리적 읽기 형식의 메모리 압력이 기간 루프보다 8배 더 높습니다.스테로이드에 대한 RBAR이며 While Loop을 피해야 하는 것처럼 단일 행 계산에 대해서는 어떤 대가를 치르더라도 피해야 합니다.재귀가 꽤 가치 있는 곳들이 있지만 이것은 그들 중 하나가 아닙니다.
사이드바로서 데니 씨는...정확한 크기의 영구 숫자 또는 집계 표가 대부분의 경우 사용할 수 있는 방법입니다.올바른 크기는 무엇을 의미합니까?대부분의 사람들은 VARCHAR(8000)에서 날짜를 생성하거나 분할하기 위해 Talile 테이블을 사용합니다.정확한 클러스터 인덱스가 "N"인 11,000 행 Tal 테이블을 생성하면 30년 이상의 날짜를 생성할 수 있는 충분한 행(저는 모기지 관련 업무를 수행하므로 30년은 저에게 중요한 숫자입니다)과 VARCHAR(8000) 분할을 처리할 수 있는 충분한 행이 있습니다."올바른 사이징"이 중요한 이유는 무엇입니까?Talile 테이블을 많이 사용하면 캐시에 쉽게 들어가 메모리에 큰 부담을 주지 않고 빠르게 이동할 수 있습니다.
마지막으로 중요한 것은 영구적인 Talile 테이블을 만든다면 어떤 방법을 사용하여 테이블을 만드는지는 중요하지 않다는 것을 모두가 알고 있다는 것입니다. 1) 한 번만 만들 것이고 2) 11,000개의 행 테이블과 같은 것이라면 모든 방법이 "충분히" 실행될 것이기 때문입니다.그렇다면 어떤 방법을 사용해야 하는지에 대한 내 입장의 모든 소화불량은 왜?
답은 더 잘 알지 못하고 일만 해야 하는 가난한 사람들이 Recursive CTE 방법과 같은 것을 보고 영구적인 Tal 테이블을 만드는 것보다 훨씬 더 크고 더 자주 사용되는 것에 그것을 사용하기로 결정할 수 있다는 것입니다. 그리고 저는 그 사람들, 그들의 코드가 실행되는 서버를 보호하려고 노력하고 있습니다. 서버에 있는 데이터를 소유한 회사입니다.네...그것은 큰 일입니다.다른 모든 사람들을 위한 것이어야 합니다."충분히 좋다" 대신 올바른 방법을 가르쳐 주세요.게시물이나 책을 게시하거나 사용하기 전에 테스트를 수행합니다.사실, 당신이 구한 생명은, 특히 당신이 재귀적인 CTE가 이런 것을 위한 방법이라고 생각한다면, 당신 자신일 수도 있습니다. ;-)
들어주셔서 감사합니다...
가장 최적의 기능은 함수 대신 표를 사용하는 것입니다.함수를 사용하면 CPU 로드가 증가하여 반환되는 데이터의 값이 생성됩니다. 반환되는 값이 매우 큰 범위를 차지하는 경우에는 특히 그렇습니다.
이 기사는 각각의 논의와 함께 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 2022부터 다음 작업을 수행할 수 있습니다.
SELECT Value
FROM GENERATE_SERIES(START = 1, STOP = 100, STEP=1)
SQL Server 2022(CTP 2.0)의 공개 미리 보기에는 매우 유망한 요소가 몇 가지 있습니다.실제 출시 전에 부정적인 측면이 해결되기를 바랍니다.
✅ 번호 생성을 위한 실행 시간 아래는 테스트 VM에서 700ms 내에 10,000,000개의 번호를 생성합니다(변수를 할당하면 클라이언트로 결과를 전송할 때 오버헤드가 제거됨).
DECLARE @Value INT
SELECT @Value =[value]
FROM GENERATE_SERIES(START=1, STOP=10000000)
✅ 카디널리티 추정치
연산자에서 반환될 숫자의 수를 계산하는 것은 간단하며 SQL Server는 아래와 같이 이를 활용합니다.
아래 삽입에 대한 계획에는 완전히 불필요한 스풀이 있습니다. SQL Server에는 현재 행의 소스가 잠재적으로 대상이 아님을 확인할 수 있는 논리가 없기 때문입니다.
CREATE TABLE dbo.NumberHeap(Number INT);
INSERT INTO dbo.Numbers
SELECT [value]
FROM GENERATE_SERIES(START=1, STOP=10);
번호에 클러스터된 인덱스가 있는 테이블에 삽입할 때 스풀을 대신 정렬로 대체할 수 있습니다(위상 분리도 제공).
❌ 불필요한 정렬
다음은 행을 순서대로 반환하지만 SQL Server에는 이를 보장하고 실행 계획에서 이를 활용할 수 있는 속성이 아직 설정되어 있지 않습니다.
SELECT [value]
FROM GENERATE_SERIES(START=1, STOP=10)
ORDER BY [value]
RE: 이 마지막 지점은 Aaron Bertrand가 현재 체크 표시된 상자가 아니지만 곧 표시될 수 있음을 나타냅니다.
용사를 합니다.SQL Server 2016+사용할 수 있는 숫자 표를 생성합니다.OPENJSON:
-- 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),']'));
Idea taken from OPENJSON을 사용하여 일련의 숫자를 생성하려면 어떻게 해야 합니까?
편집: 아래 콘래드의 설명을 참조하십시오.
제프 모든의 대답은 훌륭합니다... 하지만 저는 포스트그레스에서 E32 행을 제거하지 않으면 Itzik 방법이 실패한다는 것을 발견했습니다.
포스트그레스(40ms 대 100ms)에서 약간 빠른 것도 포스트그레스에 적합한 또 다른 방법입니다.
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
SQL Server에서 Postgres 월드로 이동하는 동안 해당 플랫폼에서 테이블을 더 효율적으로 문서화할 수 있는 방법을 놓쳤을 수 있습니다.정수()? 시퀀스()?
나중에 조금 다른 '전통적인' CTE에 기여하고 싶습니다(행의 볼륨을 얻기 위해 기본 테이블을 누르지 않음).
--===== 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
이 CTE는 Itzik의 CTE보다 읽기를 더 많이 수행하지만 기존 CTE보다는 덜 수행합니다.그러나 일관되게 다른 쿼리보다 적은 쓰기 작업을 수행합니다.아시다시피 쓰기는 읽기보다 훨씬 더 비쌉니다.
기간은 코어 수(MAXDOP)에 따라 크게 달라지지만, 8코어에서는 다른 쿼리에 비해 지속적으로 더 빨리(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: )
윈도우즈 서버 2012 R2, 32GB, Xeon X3450 @2.67Ghz, 4코어 HT 사용
언급URL : https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers
'programing' 카테고리의 다른 글
| 판다 데이터 프레임에서 행의 하위 집합 수정 (0) | 2023.06.19 |
|---|---|
| 원격 풀 중에 모든 태그를 가져오도록 Git 기본값을 설정할 수 있습니까? (0) | 2023.06.19 |
| 'charmap' 코덱은 위치 0에서 문자 '\u010c'를 인코딩할 수 없습니다. 문자 맵은 MariaDB 및 SQLChemy에 있습니다. (0) | 2023.06.19 |
| WPF 데이터 그리드의 "삭제 키 누름" 이벤트는 무엇입니까? (0) | 2023.06.19 |
| 오라클 XE 데이터베이스의 공식 도커 이미지가 있습니까? (0) | 2023.06.19 |