WSTAW różnicę wydajności między tabelami tymczasowymi a zmienną tabeli

Mam następujący problem w SQL Server 2005: próba wstawienia niektórych wierszy do zmiennej tabeli zajmuje dużo czasu w porównaniu z tym samym wstawieniem przy użyciu tabeli tymczasowej.

To jest kod, który należy wstawić do zmiennej tabeli

DECLARE @Data TABLE(...)
INSERT INTO @DATA( ... )
SELECT ..
FROM ...

To jest kod do wstawienia do tabeli temp

CREATE #Data TABLE(...)
INSERT INTO #DATA( ... )
SELECT ..
FROM ...
DROP TABLE #Data

Tabela tymczasowa nie ma żadnych kluczy ani indeksów, część select jest taka sama między 2 zapytaniami, a liczba wyników zwróconych przez select wynosi ~ 10000 wierszy. Czas potrzebny do wykonania samego wyboru wynosi ~ 10 sekund.

Wykonanie wersji tabeli temp zajmuje do 10 sekund, musiałem zatrzymać wersję zmiennej tabeli po 5 minutach.

Muszę użyć zmiennej tabeli, ponieważ zapytanie jest częścią funkcji wartości tabeli, która nie pozwala na dostęp do tabeli tymczasowej.

Plan wykonania dla wersji zmiennej tabelowej

Plan wykonania dla wersji tabeli tymczasowej

sql-server-2005 performance temporary-tables

— Munissor
źródło

Odpowiedzi:

Oczywista różnica między tymi dwoma planami polega na tym, że szybki jest równoległy, a wolniejszy - szeregowy.

Jest to jedno z ograniczeń planów wstawianych do zmiennych tabeli. Jak wspomniano w komentarzach (i wydaje się, że miał pożądany efekt), możesz spróbować

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

aby sprawdzić, czy obejdzie to ograniczenie.

— Martin Smith
źródło

To była świetna sugestia, choć myślałem, że nie można użyć EXECtej funkcji .... chyba się myliłem

— Lamak

@Lamak - Doh! Nie możesz, więc to nie zadziała dla PO. Invalid use of a side-effecting operator 'INSERT EXEC' within a function.. Obejść praca może chociaż. OPENQUERY

— Martin Smith

Ach, dobrze wiedzieć, dziękuję za wyjaśnienie

— Lamak

Zgodnie z ogólną zasadą nie należy używać zmiennych tabeli, jeśli oczekuje się, że zostanie zwrócony zestaw dużych danych. W takim przypadku tabele temperatur są zwykle szybsze.

— HLGEM

@ Munissor, a następnie nie używaj funkcji cenionej w tabeli. Jeśli chcesz uzyskać lepszą poradę, opublikuj dokładnie to, co robisz.

— HLGEM

-1

Zmienne tabeli są czasami wolniejsze, ponieważ nie ma statystyk dotyczących zmiennych tabeli, dlatego optymalizator zawsze zakłada tylko jeden rekord.

Jednak nie mogę zagwarantować, że tak jest w tym przypadku, musisz rzucić okiem na informacje o „szacowanych wierszach” w planie zapytań dla zmiennej tabeli.

— yoel halb
źródło

Jak wpłynęłoby to na wstawkę do zmiennej tabeli?

— Martin Smith

Wydaje się, że tak właśnie jest, ponieważ widać, że nie tylko różnica między połączeniem równoległym i szeregowym, ale także między połączeniami pętli mieszającej i zagnieżdżonej, najwyraźniej optymalizator zakłada, że skoro zmienna tabelowa zapamiętuje jeden rekord, to wynik zapytania będzie również jednym rekordem, po raz kolejny jedynym sposobem na udowodnienie, że byłoby zobaczyć rzeczywiste statystyki dla każdej części zapytania, ale faktem jest, że wszystkie zapytania dotyczące zmiennych tabeli kończą się sprzężeniami pętli i przetwarzaniem szeregowym, więc wydaje mi się, że można tu podejrzewać

— yoel halb