vzczc
그는 물었다 12년 전
57

SQL, 보조 숫자임 표

특정 유형의 sql 쿼리, 보조 표 숫자임 매우 유용할 수 있습니다. 이 때 많은 행뿐만 테이블로 생성할 수 있는 특정 작업을 할 수 있는 사용자 정의 함수 또는 형태로 되돌려줍니다 필요한 행뿐만 각 질의입니다.

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

jammus
그는 12년 전 댓글을 달았습니다
1

39 표, d 에 대해 설명해 주시겠습니까 you& 왜 이렇게 미리 채워진 사용하지 않고, 숫자?

vzczc
그는 11년 전 댓글을 달았습니다
7

이런 칠하려면 요약표 예.

JeffO
그는 7년 전 댓글을 달았습니다
4

일부 DBA& # 39 의 및 / 또는 3rd party 앱을 사용하면 추가로 페르마나나 한 표.

답변 7 개

이젠 아니야. 미안해, 이렇게 늦게 응답하는 등 기존 게시물로의 I& # 39 m, 그래, 나는 가장 인기 있는 오토메이티드 목격하고서야 했다 때문에 (당시 14 개의 다른 방법을 통해 반복 스테 오토메이티드 https://partner. microsoft.) 이 마음이, 흠. 기껏해야 성능 도전했다.

첫째, 14 개의 다른 솔루션뀉뀉뀉뀉 정상입니다 있었어요새롭게 다른 만드는 방법을 사용한 기사 한 표 즉석에서 이 글에서 지적한 대로 탤리를 번호 / 하지만, 또한 매우 중요한 쿼트에 a # 39 의 there& 스레드할 들었다.

&gt. &quot 대한 제안, 효율성 및 &gt. 성능 종종 주관적인 것이다. &gt. 어떻게 기술입니까 질의입니다 상관없이 &gt. 사용하는 실제 구현 &gt. 의 효율성을 결합할지 질의입니다. &gt. 따라서 의존하지 않고 &gt. 바이어스됨 지침, 것이 중요합니다 &gt. 쿼리하고 테스트하려면 확인할 수 있는 &gt. 어느쪽이야 향상합니다 better.&quot.

아이러니하게도 이 많이 들어 있으며, 그 주관적인 명령문입니다 &quot 바이어스됨 guidelines&quot. 재귀 스테 &quot 등 여러 efficiently&quo , , 열거합니다 생성할 수 있으며, 이는 꽤 효과적인 방법을 사용하여 및 &quot , * from a while 루프를 통해 글을 이치크 Ben-Gen&quot 뉴스그룹 (I& 게시하기를 비교를 위해, 그는 그냥 # 39 m 합니다 어떤 목적). 월요일 c& # 39, 여러분. 그냥 좋은 이름을 거론하며 Itzik& # 39 의 일부) 를 유발할 수 있는 실제 사용 케이블링의 진흙땅 끔찍한 메서드입니다. 저자는 practice what %hskb), 그는 설교 및 성능 테스트를 할 때는 먼저 잘못된 명령문입니다 특히 침묵이군 터무니없이 작은 어떻게해야합니다 등 모든 스칼리브릴리티.

실제로 어떤 생각을 하고 있는 몇 가지 테스트 코드를 만들기 전에 모든 사람을, 아니,, 주관적 청구용 의심하였더라 높여줍니까 &quot likes&quot here& # 39 의 일부 코드를 통해 자신의 테스트 할 수 있습니다. # 39 에서 설치 및 실행, re you& 프로필러 스피드 대한 테스트를 위한 체크아웃합니다 구하겠죠 just do a # 39 &quot Search& n& # 39, Replace";; 1 000 000 단축시킵니다 수 &quot 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

39 의 숫자는 we& 동시에, re dell. 해버린다는거, here& # 39 에서 SQL 의 값을 얻을 수 있으며, 100, 1000년 그리고 10000 프로필러 100000, 1 000 000.

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

  • 재귀적 스테 알 수 있듯이, 이 기간 동안 두 번째 방법은 오직 한 형태로, CPU, 메모리 압력을 루프 (loop) 는 8 배 이상의 논리적 판독합니다 while 루프 . # 39 의 바르 it& 전혀 비용, 모든 단일 행에 대해 강하게 수 있으므로 피해야 합니다, while 루프 마찬가지로 계산이므로 피해야 합니다. 매우 중요한 위치에 있는데, 그 중 하나가 ISN& # 39 이 반복이 아니라 T *.

이에 따라 때는기대어 표시줄에는 스미슨씨 데니 절대적으로 별색 생일날이야 올바르게 크기의 영구적입니다 번호 또는 탤리를 테이블은 길을 갈 대부분의 전지전능하심이라 올바른 크기의 무엇을 의미합니까? 뭐, 대부분의 사람들은 탤리를 테이블을 사용할 수 있는 못하며창조된 분할됩니다 발령합니다 날짜 또는 VARCHAR (8000). 만드는 경우 11,000 행일 있는 표에 대한 올바른 클러스터링된 인덱스화할 &quot N&quot you& 탤리를 # 39, ll,, 만들 수 있는 충분한 행뿐만 30 년 이상 규모의 날짜 (내가 작업할 수 있어 매우 중요한 가져다줄래요 공정한 약어입니다 주택 30 년) 과 충분히 처리할 수 있도록 한 것은 분할합니다 VARCHAR (8000). 이유는 바로 sizing&quot "; 그렇게 중요한? 손쉽게 사용할 수 있도록 하는 경우, 엄청나게 많은 탤리를 테이블 없이 압력을 훨씬 빠른 메모리 캐시에 it at all.

당연하면서도 중요한 사실은 모든 사람이 알고 만들면 영구적입니다 탤리를 테이블 얻게된다면 doesn& # 39, t 많은 문제를 어떤 방법을 사용하든 it 환경을 구축할 수 있기 때문에 1), s # 39 만 it& 될 브라운아저씨의 후 2) 의 경우 모든 방법을 등과 같은 it& # 39 표, 뭔가 좋은 enough&quot &quot 실행하십시오 11,000 행일 할 것 같습니다;;). 왜 내 세에서의 에 대한 모든 인디지나션 가능 사용할 방법은??

답은 잘 모르는 일부 케이블링의 담당이잖아요 / gal 누굴요 doesn& # 39, 그냥 자신의 업무를 볼 수도 있다는 데 필요한 모든 확보하십시오 반복 사용할지 결정할 수 있는 방법 및 스테 무언가를 위해 이 같은 일을 훨씬 더 자주 사용되는 구축하는 것보다 훨씬 크고, m # 39 표 및 I& 탤리를 영구 보호할 하는 사람들은 그들의 코드를 실행됩니, 회사 서버에서 데이터를 소유하는 그 서버에서 . 그래. # 39 의 큰 it& 계약을 체결했다. 그 사람들이 해야 할 경우, 잘 알려져 있다. 대신 좋은 길을 가르쳐 to do things enough&quot ";). 몇 가지 테스트 후 게시하기 전에 뭔가 사용하거나 또는 책. 특히 5월 저장할 수 있도록 자신의 생명을 사실 그렇게 생각한다면 재귀 스테 운행에서어떠한 이 같은 일이 거의 다 있다. -)

청취해 주셔서 대단히 감사합니다.

Jeff Moden
Andre Figueiredo
그는 3년 전 댓글을 달았습니다
0

정말 정말 강조표시할 너회의 감지에서 보다 많은 사람들이 소셜 레스폰사빌레티. 그 외에 채워야 할 수 있다고 주장한 one& 및; d # 39 한 번 모든 종류의 물건을 숫자임 요약표 필요한 경우 어떤 이유에서인지 [id / '선택' w '로 보인다' 는 보다 스테] (https://stackoverflow.com/a/1407488/986862).

Jeff Moden
그는 3년 전 댓글을 달았습니다
0

Thank you for the very 친절하구만 의견 감사 드립니다.

대신 테이블 사용하는 것이 가장 적합한 함수는 함수. 기능 추가 CPU 가 사용하는 데이터 값이 반환될 경우 만듭다 로드, 특히 값이 반환될 빽이라는 매우 큰 약점이다.

Rbjz
그는 7년 전 댓글을 달았습니다
1

그 후 자신의 상황에 따라 달라질 수 있는 것 같아요. 양국 간 교역 및 CPU, 입출력 (i / o 최고의 성능을 옵션 비용, 내용에 따라 더 많은 비용이 들 수 있습니다.

mrdenny
그는 7년 전 댓글을 달았습니다
0

이 표에 비해 CPU, 입출력 수단은 거의 항상 체험이 될 수도 있고 작은 이미 버퍼풀.

rmalayter
그는 3년 전 댓글을 달았습니다
1

항상 많은 비용이 들고 way @mrdenny I / O 는 보다 느린 CPU. Ssd 는 최근 몇 년간 이 다소 변경된 있지만, 대부분 이러한 ssd 네트워크 구축 및 운영 아케텍처에 그들 사이에 https://partner. cpu. 내가 보고 유일한 데이터베이스뿐 진정으로 cpucomment 바인딩될 튜닝되지 실행됩니까 러마 국한됨 어플리케이션 또는 무거운 기계 학습.

Dogs
그는 일 년 전 댓글을 달았습니다
0

@rmalayter 돌봐야 할 수 있는 경우를 제외하고는 거의 틀림없이 우리 테이블은 자주 하면 보통, t # 39 의 메모리 및 메모리 업그레이드 및 싸다 doesn& 라이센싱에 추가 CPU 코어 운행에서어떠한 영향을 미칠 수 있다. SQL Server Enterprise edition 의 것입니다, 즉 5 자리 숫자를 무도회죠 박 전 대표를 코어당 추가 비용을 절감할 수 있는 것보다 더 많은 비용을 너 혼자 라이센싱에 코어 아마도 전체 서버에 있는 문을 더 ramcomment 던졌다.

이 문서 에 논의를 통해 14 개의 다른 해결책을 각. 중요한 점은.

&gt. 이에 대해 효율성 및 제안 &gt. 성능 종종 주관적인 것이다. &gt. 어떻게 기술입니까 질의입니다 상관없이 &gt. 사용하는 실제 구현 &gt. 의 효율성을 결합할지 질의입니다. &gt. 따라서 의존하지 않고 &gt. 바이어스됨 지침, 것이 중요합니다 &gt. 쿼리하고 테스트하려면 확인할 수 있는 &gt. 어느쪽이야 향상합니다 좋습니다.

개인적으로 좋아했잖아:

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

Rbjz
그는 7년 전 댓글을 달았습니다
2

잘못된 의해 검증된 수락됨 답? # 39, & # 39, optimal& 표시되어도 잘 사용하지 않는 것이 보입니다.

이 관점은 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

Andriy M
그는 9년 전 댓글을 달았습니다
1

'0' 유용한 경우가 많습니다. 그리고 난 아마도 최종 열을 변환하십시오 'int'. 또한 이 방식은 기본적으로 포함되어 있다는 걸 알아야 한다 ('0' 또는 '없이' 로 변환하는 인컨텍스트 (int) 에 수락됨 그 이름을 Itzik& # 39 의 금지커서 참여됨 스테 메서드입니다 .

ca9163d9
그는 8년 전 댓글을 달았습니다
0

특별한 이유는 '와' 추가 셰마빈딩 뷰에서의?

Anthony Faull
그는 8년 전 댓글을 달았습니다
0

39, & # 39 SCHEMABINDING& 함께 추가. 내말들어봐 쿼리합니다 가속화합니다. 이를 통해 이 최적화기 없음을 알고 데이터 액세스됩니다. 자세한 내용은 http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx)

Rbjz
그는 7년 전 댓글을 달았습니다
0

이 경우 일부 측정선을 뒤로를 수 있는 보다 @AnthonyFaull 궁금하다.

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

&lt, kbd&gt, ['리베디모'] (https://data.stackexchange.com/stackoverflow/query/481961) &lt /kbd>;

&lt hr>; Idea 에서 발췌 [어떻게 사용할 수 있는 일련의 번호를 오픈제슨 발령합니다?] (https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/generate-serie-of-numbers-in-sql-server-2016-using-openjson/)

Lukasz Szozda
Andriy M
그는 4년 전 댓글을 달았습니다
1

좋다. 아마 내가 한 사용할 수 있습니다 ' ()' 에서 SQL XML 이 경우 그동안 partnernet 포지셔닝하십시오 완전히 Server& # 39 의 XQuery.

Jeff Moden
그는 2년 전 댓글을 달았습니다
0

하지만 지금은 그 코드를 사용하는 CPU 와 무한히 많은 말 주석문입니다 11.4 배에서 판독합니다 Itik& # 39 의 논리 (2,000,023) 보다 더 캐스케이딩이란 스테 메서드입니다.

나중에, & # 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

39 의 스테 않은 것이 그 후 이 스테 향상합니다 더 판독합니다 Itzik& 전통표기 스테.

  • 그러나 다음 다른 queries.* 일관되게 향상합니다 덜 쓰기 아시다시피 훨씬 비싼 어졌다면 판독합니다 일관성 있는 쓰기뿐만 적지 않다.

이 기간 따라 수에 따라 크게 코어 (맥스도프) 이지만, 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 활성화되었습니다.

편집: # 39 의 주석문입니다 Conrad& 볼 수 있습니다.

39 의 제프 Moden& 오토메이티드 아주 좋다. 하지만 내가 찾을 수 있는 방법을 않으면 실패할 Postgres 작업자쪽에서 이치크 분리하십시오 E32 행일.

내가 조금 빠름 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 가질 수 있는 테이블을 누락했습니다 탤리를 있는 플랫폼. 정수 ()? 시퀀스 ()?

Ruskin
Conrad Frix
그는 8년 전 댓글을 달았습니다
2
Ruskin
그는 7년 전 댓글을 달았습니다
0

감사합니다 콘래드. 루키 오류:.

Jeff Moden
그는 2년 전 댓글을 달았습니다
0

매우 늦은 사과를 @conrad 프리스 대한 질문 (5 년 이상 늦은) 이지만, have you done 내장된 도구에서는 비교해 뛰어난 성능 테스트에서 모든 다른 방법을 사용하여?

Conrad Frix
그는 2년 전 댓글을 달았습니다
0

아니, 근데 # 39 의 쉽냐구요 테스트하려면 it& @jeffmoden 죄송합니다. # 39 의 Ruskin& 시행하십시오 쿼리하고 비교하여 call to 발령합니다 시리즈.

Jeff Moden
그는 2년 전 댓글을 달았습니다
0

넌 내가 너 때문에 이 같은 주장을 모두 액세스하도록 @conrad 프리스 성능과 환경 (어떤 난 don& # 39, t) 와 너 또한 쉽게 하였노라너희가 it& # 39 의 테스트하려면 you&; d 시행하십시오 테스트하려면 거잖나. # 39 시간, 정말 미안해. -)

Conrad Frix
그는 2년 전 댓글을 달았습니다
0

Postgresql 은 매우 클 수 있는 무료 테스터 성능 진실이며당신이 @jeffmoden 것으로 보인다. # 39 의 it& 다는일은 선택해제합니다 왜 제가 쓴 《 수도 없습니다 그냥 https://partner. microsoft. doc 논평을 요구하고 있다. ). 또한 이 시리즈는 빠른 http://sqlfiddle.com/ #! 17/9eecb/20843 전망은 매우 밝은 발령합니다 # 39, ve done in depth 테스트 you& 있지만 선뜻 이해가 되지 않는다

Jeff Moden
그는 2년 전 댓글을 달았습니다
1

@Conrad 프리스, 이젠 아니야. 넌 이미 할꺼이나 설정 진실이며당신이 can& # 39 5 분, t 시행하십시오 İç 테스트하려면 하였노라너희가 성능. NP. 다른거,