복붙노트

[SQL] SQL, 숫자의 보조 테이블

SQL

SQL, 숫자의 보조 테이블

SQL 쿼리의 특정 유형의 숫자의 보조 표는 매우 유용 할 수 있습니다. 특정 작업 또는 반환되는 행의 수는 각 쿼리에서 요구하는 사용자 정의 함수로 필요에 따라 그것은 많은 행으로 테이블로 만들 수 있습니다.

이러한 기능을 만들 수있는 최적의 방법은 무엇입니까?

해결법

  1. ==============================

    1.훗 ... 미안 내가 너무 늦은 이전 게시물에 응답하고있다. 가장 인기있는 대답 (당시는 14 가지 방법에 대한 링크와 재귀 CTE 응답)이 스레드가 음, 어디 ... 성능이 최고에 도전이기 때문에, 그래, 나는 응답했다.

    훗 ... 미안 내가 너무 늦은 이전 게시물에 응답하고있다. 가장 인기있는 대답 (당시는 14 가지 방법에 대한 링크와 재귀 CTE 응답)이 스레드가 음, 어디 ... 성능이 최고에 도전이기 때문에, 그래, 나는 응답했다.

    첫째, 14 개 솔루션 기사는 숫자 즉시하지만 문서의 뾰족한 아웃로와 인용 된 스레드에서 / 집계 테이블을 생성하는 다른 방법을보고 괜찮지, 매우 중요한 따옴표가있다 ...

    아이러니하게도, 기사 자체는 ( "이것은 Itzik 벤 세대에 의한 뉴스 그룹 포스팅에서 WHILE 루프를 사용하는 효율적인 방법이다" "재귀 CTE가 매우 효율적으로 목록 번호를 생성 할 수 있습니다"와 같은 많은 주관적인 진술과 "바이어스 가이드 라인"을 포함 어느 나는 확실히 그는) 비교를 위해 단지 배치하고있다. 그냥 Itzik의 좋은 이름을 언급 어이 사람들은 ... 실제로 끔찍한 방법 것을 사용에 형편없는 진흙을 발생할 수 있습니다. 저자는 그가 설교 특히 어떤 scalablility의 얼굴에 그런 터무니없이 잘못된 진술을하기 전에 약간의 성능 테스트를 수행해야하는 작업 (들)을 연습해야한다.

    실제로 코드가 수행 또는 어떤 사람이 "좋아하는"것에 대해 어떠한 주관적 주장을하기 전에 몇 가지 테스트를 수행하는 생각과 함께, 여기 당신이 당신의 자신의 테스트를 할 수있는 몇 가지 코드입니다. 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

    우리가에있는 동안, 여기에 내가 100, 1000, 10000, 100000, 1000000의 값을 SQL 프로파일 러에서 얻을 숫자의 ...

    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의 While 루프에 두 번째 최악의 논리의 형태로 8 배의 메모리 압력이 While 루프보다 읽고 있습니다. 그것은 스테로이드에 RBAR을 그리고 루프는 피해야한다 동안 단지와 같은 단일 행 계산을 위해, 모든 비용을 피해야한다. 이 재귀 매우 가치있는 장소가 있지만이 그들 중 하나가 아닙니다.

    사이드 바대로, 씨 데니는 절대적으로 ... 정확하게 크기의 영구적 인 숫자에 발견하거나 집계 테이블은 대부분의 것들에 갈 수있는 방법입니다. 정확하게 무엇을 의미 크기는 무엇입니까? 글쎄, 대부분의 사람들은 날짜를 생성하거나 VARCHAR (8000)에 분할 할 수있는 집계 테이블을 사용합니다. 당신이 "N"의 올바른 클러스터 된 인덱스와 11,000 행 집계 테이블을 작성하는 경우, 당신은 공정한 비트 30 년, 그래서 나를 위해 키 번호입니다 날짜의 30 년 이상의 가치 (주택 담보 대출로 I 일을 만들 수있는 충분한 행이있을 것이다 )와 VARCHAR (8000) 분할을 처리 할 확실히 충분. 왜 그렇게 중요 "바로 크기"는 무엇입니까? 집계 테이블을 많이 사용하는 경우, 쉽게 전혀 메모리에 많은 압력없이 엄청나게 빨리 만드는 캐시에 적합합니다.

    마지막으로, 모든 사람은 영구 집계 테이블을 작성하는 경우,이 경우 1)은 무슨 일이) 한 번 만들어 둘 수 있기 때문에 당신이 그것을 구축하는 데 사용할 방법별로 문제를한다는 것을 알고 11,000 행과 같은 그것의 무엇인가 테이블은 모든 방법은 "충분히 좋은"를 실행하는 것입니다. 그렇다면 어떤 방법에 대한 사용에 내 인생의 모든 indigination ???

    대답은 형편없는 남자 / 여자 더 나은 모르는 사람이다 그냥 얻을 필요가 자신 재귀 CTE 방법 같은 것을 볼 훨씬 크고 훨씬 더 자주 건물보다 사용 무언가를 사용하기로 결정 수있는 일 그녀의 직업 영구 집계 테이블 나는 그 사람에 서버 자신의 코드 실행을하고, 해당 서버의 데이터를 소유하는 회사를 보호하기 위해 노력하고있어. 그래 ... 그것은 그 큰 거래이다. 그것은뿐만 아니라, 다른 사람을 위해 있어야합니다. 대신에 "충분히 좋은"의 일을 할 수있는 올바른 방법을 가르치십시오. ... 게시하기 전에 또는 게시하거나 책에서 뭔가를 사용하여 몇 가지 테스트를 수행하면, 사실, 재귀 CTE는이 같은 뭔가를 갈 수있는 방법이라고 생각 특히 자신 할 수있다 저장 수명. ;-)

    듣기 주셔서 감사합니다...

  2. ==============================

    2.가장 최적의 기능을 대신하는 기능의 테이블을 사용하는 것입니다. 함수를 사용하여 값 덮개 매우 큰 범위를 반환하는 경우, 특히, 데이터가 반환되는 값을 생성하기 위해 추가적인 CPU 부하를 야기한다.

    가장 최적의 기능을 대신하는 기능의 테이블을 사용하는 것입니다. 함수를 사용하여 값 덮개 매우 큰 범위를 반환하는 경우, 특히, 데이터가 반환되는 값을 생성하기 위해 추가적인 CPU 부하를 야기한다.

  3. ==============================

    3.이 문서에서는 각각의 토론과 14 개 가지 가능한 솔루션을 제공합니다. 중요한 점은 즉 :

    이 문서에서는 각각의 토론과 14 개 가지 가능한 솔루션을 제공합니다. 중요한 점은 즉 :

    나는 개인적으로 좋아 :

    WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
  4. ==============================

    4.이보기는 슈퍼 빠른이며 모든 긍정적 인 INT 값을 포함합니다.

    이보기는 슈퍼 빠른이며 모든 긍정적 인 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
  5. ==============================

    5.SQL 서버 2016+를 사용하면 OPENJSON를 사용할 수있는 번호 테이블을 생성합니다 :

    SQL 서버 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),']'));

    LiveDemo

    우리가 OPENJSON 숫자의 시리즈를 생성하는 데 사용할 수있는 방법에서 가져온 아이디어?

  6. ==============================

    6.편집 : 아래 콘라드의 의견을 참조하십시오.

    편집 : 아래 콘라드의 의견을 참조하십시오.

    제프 MODEN의 대답은 중대하다 ...하지만 당신은 E32 행을 제거하지 않는 한 Itzik 방법이 실패 나는 포스트 그레스에서 찾을 수 있습니다.

    약간 빠른 포스트 그레스에 (이 100ms 대 40ms로는) 내가 여기에 발견 포스트 그레스에 맞게 다른 방법은 다음과 같습니다

    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 서버에서 이동하고로서, 해당 플랫폼에 집계 테이블을 할 수있는 더 좋은 방법을 놓칠 수 있습니다 ... INTEGER ()? 순서()?

  7. ==============================

    7.그럼에도 불구하고 훨씬 나중에, 나는 약간 다른 '전통적인'CTE를 (행의 볼륨을 얻기 위해 기본 테이블을 터치하지 않음)에 기여하고 싶습니다 :

    그럼에도 불구하고 훨씬 나중에, 나는 약간 다른 '전통적인'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 읽습니다. 그러나, 지속적으로 덜 수행 한 후 다른 쿼리를 작성합니다. 아시다시피, 기록 기능은 지속적으로 매우 훨씬 더 비싼 다음 읽어들입니다.

    기간은 수행이 지속적으로 빠르게 다음 (밀리 적은 기간) 다른 쿼리, 내 8core에, 코어 (MAXDOP)의 수에 크게 의존하지만.

    내가 사용하고 있습니다 :

    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 32 기가 바이트 2.67Ghz @ 제온 x3450의에, 4 개의 코어가 활성화 HT.

    8. from https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQL 주입에 대한 매개 변수화 된 쿼리 도움을?  (0) 2020.03.07
[SQL] 어떻게 SQL SELECT에서 THEN ...는 IF를 수행합니까?  (0) 2020.03.07
[SQL] 는 SQL 서버에서 어떻게 물건과 'XML 경로에 대한'작업  (0) 2020.03.07
[SQL] 요소 번호의 PostgreSQL unnest ()  (0) 2020.03.07
[SQL] MySQL의에서 동일한 열 값이 행을 반환하는 방법  (0) 2020.03.07
,

티스토리툴바