O ile mogę powiedzieć, możesz zoptymalizować wkładkę zbiorczą w bardzo podobny sposób, jak w przypadku zwykłej wkładki. Zazwyczaj plan zapytań dla prostej wstawki nie jest zbyt pouczający, więc nie martw się o brak planu. Omówię kilka sposobów optymalizacji wkładki, ale większość z nich prawdopodobnie nie dotyczy wkładki określonej w pytaniu. Mogą być jednak pomocne, jeśli w przyszłości będziesz musiał załadować większe ilości danych.
1. Wstaw dane w kolejności kluczy klastrowych
SQL Server często sortuje dane przed wstawieniem ich do tabeli z indeksem klastrowym. W przypadku niektórych tabel i aplikacji można poprawić wydajność, sortując dane w pliku płaskim i informując SQL Server, że dane są sortowane według ORDERargumentu BULK INSERT:
ZAMÓWIENIE ({kolumna [ASC | DESC]} [, ... n])
Określa sposób sortowania danych w pliku danych. Wydajność importu zbiorczego poprawia się, jeśli importowane dane są sortowane zgodnie z indeksem klastrowym w tabeli, jeśli taki istnieje.
Ponieważ używasz IDENTITYkolumny jako klucza klastrowanego, nie musisz się tym martwić.
2. Użyj, TABLOCKjeśli to możliwe
Jeśli masz gwarancję, że tylko jedna sesja wstawi dane do tabeli, możesz podać TABLOCKargument BULK INSERT. Może to zmniejszyć rywalizację o blokadę i może prowadzić do minimalnego logowania w niektórych scenariuszach. Jednak wstawiasz do tabeli z indeksem klastrowym, który już zawiera dane, więc nie uzyskasz minimalnego rejestrowania bez flagi śledzenia 610, o której mowa w dalszej części tej odpowiedzi.
Jeśli TABLOCKnie jest to możliwe, ponieważ nie można zmienić kodu , nie wszystko stracone. Rozważ użycie sp_table_option:
EXEC [sys].[sp_tableoption]
@TableNamePattern = N'dbo.BulkLoadTable' ,
@OptionName = 'table lock on bulk load' ,
@OptionValue = 'ON'
Inną opcją jest włączenie flagi śledzenia 715 .
3. Użyj odpowiedniego rozmiaru partii
Czasami będziesz mógł dostroić wstawki, zmieniając rozmiar partii.
ROWS_PER_BATCH = wiersze_z_badą
Wskazuje przybliżoną liczbę wierszy danych w pliku danych.
Domyślnie wszystkie dane w pliku danych są wysyłane do serwera jako pojedyncza transakcja, a liczba wierszy w partii jest nieznana optymalizatorowi zapytań. Jeśli podasz ROWS_PER_BATCH (o wartości> 0), serwer użyje tej wartości do zoptymalizowania operacji importu zbiorczego. Wartość określona dla ROWS_PER_BATCH powinna być w przybliżeniu taka sama jak faktyczna liczba wierszy. Aby uzyskać informacje dotyczące zagadnień związanych z wydajnością, zobacz „Uwagi” w dalszej części tego tematu.
Oto cytat z późniejszego artykułu:
Jeśli liczba stron, które mają być opróżnione w jednej partii, przekracza wewnętrzny próg, może wystąpić pełne skanowanie puli buforów w celu zidentyfikowania stron, które mają zostać opróżnione po zatwierdzeniu partii. To pełne skanowanie może zaszkodzić wydajności importu zbiorczego. Prawdopodobny przypadek przekroczenia wewnętrznego progu występuje, gdy duża pula buforów jest połączona z wolnym podsystemem We / Wy. Aby uniknąć przepełnienia bufora na dużych komputerach, nie używaj wskazówki TABLOCK (która usunie optymalizacje zbiorcze) lub użyj mniejszej wielkości partii (która zachowuje optymalizacje zbiorcze).
Ponieważ komputery różnią się, zalecamy przetestowanie różnych wielkości partii przy ładowaniu danych, aby dowiedzieć się, co będzie dla Ciebie najlepsze.
Osobiście po prostu wstawiłbym wszystkie 695 wierszy w jednej partii. Strojenie wielkości partii może jednak mieć duże znaczenie przy wstawianiu dużej ilości danych.
4. Upewnij się, że potrzebujesz IDENTITYkolumny
Nie wiem nic o twoim modelu danych ani wymaganiach, ale nie wpadam w pułapkę dodawania IDENTITYkolumny do każdej tabeli. Aaron Bertrand ma artykuł na ten temat o nazwie Złe nawyki, które należy wykopać: umieszczając kolumnę TOŻSAMOŚCI na każdym stole . Dla jasności nie mówię, że powinieneś usunąć IDENTITYkolumnę z tej tabeli. Jeśli jednak stwierdzisz, że IDENTITYkolumna nie jest konieczna, usuń ją, co może poprawić wydajność wstawiania.
5. Wyłącz indeksy lub ograniczenia
Jeśli ładujesz dużą ilość danych do tabeli w porównaniu z tym, co już masz, może być szybsze wyłączenie indeksów lub ograniczeń przed ładowaniem i włączenie ich po ładowaniu. W przypadku dużych ilości danych zwykle bardziej nieefektywne jest utworzenie przez SQL Server indeksu naraz zamiast wczytywania danych do tabeli. Wygląda na to, że wstawiłeś 695 wierszy do tabeli z 11500 wierszami, więc nie poleciłbym tej techniki.
6. Rozważ TF 610
Flaga śledzenia 610 umożliwia minimalne logowanie w niektórych dodatkowych scenariuszach. W przypadku tabeli z IDENTITYkluczem klastrowym uzyskasz minimalne rejestrowanie dla nowych stron danych, o ile model odzyskiwania jest prosty lub zbiorczo. Uważam, że ta funkcja nie jest domyślnie włączona, ponieważ może obniżyć wydajność w niektórych systemach. Przed włączeniem tej flagi śledzenia należy dokładnie przetestować. Zalecanym odniesieniem Microsoft nadal wydaje się Przewodnik wydajności ładowania danych
Wpływ minimalnego rejestrowania we / wy pod flagą śledzenia 610
Po zatwierdzeniu transakcji ładowania masowego, która została minimalnie zarejestrowana, wszystkie załadowane strony muszą zostać opróżnione na dysk przed zakończeniem zatwierdzenia. Wszelkie opróżnione strony, które nie zostały przechwycone przez wcześniejszą operację punktu kontrolnego, mogą tworzyć wiele przypadkowych operacji we / wy. Porównaj to z w pełni zalogowaną operacją, która zamiast tego tworzy sekwencyjne operacje we / wy w zapisie dziennika i nie wymaga, aby załadowane strony były opróżniane na dysk w czasie zatwierdzania.
Jeśli twoim scenariuszem ładowania są małe operacje wstawiania na drzewach, które nie przekraczają granic punktów kontrolnych, a masz wolny system we / wy, użycie minimalnego rejestrowania może faktycznie spowolnić prędkości wstawiania.
O ile mogę stwierdzić, nie ma to nic wspólnego z flagą śledzenia 610, ale raczej z samym minimalnym logowaniem. Wierzę, że wcześniejszy cytat o ROWS_PER_BATCHtuningu dotyczył tej samej koncepcji.
Podsumowując, prawdopodobnie nie możesz wiele zrobić, aby dostroić swoje BULK INSERT. Nie martwiłbym się liczbą odczytów, którą zaobserwowałeś przy swojej wkładce. SQL Server zgłosi odczyt za każdym razem, gdy wstawisz dane. Rozważ następujące bardzo proste INSERT:
DROP TABLE IF EXISTS X_TABLE;
CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);
SET STATISTICS IO, TIME ON;
INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows
Wyjście z SET STATISTICS IO, TIME ON:
Tabela „X_TABLE”. Liczba skanów 0, logiczne odczyty 11428
Zgłoszono 11428 odczytów, ale nie jest to informacja możliwa do wykonania. Czasami liczbę zgłaszanych odczytów można zmniejszyć przez minimalne rejestrowanie, ale oczywiście różnicy nie można bezpośrednio przełożyć na wzrost wydajności.