Wydajność serwera SQL Wydajność SELECT INTO vs INSERT INTO dla tabel tymczasowych
By: Jignesh Raiyani | Updated: 2017-01-04 | Komentarze (9) | Powiązane: More > T-SQL
Darmowe webinarium MSSQLTips: Development Best Practices for SQL Server
Weź udział w tym webinarium, aby dowiedzieć się o najlepszych praktykach rozwoju dla SQL Server. Andy Warren podzieli się swoim wieloletnim doświadczeniem, aby dać kilka wskazówek na temat tego, co najlepiej sprawdziło się w jego przypadku i jak możesz wykorzystać część tej wiedzy.
Problem
Serwer SQL zawiera kod SELECT…INTO i INSERT…INTO do wstawiania danych do tabel tymczasowych. Czy możesz podać kilka próbek i wskazać, która opcja działa lepiej?
Rozwiązanie
SerwerSQL zawiera dwie opcje dla tabel tymczasowych:
- Lokalna tabela tymczasowa
- Globalna tabela tymczasowa
Potrzebujesz dodać prefiks '#' dla lokalnych tabel tymczasowych oraz '##' dla globalnych tabel tymczasowych. Obiekty te zostaną utworzone w systemowej bazie danych TempDB. Tabele tymczasowe są przechowywane w bazie danych TempDB z unikalną nazwą obiektu i tworzone na poziomie połączenia lub sesji. Tabele te są widoczne i dostępne tylko w ramach danej sesji. Tabelę tymczasową możemy usunąć za pomocą polecenia DROP TABLE lub zostanie ona usunięta automatycznie po rozłączeniu sesji. Dodatkowo SQL Server utrzymuje statystyki tabel tymczasowych.
Tworzenie i usuwanie tabel tymczasowych wymaga dostępu i modyfikacji stron mapy alokacji TempDB (IAM, SGAM i PES). Tabele tymczasowe są buforowane w puli buforowej w porównaniu do tabel stałych opartych na dysku. Przy buforowaniu tabel tymczasowych, SQL Server nie usuwa fizycznie tabeli, ale ją obcina i zachowuje strony IAM i danych. Gdy tabela zostanie utworzona później, SQL Server wykorzysta wcześniejsze strony, co zredukuje liczbę wymaganych modyfikacji stron.
Tymczasowe tabele mogą być tworzone na dwa sposoby:
- CREATE TABLE
- SELECT INTO
Generalnie rzecz biorąc, wydajność obu opcji jest podobna dla małej ilości danych. Dane są szybko wstawiane do tabeli tymczasowej, ale jeśli ilość danych jest duża to możemy doświadczyć słabej wydajności zapytań. Dzieje się tak zazwyczaj w przypadku tabel tymczasowych, gdy wstawiamy dużą liczbę wierszy.
SerwerSQL INSERT INTO Przykład
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SerwerSQL w czasie parsowania i kompilacji:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Gdy jako parametr wejściowy występuje duża ilość wartości rozdzielanych przecinkami,kod może być opóźniony o kilka sekund podczas wstawiania danych do kuszty.
Przykład SELECT INTO serwera MySQL
Jako alternatywne rozwiązanie możemy użyć polecenia SELECT…INTO, które generalnie działa lepiej niż polecenie INSERT…SELECT.
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
Czas parsowania i kompilacji serweraSQL:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SerwerSQL INSERT….SELECT vs. SELECT…INTO z większym zestawem danych
Powyższe przykłady są bardzo małe, więc użyjmy poniższego przykładu, aby zobaczyć jak toperformuje się z dużą ilością danych. Ten test będzie miał ponad milion wierszy.
SerwerSQL INSERT… SELECT
CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee
Czas parsowania i kompilacji serweraSQL:
SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)
SerwerSQL SELECT…INTO
SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee
Czas parsowania i kompilacji serweraSQL:
SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)
Wyniki i analiza
Jak widzimy SELECT…INTO było znacznie szybsze 489ms w porównaniu do 3241ms.
Komenda INSERT…INTO będzie ponownie wykorzystywać strony danych, które są tworzone w cache dla operacjiinsert/update/delete. Polecenie SELECT…INTO utworzy nowe strony do tworzenia tabeli, podobnie jak w przypadku regulartables i fizycznie je usunie, gdy tymczasowa tabela zostanie usunięta.
Następne kroki
- Pamiętaj o tych rozważaniach dotyczących wydajności, gdy będziesz rozwijać swój kod lub będziesz musiał dostroić kod, który nie działa optymalnie.
- Testuj dodatkowe scenariusze w swoim środowisku, aby określić, która technika kodowania będzie działać najlepiej.
- Zawsze miej otwarty umysł na różne techniki kodowania, testuj każdą technikę i pozwól, aby wydajność dyktowała odpowiedź.
Ostatnia aktualizacja: 2017-01-04
O autorze autor
Jignesh Raiyani jest SQL Server Developer/DBA z doświadczeniem w projektowaniu i rozwoju T-SQL procedur i strojenia wydajności zapytań.Zobacz wszystkie moje wskazówki
- Więcej wskazówek dla programistów baz danych…
.