사람들은 테이블 변수와 임시 테이블의 상대적인 장점에 대해 많은 논쟁을 할 수 있습니다. 때로는 함수를 작성할 때와 같이 선택의 여지가 없습니다. 하지만 그렇게하면 둘 다 용도가 있다는 것을 알게 될 것이며 둘 중 하나가 더 빠른 예를 쉽게 찾을 수 있습니다. 이 기사에서는 둘 중 하나를 선택하는 데 관련된 주요 요소를 설명하고 최상의 성능을 얻기위한 몇 가지 간단한 규칙을 보여 드리겠습니다.
기본 참여 규칙을 따른다고 가정합니다. , 상대적으로 작은 데이터 세트로 작업 할 때 테이블 변수를 첫 번째 선택으로 고려해야합니다. 임시 테이블을 사용하는 것에 비해 작업하기가 더 쉽고 사용되는 루틴에서 재 컴파일을 더 적게 트리거합니다. 또한 테이블 변수는이를 생성 한 프로세스 및 일괄 처리에 대해 개인용이므로 잠금 리소스가 더 적게 필요합니다. SQL Prompt는이 권장 사항을 코드 분석 규칙으로 구현합니다. ST011 – 임시 테이블 대신 테이블 변수 사용을 고려하십시오.
임시 데이터에 대해 더 복잡한 처리를 수행하거나 합리적으로 적은 양보다 많은 양을 사용해야하는 경우 데이터가있는 경우 로컬 임시 테이블이 더 나은 선택 일 수 있습니다. SQL Code Guard에는 그의 권장 사항 인 ST012 – 테이블 변수 대신 임시 테이블을 사용하는 것을 고려하는 코드 분석 규칙이 포함되어 있지만 현재 SQL 프롬프트에는 구현되어 있지 않습니다.
테이블 변수 및 임시 테이블의 장단점
테이블 변수를 사용하는 쿼리는 때때로 매우 비효율적 인 실행 계획을 생성하기 때문에 나쁜 보도를받는 경향이 있습니다. 그러나 몇 가지 간단한 규칙을 따르면 중간 작업테이블과 데이터 세트가 작고 필요한 처리가 비교적 간단한 루틴간에 결과를 전달하는 데 적합합니다.
테이블 변수는 주로 “유지 관리가 필요하지”않기 때문에 사용이 매우 간단합니다. 테이블 변수는 생성 된 배치 또는 루틴으로 범위가 지정되며 실행이 완료되면 자동으로 제거되므로 수명이 긴 연결 내에서 사용됩니다. tempdb에서 리소스 호깅문제가 발생할 위험이 없습니다. 테이블 변수가 저장 프로 시저에서 선언 된 경우 해당 저장 프로 시저에 대해 로컬이며 중첩 된 프로 시저에서 참조 할 수 없습니다. 테이블 변수에 대한 통계 기반 재 컴파일도 없습니다. ALTER 하나를 사용할 수 없으므로이를 사용하는 루틴은 임시 테이블을 사용하는 루틴보다 재 컴파일이 더 적게 발생하는 경향이 있습니다. 또한 완전히 로깅되지 않으므로 작성 및 채우기가 더 빠르고 t에 더 적은 공간이 필요합니다. ransaction 로그. 저장 프로 시저에서 사용되면 동시성이 높은 조건에서 시스템 테이블에 대한 경합이 적습니다. 요컨대, 깔끔하고 깔끔하게 유지하는 것이 더 쉽습니다.
비교적 작은 데이터 세트로 작업 할 때 유사한 임시 테이블보다 빠릅니다. 그러나 행 수가 약 15K 행을 넘어서면서 컨텍스트에 따라 달라짐에 따라 주로 통계 지원 부족으로 인해 어려움에 처할 수 있습니다. 테이블 변수에 PRIMARY KEY 및 UNIQUE 제약 조건을 적용하는 인덱스에도 통계가 없습니다. . 따라서 옵티마이 저는 테이블 변수에서 반환 된 1 행의 하드 코딩 된 추정을 사용하므로 소규모 데이터 세트 작업에 최적 인 연산자 (예 : 조인을위한 Nested Loops 연산자)를 선택하는 경향이 있습니다. 테이블 변수에 행이 많을수록 추정과 현실 사이의 불일치가 커지고 최적화 프로그램의 계획 선택이 더 비효율적입니다. 결과 계획은 때때로 끔찍합니다.
숙련 된 개발자 또는 DBA는 이러한 종류의 문제를 살펴보고 OPTION
테이블 변수를 사용하는 문에 대한 쿼리 힌트입니다. 테이블 변수가 포함 된 배치를 제출할 때 최적화 프로그램은 먼저 테이블 변수가 비어있는 지점에서 배치를 컴파일합니다. 일괄 처리가 실행되기 시작하면 힌트는 해당 단일 문만 다시 컴파일되도록합니다. 이때 테이블 변수가 채워지고 최적화 프로그램은 실제 행 수를 사용하여 해당 문에 대한 새 계획을 컴파일 할 수 있습니다. 가끔은 드물지만이 방법으로도 도움이되지 않습니다. 또한이 힌트에 대한 과도한 의존은 테이블 변수가 임시 테이블보다 재 컴파일을 덜 발생시키는 이점을 어느 정도 무효화합니다.
둘째, 테이블 변수에 대한 특정 인덱스 제한은 다룰 때 더 많은 요인이됩니다. 큰 데이터 세트. 이제 인라인 인덱스 생성 구문을 사용하여 테이블 변수에 비 클러스터형 인덱스를 생성 할 수 있지만 몇 가지 제한 사항이 있으며 여전히 관련 통계가 없습니다.
상대적으로 적은 행 수를 사용하더라도 조인 쿼리를 실행하려고 할 때 PRIMARY를 정의하는 것을 잊으면 쿼리 성능 문제가 발생할 수 있습니다. 조인에 사용중인 열에 대한 KEY 또는 UNIQUE 제약 조건입니다. 제공하는 메타 데이터가 없으면 옵티마이 저는 데이터의 논리적 순서 또는 조인 열의 데이터에 중복 값이 있는지 여부를 알지 못하며 비효율적 인 조인 작업을 선택하여 쿼리 속도가 느려질 수 있습니다. 테이블 변수 힙으로 작업하는 경우 단일 꿀꺽 꿀꺽 (테이블 스캔)에서 처리 될 가능성이있는 간단한 목록 만 사용할 수 있습니다. 정확한 카디널리티 추정을 위해 OPTION (RECOMPILE) 힌트의 두 가지 사용을 결합하고 최적화 프로그램에 유용한 정보를 제공하는 조인 열의 키 메타 데이터를 사용하면 더 작은 데이터 세트의 경우 로컬 임시 테이블을 사용하는 것과 비슷하거나 더 나은 쿼리 속도를 얻을 수 있습니다.
행 수가 테이블 변수의 안락한 영역을 넘어 증가하거나 더 복잡한 데이터를 수행해야하는 경우 처리하면 임시 테이블을 사용하도록 전환하는 것이 가장 좋습니다. 여기에서 인덱싱에 사용할 수있는 전체 옵션이 있으며 옵티마이 저는 이러한 인덱스 각각에 대한 통계를 사용할 수 있습니다. 물론 단점은 임시 테이블이 유지 관리 비용이 더 많이 든다는 것입니다. tempdb 혼잡을 피하기 위해 스스로 정리해야합니다. 임시 테이블을 변경하거나 그 안의 데이터를 수정하면 부모 루틴의 재 컴파일이 발생할 수 있습니다.
많은 삭제 및 삽입 (행 집합 공유)이 필요한 경우 임시 테이블이 더 좋습니다. ). 임시 테이블 만 자르기를 지원하므로 데이터를 테이블에서 완전히 제거해야하는 경우 특히 그렇습니다. 통계 부족 및 재 컴파일과 같은 테이블 변수 디자인의 타협은 데이터가 휘발성 일 경우 영향을 미칩니다.
테이블 변수를 사용하는 데 비용이들 때
우리는 테이블 변수가 이상적인 예에서 시작하여 더 나은 성능을 제공합니다. Adventureworks의 직원 목록, 근무하는 부서 및 근무 교대를 생성합니다. 작은 데이터 세트 (291 행)를 처리하고 있습니다.
결과를 다음 배치로 전달하는 것처럼 두 번째 임시 테이블에 결과를 저장합니다. 목록 1은 코드를 보여줍니다.
그리고 다음은 저의 느린 테스트 머신의 일반적인 결과입니다.
개별 실행이 상당히 다를 수 있지만 임시 테이블을 사용하는 것은 지속적으로 느립니다.
확장 문제와 키 또는 힌트 제공을 잊어 버린 경우
두 테이블 변수를 결합하면 성능이 어떻습니까? 시도해 봅시다. 이 예에서는 두 개의 간단한 테이블이 필요합니다. 하나는 영어로 된 모든 공통 단어 (CommonWords)와 Bram Stoker의 Dracula에있는 모든 단어 목록이있는 테이블입니다. (WordsInDracula). TestTVsAndTTs 다운로드에는 이러한 두 테이블을 만들고 관련 텍스트 파일에서 각각을 채우는 스크립트가 포함되어 있습니다. 60,000 개의 일반적인 단어가 있지만 Bram Stoker는 그중 10,000 개만 사용했습니다. 전자는 임시 테이블을 선호하기 시작하는 손익분기 점을 훨씬 벗어납니다.
4 개의 간단한 외부 조인 쿼리를 사용하여 NULL 값은 드라큘라에없는 일반적인 단어, 드라큘라에있는 일반적인 단어, 흔하지 않은 드라큘라의 단어, 마지막으로 드라큘라에서 일반적인 단어를 찾는 또 다른 쿼리이지만 반대 방향으로 결합하는 값입니다. Test Rig의 코드를 표시하면 곧 쿼리가 표시됩니다.
다음은 초기 테스트 실행의 결과입니다. 첫 번째 실행에서는 두 테이블 변수에 모두 기본 키가 있고 두 번째 실행에서는 두 테이블 변수가 모두 힙입니다. 테이블 변수에서 인덱스를 선언하지 않는 문제를 과장하고 있는지 확인하기 위해서입니다. 마지막으로 임시 테이블로 동일한 쿼리를 실행합니다. 모든 테스트는 설명을 위해 느린 개발 서버에서 의도적으로 실행되었습니다. 프로덕션 서버에서는 매우 다른 결과를 얻을 수 있습니다.
결과는 테이블 변수가 힙일 때 쿼리가 100 밀리 초가 아닌 10 분 동안 실행될 위험이 있음을 보여줍니다. 이것은 규칙을 모르면 경험할 수있는 무시 무시한 성능의 좋은 예입니다. 하지만 기본 키를 사용할 때도 우리가 다루는 행의 수는 임시 테이블 사용이 이제 두 배 빨라진다는 것을 의미합니다.
이러한 실행 계획에 대한 자세한 내용은 다루지 않겠습니다. 주요 차이점에 대한 몇 가지 광범위한 설명을 제공하는 것 외에 성능 메트릭. 임시 테이블 쿼리의 경우 카디널리티 및 기본 키 제약 조건의 메타 데이터에 대한 전체 지식을 갖춘 최적화 프로그램은 효율적인 병합 조인 연산자를 선택하여 조인 작업을 수행합니다.기본 키가있는 테이블 변수의 경우 옵티마이 저는 조인 열에있는 행의 순서를 알고 있으며 중복이 포함되어 있지 않지만 한 행만 처리한다고 가정하므로 대신 중첩 루프 조인을 선택합니다. 여기에서 하나의 테이블을 스캔 한 다음 반환 된 각 행에 대해 다른 테이블의 개별 검색을 수행합니다. 이는 데이터 세트가 클수록 효율성이 떨어지며 CommonWords 테이블 변수를 스캔하는 경우에 특히 나쁩니다. 테이블 변수. Nested Loops 조인은 CommonWords에 대한 수천 번의 테이블 스캔을 수반하기 때문에 테이블 변수 힙을 사용하는 2 개의 10 분 쿼리에 대해 최고 비효율에 도달합니다. 흥미롭게도 두 개의 “Dracula의 일반적인 단어”쿼리가 훨씬 더 나은 성능을 발휘합니다.이 두 쿼리의 경우 최적화 프로그램이 대신 해시 일치 조인을 선택했기 때문입니다.
전반적으로 임시 테이블이 최선의 선택 인 것 같습니다. 하지만 아직 완료되지 않았습니다. 기본 키와 함께 테이블 변수를 사용하는 쿼리에 OPTION (RECOMPILE) 힌트를 추가해 보겠습니다. 이러한 쿼리에 대한 테스트와 임시 테이블을 사용하는 원래 쿼리를 다시 실행합니다. 당분간 불량 힙은 제외합니다.
보시다시피 임시 테이블의 성능 이점은 사라집니다. 올바른 행 수와 순서가 지정된 입력, 최적화 프로그램은 훨씬 더 효율적인 병합 조인을 선택합니다.
불량한 힙에 OPTION를 제공하면 어떻게 될까요? (RECOMPILE) 힌트도 있습니까? 자, 이야기가 바뀌어 세 가지 타이밍이 모두 훨씬 더 가깝습니다.
흥미롭게도 두 개의 “드라큘라의 일반적인 단어”쿼리는 했다 힙에서도 빠름은 이제 훨씬 더 느립니다. 올바른 행 수로 무장 한 최적화 프로그램은 전략을 변경하지만 제약 조건과 키를 정의 할 때 사용할 수있는 유용한 메타 데이터가 여전히 없기 때문에 잘못된 선택을합니다. CommonWords 힙을 스캔 한 다음 “부분 집계”를 시도하여 60K 행에서 수백 행으로 집계 될 것으로 예상합니다. 중복이 없다는 것을 알지 못합니다. 실제로는 전혀 집계되지 않으며 집계 및 후속 조인이 tempdb로 유출됩니다.
테스트 장비
이것은 최종 형식의 테스트 장비입니다. 세 가지 다른 유형의 테이블에 대해 거의 동일한 성능을 보여줍니다. 원본으로 돌아가려면 OPTION (RECOMPILE) 힌트를 제거해야합니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
> 137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
USE PhilFactor;
–Dracula의 모든 단어가 포함 된 작업 테이블을 만듭니다.
DECLARE @WordsInDracula TABLE
(word VARCHAR ( 40) NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @WordsInDracula (단어) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
-모든 공통 단어가 포함 된 다른 작업 테이블 생성
DECLARE @CommonWords TABLE
(word VARCHAR ( 40) NOT NULL PRIMARY KEY CLUSTERED);
INSERT INTO @CommonWords (단어) SELECT commonwords.word FROM dbo.commonwords;
-타이밍 로그 생성
DECLARE @log TABLE
(TheOrder INT IDENTITY (1, 1),
WhatHappened VARCHAR (200),
WhenItDid DATETIME2 DEFAULT GetDate ());
—- 타이밍 시작 (보고되지 않음)
INSERT INTO @log (WhatHappened) SELECT “Starting My_Section_of_code”;
-시작 위치
————— 테이블 변수를 사용하는 코드 섹션
-테이블 변수를 사용하는 코드의 첫 번째 시간 섹션
SELECT Count (*) AS
FROM @CommonWords AS c
LEFT OUTER JOIN @WordsInDracula AS d
ON d.word = c.word
WHERE d.word가 NULL 인 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened)
SELECT “드라큘라에없는 일반 단어 : 기본 키가있는 두 테이블 변수”;
-시간을 지정할 루틴이 끝나는 위치
-테이블 변수를 사용하는 코드의 두 번째 시간 섹션
SELECT Count (*) AS
FROM @CommonWords AS c
LEFT OUTER JOIN @WordsInDracula AS d
ON d.word = c.word
어디서 d.단어가 NULL이 아닙니다.
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened)
SELECT “드라큘라의 공통 단어 : 기본 키가있는 두 테이블 변수”;
-시간을 지정할 루틴이 끝나는 곳
-테이블 변수를 사용하는 세 번째 시간 코드 섹션
SELECT Count (*) AS
FROM @WordsInDracula AS d
LEFT OUTER JOIN @CommonWords AS c
ON d.word = c.word
c.word가 NULL 인 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened)
SELECT “드라큘라에서 흔히 볼 수없는 단어 : 기본 키가있는 두 테이블 변수”;
-시간을 지정할 루틴이 끝나는 곳
-테이블 변수를 사용하는 마지막 시간 코드 섹션
SELECT Count (*) AS
FROM @WordsInDracula AS d
LEFT OUTER JOIN @CommonWords AS c
ON d.word = c.word
c.word가 NULL이 아닌 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened)
SELECT “드라큘라의 일반적인 단어 : 기본 키가있는 두 테이블 변수”;
-시간을 지정할 루틴이 끝나는 곳
————— 힙 변수
DECLARE @WordsInDraculaHeap TABLE (word VARCHAR (40) NOT NULL);
INSERT INTO @WordsInDraculaHeap (word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
DECLARE @CommonWordsHeap TABLE (word VARCHAR (40) NOT NULL);
INSERT INTO @CommonWordsHeap (word) SELECT commonwords.word FROM dbo.commonwords;
INSERT INTO @log (WhatHappened) SELECT “Test Rig Setup”;
-시간을 지정할 루틴이 끝나는 곳
-힙 변수를 사용하는 코드의 첫 번째 시간 섹션
SELECT Count (*) AS
FROM @CommonWordsHeap AS c
LEFT OUTER JOIN @WordsInDraculaHeap AS d
ON d.word = c.word
d.word가 NULL 인 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened) SELECT “드라큘라에없는 공통 단어 : 두 힙”;
-시간을 지정할 루틴이 끝나는 곳
-힙 변수를 사용하는 두 번째 시간 코드 섹션
SELECT Count (*) AS
FROM @CommonWordsHeap AS c
LEFT OUTER JOIN @WordsInDraculaHeap AS d
ON d.word = c.word
d.word가 NULL이 아닌 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened) SELECT “드라큘라의 일반적인 단어 : 두 힙”;
-시간을 지정하려는 루틴이 끝나는 곳
-힙 변수를 사용하는 세 번째 시간 코드 섹션
SELECT Count (*) AS
FROM @WordsInDraculaHeap AS d
LEFT OUTER JOIN @CommonWordsHeap AS c
ON d.word = c.word
c.word가 NULL 인 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened) SELECT “드라큘라에서 흔히 볼 수없는 단어 : 두 힙”;
-시간을 정하려는 루틴이 끝나는 곳
-힙 변수를 사용하는 코드의 마지막 시간 섹션
SELECT Count (*) AS
FROM @WordsInDraculaHeap AS d
LEFT OUTER JOIN @CommonWordsHeap AS c
ON d.word = c.word
c.word가 NULL이 아닌 경우
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened) SELECT “드라큘라의 일반적인 단어 : 두 힙”;
-시간을 지정할 루틴이 끝나는 곳
————— 임시 테이블
CREATE TABLE #WordsInDracula (word VARCHAR (40) NOT NULL PRIMARY KEY);
INSERT INTO #WordsInDracula (word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
CREATE TABLE #CommonWords (word VARCHAR (40) NOT NULL PRIMARY KEY);
INSERT INTO #CommonWords (word) SELECT commonwords.word FROM dbo.commonwords;
INSERT INTO @log (WhatHappened) SELECT “Temp Table Test Rig Setup”;
-시간을 지정할 루틴이 끝나는 곳
-임시 테이블을 사용하는 코드의 첫 번째 시간 섹션
SELECT Count (*) AS
FROM #CommonWords AS c
LEFT OUTER JOIN #WordsInDracula AS d
ON d.word = c.word
WHERE d.word가 NULL입니다.
INSERT INTO @log (WhatHappened) SELECT “드라큘라에없는 일반적인 단어 : 두 임시 테이블”;
-시간을 정하려는 루틴이 끝나는 곳
-임시 테이블을 사용하는 두 번째 시간 지정 코드 섹션
SELECT Count (*) AS
FROM #CommonWords AS c
LEFT OUTER JOIN #WordsInDracula AS d
ON d.word = c.word
어디서 d.단어가 NULL이 아닙니다.
INSERT INTO @log (WhatHappened) SELECT “드라큘라의 일반적인 단어 : 두 임시 테이블”;
-시간을 정하려는 루틴이 끝나는 곳
-임시 테이블을 사용하는 세 번째 시간 코드 섹션
SELECT Count (*) AS
FROM #WordsInDracula AS d
LEFT OUTER JOIN #CommonWords AS c
ON d.word = c.word
WHERE c.word가 NULL입니다.
INSERT INTO @log (WhatHappened) SELECT “드라큘라에서 흔히 볼 수없는 단어 : 두 임시 테이블”;
-시간을 지정할 루틴이 끝나는 곳
-임시 테이블을 사용하는 마지막 시간이 지정된 코드 섹션
SELECT Count (*) AS
FROM #WordsInDracula AS d
LEFT OUTER JOIN #CommonWords AS c
ON d.word = c.word
여기서 c.word가 NULL이 아닙니다.
INSERT INTO @log (WhatHappened) SELECT “드라큘라의 일반적인 단어 : 두 임시 테이블”; -시간을 정하려는 루틴이 끝나는 곳
DROP TABLE #WordsInDracula;
DROP TABLE #CommonWords;
SELECT ending.WhatHappened AS,
DateDiff (ms, starting.WhenItDid, ending.WhenItDid) AS
FROM @log 시작으로
INNER JOIN @log AS 끝
ON ending.TheOrder = starting.TheOrder + 1;
-모든 타이밍 나열
|
목록 2
결론
테이블 변수를 사용하는 데 무모한 것은 없습니다. 의도 한 용도로 사용할 때 더 나은 성능을 제공하며 자체적으로 정리합니다. 특정 시점에서 더 나은 성능을 제공하는 타협 (재 컴파일 트리거, 통계 제공, 롤백, 병렬 처리 없음)이 몰락하게됩니다.
종종 SQL Server 전문가는 다음과 같은 현명한 조언을 제공합니다. 테이블 변수에 문제를 일으킬 결과의 크기. 이 기사에서 보여 드린 결과는 이것이 문제를 지나치게 단순화한다는 것을 암시 할 것입니다. 두 가지 중요한 요소가 있습니다. 1000 개의 행 (이 수치는 컨텍스트에 따라 다름)이 초과 된 결과가있는 경우 PRIMARY KEY 또는 UNIQUE 키입니다. 특정 시점에서 자체 오버 헤드가있는 적절한 실행 계획을 얻기 위해 재 컴파일을 트리거해야합니다.
그런 경우에도 특히 더 복잡한 처리를 수행하는 경우 성능이 나빠질 수 있습니다. , 옵티마이 저는 여전히 통계에 액세스 할 수 없으므로 쿼리 술어의 선택성에 대한 지식이 없기 때문입니다. 이 경우 임시 테이블 사용으로 전환해야합니다.