Kiedy powinienem używać zmiennej tabeli a tabeli tymczasowej na serwerze SQL?

Uczę się więcej szczegółów w zmiennej tabeli. Mówi, że tabele temp są zawsze na dysku, a zmienne tabeli są w pamięci, co oznacza, że ​​wydajność tabeli jest lepsza niż tabela temp, ponieważ zmienna tabeli używa mniej operacji IO niż tabela temp.

Ale czasami, jeśli w zmiennej tabeli jest zbyt wiele rekordów, których nie można zapisać w pamięci, zmienna tabeli zostanie umieszczona na dysku jak tabela tymczasowa.

Ale nie wiem, co to jest „zbyt wiele płyt”. 100 000 rekordów? lub 1000 000 rekordów? Skąd mam wiedzieć, czy zmienna tabeli, której używam, znajduje się w pamięci lub na dysku? Czy w SQL Server 2005 jest jakaś funkcja lub narzędzie do mierzenia skali zmiennej tabeli lub powiadamianie mnie, kiedy zmienna tabeli jest umieszczana na dysku z pamięci?

Twoje pytanie pokazuje, że uległeś częstym nieporozumieniom dotyczącym zmiennych tabel i tabel tymczasowych.

Na stronie DBA napisałem dość obszerną odpowiedź temat różnic między tymi dwoma typami obiektów. To także dotyczy twojego pytania o dysk vs pamięć (nie widziałem żadnej znaczącej różnicy w zachowaniu między nimi).

Jeśli jednak chodzi o pytanie zawarte w tytule, kiedy użyć zmiennej tabeli zamiast lokalnej tabeli tymczasowej, nie zawsze masz wybór. Na przykład w funkcjach możliwe jest tylko użycie zmiennej tabeli, a jeśli musisz zapisać do tabeli w zakresie potomnym, #tempzrobi to tylko tabela (parametry wyceniane w tabeli umożliwiają dostęp tylko do odczytu ).

Tam, gdzie masz wybór, poniżej znajdują się niektóre sugestie (chociaż najbardziej niezawodną metodą jest po prostu przetestowanie obu z określonym obciążeniem).

1. Jeśli potrzebujesz indeksu, którego nie można utworzyć na zmiennej tabeli, to oczywiście potrzebujesz #temporary tabeli. Szczegóły tego są jednak zależne od wersji. W przypadku SQL Server 2012 i niższych jedynymi indeksami, które można utworzyć na zmiennych tabeli, były te niejawnie utworzone za pomocą ograniczenia UNIQUElub PRIMARY KEY. SQL Server 2014 wprowadził wbudowaną składnię indeksu dla podzbioru opcji dostępnych w CREATE INDEX. Zostało to przedłużone, aby umożliwić filtrowane warunki indeksu. Indeksy z INCLUDEkolumnami -d lub indeksami magazynu kolumn nadal nie są jednak możliwe do utworzenia w zmiennych tabeli.

2. Jeśli będziesz wielokrotnie dodawać i usuwać dużą liczbę wierszy z tabeli, użyj #temporarytabeli. To obsługuje TRUNCATE(co jest bardziej wydajne niż w DELETEprzypadku dużych tabel) i dodatkowo kolejne wstawki poTRUNCATE mogą mieć lepszą wydajność niż te po a, DELETE jak pokazano tutaj .

3. Jeśli będziesz usuwać lub aktualizować dużą liczbę wierszy, wówczas tabela tymczasowa może działać znacznie lepiej niż zmienna tabelowa - jeśli jest w stanie korzystać z udostępniania zestawu zestawów wierszy (na przykład patrz „Efekty udostępniania zestawu zestawów wierszy” poniżej).
4. Jeśli optymalny plan korzystający z tabeli będzie się różnić w zależności od danych, użyj #temporary tabeli. Wspiera to tworzenie statystyk, które pozwalają na dynamiczną rekompilację planu zgodnie z danymi (chociaż w buforowanych tabelach tymczasowych w procedurach przechowywanych zachowanie rekompilacji należy rozumieć osobno).
5. Jeśli optymalny plan zapytania przy użyciu tabeli prawdopodobnie nigdy się nie zmieni, możesz rozważyć zmienną tabelową, aby pominąć narzut związany z tworzeniem statystyk i ponownej kompilacji (prawdopodobnie wymagałoby to wskazówek, aby naprawić plan, który chcesz).
6. Jeśli źródło danych wstawionych do tabeli pochodzi z potencjalnie drogiego SELECT instrukcja, należy wziąć pod uwagę, że użycie zmiennej tabeli zablokuje taką możliwość przy użyciu planu równoległego.
7. Jeśli potrzebujesz danych w tabeli, aby przetrwać wycofywanie transakcji użytkownika zewnętrznego, użyj zmiennej tabeli. Możliwym przypadkiem użycia może być rejestrowanie postępu różnych kroków w długiej partii SQL.
8. Podczas korzystania z #temptabeli w obrębie użytkownika blokady transakcji mogą być przechowywane dłużej niż w przypadku zmiennych tabeli (potencjalnie do końca transakcji w porównaniu z końcem instrukcji w zależności od rodzaju blokady i poziomu izolacji), a także mogą zapobiec obcięciu tempdbdziennika transakcji do czasu transakcja użytkownika kończy się. Może to sprzyjać stosowaniu zmiennych tabeli.
9. W ramach przechowywanych procedur można buforować zarówno zmienne tabel, jak i tabele tymczasowe. Utrzymanie metadanych dla zmiennych tabeli buforowanej jest mniejsze niż w przypadku #temporarytabel. Bob Ward podkreśla w swojej tempdbprezentacji, że może to powodować dodatkowe spory o tabele systemowe w warunkach wysokiej współbieżności. Ponadto w przypadku niewielkich ilości danych może to mieć wymierną różnicę w wydajności .

Efekty udostępniania zestawu wierszy

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

Użyj zmiennej tabeli, jeśli dla bardzo małej ilości danych (tysiące bajtów)

Użyj tabeli tymczasowej dla dużej ilości danych

Inny sposób myślenia o tym: jeśli uważasz, że możesz skorzystać z indeksu, automatycznych statystyk lub dowolnego dobrodziejstwa optymalizatora SQL, oznacza to, że Twój zestaw danych jest prawdopodobnie zbyt duży dla zmiennej tabeli.

W moim przykładzie chciałem po prostu wstawić około 20 wierszy do formatu i zmodyfikować je jako grupę, zanim użyję ich do UPDATE / INSERT stałej tabeli. Zatem zmienna stołowa jest idealna.

Ale uruchamiam też SQL, aby zapełniać tysiące wierszy jednocześnie i zdecydowanie mogę powiedzieć, że tabele tymczasowe działają bardzo dobrze lepiej niż zmienne tabel.

Nie inaczej jest w przypadku, gdy CTE są przedmiotem zainteresowania z podobnej wielkości - jeśli dane w CTE są bardzo małe, uważam, że CTE działa tak dobrze lub lepiej niż to, co oferuje optymalizator, ale jeśli jest dość duży, to bardzo cię boli.

Moje rozumienie opiera się głównie na http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/ , który zawiera znacznie więcej szczegółów.

Microsoft mówi tutaj

Zmienne tabelowe nie mają statystyk dystrybucji, nie wyzwalają ponownej kompilacji. Dlatego w wielu przypadkach optymalizator zbuduje plan zapytań przy założeniu, że zmienna tabeli nie ma wierszy. Z tego powodu powinieneś być ostrożny przy używaniu zmiennej tabeli, jeśli spodziewasz się większej liczby wierszy (więcej niż 100). Tabele temperatur mogą być w tym przypadku lepszym rozwiązaniem.

Całkowicie zgadzam się z Abacusem (przepraszam - nie mam wystarczającej liczby punktów do skomentowania).

Pamiętaj również, że niekoniecznie sprowadza się to do liczby posiadanych rekordów, ale do ich rozmiaru .

Na przykład, czy wziąłeś pod uwagę różnicę wydajności między 1000 rekordów z 50 kolumnami w porównaniu do 100 000 rekordów z tylko 5 kolumnami w każdym?

Wreszcie, może przeszukujesz / przechowujesz więcej danych niż potrzebujesz? Oto dobry tekst na temat strategii optymalizacji SQL . Ogranicz ilość pobieranych danych, szczególnie jeśli nie używasz ich wszystkich (niektórzy programiści SQL są leniwi i po prostu wybierają wszystko, mimo że używają tylko niewielkiego podzbioru). Nie zapomnij, że analizator zapytań SQL może stać się twoim najlepszym przyjacielem.

Tabela zmiennych jest dostępna tylko dla bieżącej sesji, na przykład, jeśli potrzebujesz EXECinnej procedury składowanej w ramach bieżącej, musisz przekazać tabelę, ponieważ Table Valued Parameteri oczywiście wpłynie to na wydajność, z tabelami tymczasowymi możesz to zrobić tylko przekazanie nazwy tabeli tymczasowej

Aby przetestować tabelę tymczasową:

• Otwórz edytor zapytań w studiu zarządzania
• Utwórz tymczasowy stół
• Otwórz inne okno edytora zapytań
• Wybierz z tej tabeli „Dostępne”

Aby przetestować tabelę zmiennych:

• Otwórz edytor zapytań w studiu zarządzania
• Utwórz tabelę zmiennych
• Otwórz inne okno edytora zapytań
• Wybierz z tej tabeli „Niedostępne”

coś innego, czego doświadczyłem to: Jeśli twój schemat nie ma GRANTuprawnień do tworzenia tabel, użyj tabel zmiennych.

zapisując dane w zadeklarowanych tabelach declare @tbi po połączeniu z innymi tabelami, zdałem sobie sprawę, że czas odpowiedzi w porównaniu do tabel tymczasowych tempdb .. # tbjest znacznie dłuższy.

Kiedy dołączam do nich @tb, czas na zwrócenie wyniku jest znacznie dłuższy, w przeciwieństwie do #tm , zwrot jest niemal natychmiastowy.

Wykonałem testy z łączeniem 10.000 wierszy i łączeniem z 5 innymi tabelami