Indeks na tabeli tymczasowej

Indeks na tabeli tymczasowej to struktura przyspieszająca dostęp do danych przechowywanych chwilowo w bazie tempdb.

Rola indeksu na tabeli tymczasowej w SQL Server

Indeks na tabeli tymczasowej jest strukturą przechowywaną w bazie tempdb, która porządkuje kopie wartości z wybranych kolumn i pozwala aparatowi wykonawczemu SQL Server szybciej wyszukiwać, łączyć albo porządkować dane. Tabela tymczasowa tworzona za pomocą składni CREATE TABLE #Nazwa otrzymuje nazwę lokalną, widoczną tylko w bieżącej sesji. Jej indeks nie jest więc stałym elementem docelowej bazy aplikacji, lecz obiektem związanym z czasem istnienia tej tabeli.

Indeks może być klastrowany albo nieklastrowany. Indeks klastrowany określa fizyczne uporządkowanie wierszy tabeli, natomiast indeks nieklastrowany zawiera osobną strukturę z kluczem indeksu oraz odwołaniem do wiersza. Tabela tymczasowa może mieć również klucz główny albo ograniczenie UNIQUE; w SQL Server takie ograniczenia zwykle powodują utworzenie odpowiedniego indeksu. Dobór rodzaju indeksu powinien wynikać z zapytań wykonywanych na konkretnej tabeli, a nie z samego faktu, że tabela znajduje się w tempdb.

Tworzenie indeksu przed załadowaniem danych

Indeks na tabeli tymczasowej można zdefiniować już podczas jej tworzenia albo dodać później poleceniem CREATE INDEX. Przykładowa tabela #Sprzedaz może zawierać kolumny ProduktId, DataSprzedazy i Kwota. Jeżeli dalsze zapytania często filtrują dane po ProduktId, można utworzyć nieklastrowany indeks na tej kolumnie. SQL Server będzie wtedy mógł rozważyć użycie operacji wyszukiwania po indeksie zamiast pełnego skanowania wszystkich wierszy tabeli.

Utworzenie indeksu przed wstawieniem dużej liczby rekordów upraszcza sytuację, w której tabela jest wielokrotnie zasilana i od razu odczytywana. Każda operacja INSERT musi jednak aktualizować także strukturę indeksu, dlatego indeks zwiększa koszt ładowania oraz zajmuje dodatkowe miejsce w tempdb. Przy małej tabeli koszt przeglądania indeksu, odczytania stron danych i wykonania dodatkowych operacji może być większy niż koszt prostego skanu. Optymalizator SQL Server wybiera plan na podstawie szacowanej liczby wierszy, predykatów i dostępnych statystyk.

Indeks klastrowany, nieklastrowany i kolumny dołączone

Indeks klastrowany może być użyteczny, gdy tabela tymczasowa jest później przeszukiwana po kluczu zakresowym, na przykład po identyfikatorze albo dacie, oraz gdy wynik wymaga uporządkowania zgodnego z kluczem. Ponieważ tabela może mieć tylko jeden indeks klastrowany, jego klucz trzeba dobrać z uwzględnieniem najważniejszych zapytań. W przypadku tabeli tymczasowej z kluczem głównym SQL Server może utworzyć indeks klastrowany, jeśli nie określono innego typu indeksu.

Indeks nieklastrowany może obejmować kilka kolumn. Ich kolejność ma znaczenie: pierwsza kolumna klucza jest najważniejsza dla wyszukiwania z predykatem zgodnym z początkiem klucza. Na przykład indeks na (ProduktId, DataSprzedazy) lepiej pasuje do filtrowania najpierw po produkcie, a następnie po zakresie dat niż do zapytania ograniczonego wyłącznie zakresem dat. Kolumny potrzebne tylko do zwrócenia wyniku można dodać jako INCLUDE, aby ograniczyć konieczność odczytu podstawowej tabeli. Nie stają się one jednak częścią klucza używanego do porządkowania indeksu.

Indeks może również wymuszać unikatowość, ale nie należy używać właściwości UNIQUE wyłącznie w celu przyspieszenia zapytania. Jeżeli dane zawierają powtarzające się wartości, utworzenie takiego indeksu zakończy się błędem. W tabelach tymczasowych trzeba też uwzględnić, że wielokrotne tworzenie i usuwanie obiektów oraz indeksów zwiększa aktywność w tempdb. Nazwy lokalnych tabel tymczasowych są obsługiwane przez SQL Server w sposób pozwalający na równoczesne używanie tabel o tej samej nazwie przez różne sesje.

Wpływ statystyk i planu wykonania

Sam fakt istnienia indeksu nie gwarantuje jego wykorzystania. SQL Server może wybrać skan tabeli, jeżeli uzna go za tańszy od wyszukiwania indeksowego, szczególnie przy niewielkiej liczbie danych albo gdy zapytanie zwraca znaczną część tabeli. Istotne są także predykaty nienadające się do efektywnego wyszukiwania, niezgodności typów danych oraz funkcje zastosowane bezpośrednio na indeksowanej kolumnie. Weryfikacja powinna obejmować rzeczywisty plan wykonania, operator odczytu oraz liczbę logicznych odczytów.

SQL Server może tworzyć i aktualizować statystyki związane z tabelą tymczasową, co pomaga optymalizatorowi oszacować rozkład danych. Jeżeli tabela jest zapełniana etapami, a następnie używana w złożonych połączeniach, plan przygotowany przed znaczną zmianą liczby wierszy może być mniej trafny. W analizie warto porównać wykonanie zapytania z indeksem i bez niego, mierząc czas, logiczne odczyty oraz koszt ładowania danych. Należy również sprawdzić, czy zapytanie nie powoduje blokowania, nadmiernego wzrostu tempdb albo niepotrzebnej przebudowy indeksu.

Diagnostyka problemu z tabelą tymczasową obejmuje sprawdzenie definicji tabeli, kluczy indeksów, typów kolumn oraz kolejności operacji. Pomocne są polecenia sys.indexes i sys.index_columns, używane do odczytu metadanych indeksów dostępnych w bieżącym kontekście. W przypadku długiego zapytania trzeba dodatkowo ustalić, czy opóźnienie wynika z braku indeksu, nieaktualnych szacunków, kosztu zapisów do tempdb, czy z samego zapytania. Dopiero takie porównanie pozwala ocenić, czy indeks rzeczywiście poprawia przepływ danych.