Lidé mohou a mohou hodně argumentovat o relativních výhodách proměnných tabulky a dočasných tabulek. Někdy, jako při psaní funkcí, nemáte jinou možnost; ale když zjistíte, že oba mají své využití, je snadné najít příklady, kde je jeden z nich rychlejší. V tomto článku vysvětlím hlavní faktory spojené s výběrem jednoho nebo druhého a předvedu několik jednoduchých „pravidel“ pro dosažení nejlepšího výkonu.
Za předpokladu, že budete dodržovat základní pravidla zapojení , pak byste měli při práci s relativně malými datovými sadami považovat proměnné tabulky za první volbu. Snadněji se s nimi pracuje a spouští méně rekompilací v rutinách, ve kterých se používají, ve srovnání s použitím dočasných tabulek. Proměnné tabulky také vyžadují méně uzamykacích prostředků, protože jsou „soukromé“ pro proces a dávku, která je vytvořila. Výzva SQL implementuje toto doporučení jako pravidlo analýzy kódu, ST011 – zvažte použití dočasné tabulky namísto dočasné tabulky.
Pokud provádíte složitější zpracování dočasných dat nebo potřebujete použít více než přiměřeně malé množství data v nich, pak místní dočasné tabulky budou pravděpodobně lepší volbou. SQL Code Guard obsahuje na základě jeho doporučení pravidlo pro analýzu kódu, ST012 – Zvažte použití dočasné tabulky místo proměnné tabulky, ale není aktuálně implementována v SQL Prompt.
Výhody a nevýhody proměnných tabulky a dočasných tabulek
Proměnné tabulky mají tendenci získávat „špatný tisk“, protože dotazy, které je občas používají, mají za následek velmi neefektivní plány provádění. Pokud však budete postupovat podle několika jednoduchých pravidel, jsou dobrou volbou pro přechodné „pracovní“ tabulky a pro předávání výsledků mezi rutinami, kde jsou datové sady malé a požadované zpracování je relativně jednoduché.
Proměnné tabulky se používají velmi snadno, hlavně proto, že mají „nulovou údržbu“. Jsou vymezeny na dávku nebo rutinu, ve které jsou vytvořeny, a jsou automaticky odstraněny, jakmile dokončí provádění, a proto je lze použít v rámci dlouhodobého připojení. neriskuje problémy „hogging zdrojů“ v databázi tempdb. Pokud je proměnná tabulky deklarována v uložené proceduře, je pro tuto uloženou proceduru lokální a nelze na ni vnořenou proceduru odkazovat. Pro proměnné tabulky a statistické proměnné a nemůžete ALTER jeden, takže rutiny, které je používají, mají tendenci vynakládat méně rekompilací než ty, které používají dočasné tabulky. Také nejsou plně protokolovány, takže jejich vytváření a vyplňování je rychlejší a vyžaduje méně prostoru v t deník transakcí. Když se používají v uložených procedurách, je v systémových tabulkách za podmínek vysoké souběžnosti menší spor. Stručně řečeno, je snazší udržovat pořádek a pořádek.
Při práci s relativně malými datovými sadami jsou rychlejší než srovnatelná dočasná tabulka. Jak se však počet řádků zvyšuje, přesahuje přibližně 15 tisíc řádků, ale liší se podle kontextu, můžete narazit na potíže, hlavně kvůli nedostatečné podpoře statistik. Statistiky nemají ani indexy, které vynucují omezení PRIMARY KEY a UNIQUE u proměnných tabulky . Optimalizátor proto použije pevně stanovený odhad 1 řádku vráceného z proměnné tabulky, a proto bude mít tendenci volit operátory optimální pro práci s malými datovými sadami (například operátor Vnořené smyčky pro spojení). Čím více řádků v proměnné tabulky, tím větší jsou nesrovnalosti mezi odhadem a realitou a tím neefektivnější se stanou volby plánu optimalizátoru. Výsledný plán je někdy děsivý.
Zkušený vývojář nebo DBA bude tento druh problému hledat a bude připraven přidat OPTION (RECOMPILE) nápověda k dotazu na příkaz, který používá proměnnou tabulky. Když zadáme dávku obsahující proměnnou tabulky, optimalizátor nejprve sestaví dávku, kdy je proměnná tabulky prázdná. Když se dávka začne spouštět, nápověda způsobí překompilaci pouze tohoto jediného příkazu, kdy se proměnná tabulky naplní a optimalizátor může použít skutečný počet řádků ke kompilaci nového plánu pro daný příkaz. Někdy, ale zřídka, ani to nepomůže. Nadměrné spoléhání se na tuto nápovědu také do určité míry vyvrátí výhodu, kterou mají proměnné tabulky, protože způsobují méně rekompilací než dočasné tabulky.
Zadruhé, určitá omezení indexu s proměnnými tabulky se stávají více faktorem při řešení velké datové soubory. I když nyní můžete použít syntaxi vytváření vloženého indexu k vytvoření neseskupených indexů v proměnné tabulky, existují určitá omezení a stále neexistují žádné související statistiky.
I při relativně skromném počtu řádků se můžete setkat s problémy s výkonem dotazu, pokud se pokusíte provést dotaz, který je spojením, a zapomenete definovat PRIMARY KEY nebo UNIQUE omezení sloupce, který pro připojení používáte. Bez metadat, která poskytují, nemá optimalizátor žádné znalosti logického pořadí dat ani toho, zda data ve sloupci spojení obsahují duplicitní hodnoty, a pravděpodobně zvolí neefektivní operace spojení, což povede k pomalým dotazům. Pokud pracujete s haldy proměnné tabulky, můžete ji použít pouze jako jednoduchý seznam, který bude pravděpodobně zpracován jediným hltem (sken tabulky). Pokud zkombinujete jak použití OPTION (RECOMPILE) nápovědy pro přesné odhady mohutnosti, tak klíč ve sloupci spojení, který optimalizátoru poskytne užitečné metadata, pak u menších datových sad můžete často dosáhnout rychlosti dotazu podobné nebo lepší než při použití místní dočasné tabulky.
Jakmile se počty řádků zvýší nad komfortní zónu proměnné tabulky, nebo budete muset provést složitější data zpracování, pak je nejlepší přepnout na používání dočasných tabulek. Zde máte k dispozici všechny možnosti indexování a optimalizátor bude mít luxus použití statistik pro každý z těchto indexů. Nevýhodou samozřejmě je, že dočasné tabulky mají vyšší náklady na údržbu. Musíte se ujistit, že jste se uklidili, abyste předešli zahlcení tempdb. Pokud změníte dočasnou tabulku nebo upravíte data v ní, může dojít k překompilování nadřazené rutiny.
Dočasné tabulky jsou lepší, když existuje požadavek na velký počet odstranění a vložení (sdílení sady řádků) ). To platí zejména v případě, že data musí být z tabulky zcela odstraněna, protože zkrácení podporují pouze dočasné tabulky. Kompromisy v návrhu proměnných tabulky, jako je nedostatek statistik a překompilování, proti nim fungují, pokud jsou data nestálá.
Když se vyplatí použít proměnné tabulky
My Začnu příkladem, kde je proměnná tabulky ideální a vede k lepšímu výkonu. Vypracujeme seznam zaměstnanců Adventureworks, ve kterém oddělení pracují, a směn, ve kterých pracují. Máme co do činění s malou datovou sadou (291 řádků).
Výsledky umístíme do druhé dočasné tabulky, jako bychom výsledek předávali další dávce. Výpis 1 ukazuje kód.
A tady je typický výsledek na mém pomalém testovacím stroji:
Používání dočasné tabulky je trvale pomalejší, i když se jednotlivé běhy mohou značně lišit.
Problémy s rozsahem a zapomenutím poskytnout klíč nebo nápovědu
Jaký je výkon, když spojíme dvě proměnné tabulky? Zkusme to. V tomto příkladu potřebujeme dvě jednoduché tabulky, jednu se všemi běžnými slovy v anglickém jazyce (CommonWords) a druhou se seznamem všech slov v Drákula Brama Stokera (WordsInDracula). Stažení TestTVsAndTTs zahrnuje skript pro vytvoření těchto dvou tabulek a naplnění každé z jejího přidruženého textového souboru. Existuje 60 000 běžných slov, ale Bram Stoker je použil pouze 10 000. První z nich je zcela mimo bod zvratu, kdy je třeba upřednostňovat dočasné tabulky.
Použijeme čtyři jednoduché dotazy na vnější spojení, které testují výsledek pro NULL hodnoty, abyste zjistili běžná slova, která nejsou v Drákula, běžná slova, která jsou v Drákula, slova v Drákula, která jsou neobvyklá, a nakonec další dotaz pro nalezení běžných slov v Drákula, ale spojování v opačném směru. Dotazy uvidíte brzy, až ukážu kód pro testovací soupravu.
Následují výsledky počátečních testovacích běhů. V prvním spuštění mají obě proměnné tabulky primární klíče a ve druhém jsou obě hromady, jen abych zjistil, jestli přehánám problémy s nedeklarováním indexu v proměnné tabulky. Nakonec spustíme stejné dotazy s dočasnými tabulkami. Všechny testy byly pro ilustraci spuštěny záměrně na serveru s pomalým vývojem; s produkčním serverem získáte velmi odlišné výsledky.
Výsledky ukazují, že když jsou proměnné tabulky hromadné, riskujete spuštění dotazu po dobu deseti minut, nikoli 100 milisekund. Jsou skvělým příkladem příšerného výkonu, který můžete zažít, pokud neznáte pravidla. I když používáme primární klíče, počet řádků, se kterými se potýkáme, znamená, že používání dočasných tabulek je nyní dvakrát rychlejší.
Nebudu se ponořit do podrobností plánů provádění za nimi metriky výkonu, kromě několika obecných vysvětlení hlavních rozdílů. Pro dotazy na dočasnou tabulku zvolí optimalizátor, vyzbrojený úplnou znalostí mohutnosti a metadat z omezení primárního klíče, efektivní operátor sloučení spojení, který provede operaci spojení.U proměnné tabulek s primárními klíči zná optimalizátor pořadí řádků ve sloupci spojení a že neobsahují žádné duplikáty, ale předpokládá, že jde pouze o jeden řádek, a proto místo toho zvolí spojení vnořených smyček. Zde prohledá jednu tabulku a poté pro každý vrácený řádek provede individuální hledání druhé tabulky. Čím větší jsou datové soubory, tím je to méně efektivní, což je obzvláště špatné v případech, kdy prohledává proměnnou tabulky CommonWords, protože to má za následek více než 60 tis. Hledání Dracula proměnná tabulky. Spojení vnořených smyček dosahuje „maximální neefektivity“ dvou, desetiminutových dotazů pomocí hromádek proměnných tabulek, protože s sebou přináší tisíce skenování tabulek CommonWords. Zajímavé je, že dva dotazy „běžná slova v Draculovi“ fungují mnohem lépe, a to proto, že pro tyto dva optimalizátor místo toho vybral spojení Hash Match.
Celkově vypadají dočasné tabulky jako nejlepší volba , ale ještě jsme neskončili! Přidejme OPTION (RECOMPILE) nápovědu k dotazům, které používají proměnné tabulky s primárními klíči, a spusťte znovu testy těchto dotazů a původních dotazů pomocí dočasných tabulek. Prozatím vynecháme špatné hromady.
Jak vidíte, výhoda výkonu dočasné tabulky zmizí. Vyzbrojeni správné počty řádků a seřazené vstupy, optimalizátor vybere mnohem efektivnější sloučení spojení.
Co by se, divíte, stalo, kdybyste těm chudým hromadám dali OPTION (RECOMPILE) nápověda také? Hle, příběh se pro ně změní, takže všechny tři načasování jsou mnohem blíže.
Zajímavé je, že dvě „běžná slova v Drákula“ se ptají, že byly rychlé i na hromadách jsou nyní mnohem pomalejší. Vyzbrojen správným počtem řádků optimalizátor změní svou strategii, ale protože při definování omezení a klíčů stále nemá k dispozici žádná z užitečných metadat, dělá špatnou volbu. Naskenuje haldu CommonWords a poté se pokusí o „částečnou agregaci“ s odhadem, že se agreguje z řádků 60 kB na několik stovek. Neví, že neexistují žádné duplikáty, takže ve skutečnosti se vůbec neagreguje a agregace a následné spojení se přelije na tempdb.
Testovací souprava
Vezměte prosím na vědomí, že se jedná o testovací soupravu v její finální podobě vykazující zhruba stejný výkon pro tři různé typy tabulek. Chcete-li se vrátit k původní verzi, budete muset odstranit OPTION (RECOMPILE) tipy.
|
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;
– vytvořte pracovní stůl, který obsahuje všechna slova z Drákuly
DECLARE @WordsInDracula TABLE
(slovo VARCHAR ( 40) NENÍ NULL PRIMARY KEY CLUSTERED);
INSERT INTO @WordsInDracula (word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
– vytvořte druhou pracovní tabulku se všemi běžnými slovy v ní
DECLARE @CommonWords TABLE
(slovo VARCHAR ( 40) NENÍ NULL PRIMARY KEY CLUSTERED);
INSERT INTO @CommonWords (word) SELECT commonwords.word FROM dbo.commonwords;
– vytvoření časovacího protokolu
DECLARE @log TABLE
(TheOrder INT IDENTITY (1, 1),
WhatHappened VARCHAR (200),
WhenItDid DATETIME2 DEFAULT GetDate ());
—- začátek načasování (nikdy nenahlášeno)
INSERT INTO @log (WhatHappened) SELECT „Starting My_Section_of_code“;
– místo na začátku
————— část kódu pomocí tabulkových proměnných
– první časovaná část kódu pomocí proměnných tabulky
SELECT Count (*) AS
FROM @CommonWords AS c
LEFT OUTER JOIN @WordsInDracula AS d
ON d.word = c.word
WHERE d.word IS NULL
OPTION (RECOMPILE);
INSERT INTO @log (WhatHappened)
SELECT „běžná slova, která nejsou v Drákule: Obě proměnné tabulky s primárními klíči“;
– kde končí rutina, kterou chcete načasovat
– Druhá časovaná část kódu pomocí proměnných tabulky
SELECT Count (*) AS
FROM @CommonWords AS c
LEFT OUTER JOIN @WordsInDracula AS d
ON d.word = c.word
KDE d.slovo NENÍ NULL
MOŽNOST (RECOMPILACE);
INSERT INTO @log (WhatHappened)
SELECT „běžná slova v Dracula: Obě proměnné tabulky s primárními klíči“;
–kde končí rutina, kterou chcete načasovat
– třetí časovaná část kódu pomocí proměnných tabulky
SELECT Count (*) AS
FROM @WordsInDracula AS d
LEFT OUTER JOIN @CommonWords AS c
ON d.word = c.word
KDE c.word JE NULL
MOŽNOST (RECOMPILACE);
INSERT INTO @log (WhatHappened)
SELECT „neobvyklá slova v Dracula: Obě proměnné tabulky s primárními klíči“;
– kde končí rutina, kterou chcete časově ukončit
–poslední časovaná část kódu pomocí proměnných tabulky
SELECT Count (*) AS
FROM @WordsInDracula AS d
LEFT OUTER JOIN @CommonWords AS c
ON d.word = c.word
KDE c.word NENÍ NULL
MOŽNOST (RECOMPILACE);
INSERT INTO @log (WhatHappened)
VÝBĚR „běžnější slova v Drákula: Obě proměnné tabulky s primárními klíči“;
– kde končí rutina, kterou chcete načasovat
————— část kódu pomocí haldy proměnné
DECLARE @WordsInDraculaHeap TABLE (slovo VARCHAR (40) NOT NULL);
INSERT INTO @WordsInDraculaHeap (word) SELECT WordsInDracula.word FROM dbo.WordsInDracula;
PROHLÁŠENÍ @CommonWordsHeap TABULKA (slovo VARCHAR (40) NENÍ NULL);
INSERT INTO @CommonWordsHeap (word) SELECT commonwords.word FROM dbo.commonwords;
INSERT INTO @log (WhatHappened) SELECT „Test Rig Setup“;
–kde končí rutina, kterou chcete časově ukončit
– první časovaná část kódu pomocí haldy proměnných
SELECT počet (*) AS
FROM @CommonWordsHeap AS c
LEFT OUTER JOIN @WordsInDraculaHeap AS d
ON d.word = c.word
KDE JE d.word NULL
MOŽNOST (RECOMPILACE);
INSERT INTO @log (WhatHappened) SELECT „běžná slova, která nejsou v Drákule: Obě hromady“;
–kde končí rutina, kterou chcete časově ukončit
– druhá časovaná část kódu pomocí haldy proměnných
SELECT počet (*) AS
FROM @CommonWordsHeap AS c
LEFT OUTER JOIN @WordsInDraculaHeap AS d
ON d.word = c.word
KDE d.word NENÍ NULL
MOŽNOST (RECOMPILACE);
VLOŽTE DO @log (WhatHappened) VYBERTE „běžná slova v Drákula: Obě hromady“;
–kde končí rutina, kterou chcete načasovat
– třetí časovaná část kódu používající haldy proměnných
SELECT počet (*) AS
FROM @WordsInDraculaHeap AS d
LEFT OUTER JOIN @CommonWordsHeap AS c
ON d.word = c.word
KDE c.word JE NULL
MOŽNOST (RECOMPILACE);
INSERT INTO @log (WhatHappened) SELECT „neobvyklá slova v Drákule: Obě hromady“;
–kde končí rutina, kterou chcete načasovat
–poslední časovaná část kódu pomocí haldy proměnných
SELECT počet (*) AS
FROM @WordsInDraculaHeap AS d
LEFT OUTER JOIN @CommonWordsHeap AS c
ON d.word = c.word
KDE c.word NENÍ NULL
MOŽNOST (RECOMPILACE);
VLOŽTE DO @log (WhatHappened) VYBERTE „běžná slova v Drákula: Obě hromady“;
– kde končí rutina, kterou chcete načasovat
————— část kódu pomocí Dočasné tabulky
CREATE TABLE #WordsInDracula (slovo VARCHAR (40) NOT NULL PRIMARY KEY);
VLOŽTE DO #WordsInDracula (slovo) VYBERTE WordsInDracula.word Z dbo.WordsInDracula;
VYTVOŘIT TABULKU #CommonWords (slovo VARCHAR (40) NENÍ NULL PRIMARY KEY);
INSERT INTO #CommonWords (word) SELECT commonwords.word FROM dbo.commonwords;
INSERT INTO @log (WhatHappened) SELECT „Temp Table Test Rig Setup“;
–kde končí rutina, kterou chcete časově ukončit
– první časovaná část kódu pomocí dočasných tabulek
SELECT počet (*) AS
FROM #CommonWords AS c
LEFT OUTER JOIN #WordsInDracula AS d
ON d.word = c.word
KDE d.slovo JE NULL;
INSERT INTO @log (WhatHappened) SELECT „běžná slova, která nejsou v Drákula: Obě dočasné tabulky“;
–kde končí rutina, kterou chcete načasovat
– Druhá časovaná část kódu pomocí dočasných tabulek
VÝBĚR Počet (*) AS
FROM #CommonWords AS c
LEFT OUTER JOIN #WordsInDracula AS d
ON d.word = c.word
KDE d.slovo NENÍ NULL;
INSERT INTO @log (WhatHappened) SELECT „běžná slova v Drákula: Obě dočasné tabulky“;
–kde končí rutina, kterou chcete časově ukončit
– třetí časovaná část kódu pomocí dočasných tabulek
SELECT Count (*) AS
FROM #WordsInDracula AS d
LEFT OUTER JOIN #CommonWords AS c
ON d.word = c.word
KDE c. slovo JE NULL;
INSERT INTO @log (WhatHappened) SELECT „neobvyklá slova v Drákula: Obě dočasné tabulky“;
–kde končí rutina, kterou chcete časově ukončit
–poslední časovaná část kódu pomocí dočasných tabulek
SELECT počet (*) AS
FROM #WordsInDracula AS d
LEFT OUTER JOIN #CommonWords AS c
ON d.word = c.word
KDE c.word NENÍ NULL;
INSERT INTO @log (WhatHappened) SELECT „běžná slova v Drákula: Obě dočasné tabulky“; – kde končí rutina, kterou chcete načasovat
DROP TABLE #WordsInDracula;
DROP TABULKA #CommonWords;
SELECT konec. WhatHappened AS,
DateDiff (ms, počínaje.WhenItDid, konec.WhenItDid) AS
Z @logu AS začíná
INNER JOIN @log AS končí
ON končí TheOrder = začíná TheOrder + 1;
– vypsat všechna časování
|
Výpis 2
Závěry
O použití proměnných tabulky není nic neuváženého. Při použití pro účely, pro které byly určeny, podávají lepší výkon a provádějí vlastní vyčištění. V určitém okamžiku se kompromisy, které jim poskytnou lepší výkon (nespouštění rekompilací, neposkytování statistik, žádné vrácení zpět, žádný paralelismus), stávají jejich úpadkem.
Odborník na SQL Server často poskytne mudrcům rady o velikost výsledku, který způsobí problémy pro tabulkovou proměnnou. Výsledky, které jsem vám ukázal v tomto článku, vám naznačují, že to problémy příliš zjednodušuje. Existují dva důležité faktory: pokud máte výsledek přes, řekněme 1000 řádků (a toto číslo závisí na kontextu), musíte mít PRIMARY KEY nebo UNIQUE pro všechny dotazy, které se připojují k proměnné tabulky. V určitém okamžiku budete také muset spustit rekompilaci, abyste získali slušný plán provádění, který má vlastní režii.
I tehdy může výkon špatně trpět, zvláště pokud provádíte složitější zpracování , protože optimalizátor stále nemá přístup ke statistikám, a tedy ani žádnou znalost selektivity žádného predikátu dotazu. V takových případech budete muset přepnout na používání dočasných tabulek.