Za chwilę będę musiał przepisać jakiś dość stary kod BULK INSERT
poleceniem SQL Servera bo schemat się zmienił i przyszło mi do głowy, że może powinienem pomyśleć o przejściu na procedurę składowaną z TVP, ale zastanawiam się jaki efekt to może mieć na wydajność.
Kilka podstawowych informacji, które mogą pomóc wyjaśnić, dlaczego zadaję to pytanie:
Dane faktycznie są dostarczane za pośrednictwem usługi internetowej. Usługa sieciowa zapisuje plik tekstowy w folderze udostępnionym na serwerze bazy danych, który z kolei wykonuje operację
BULK INSERT
. Ten proces został pierwotnie zaimplementowany w SQL Server 2000 i wtedy tak naprawdę nie było innej alternatywy niż wrzucenieINSERT
do serwera kilkuset instrukcji, co w rzeczywistości było pierwotnym procesem i spowodowało katastrofę wydajności.Dane są zbiorczo wstawiane do stałej tabeli pomostowej, a następnie łączone w znacznie większą tabelę (po czym są usuwane z tabeli pomostowej).
Ilość danych do wstawienia jest „duża”, ale nie „ogromna” - zwykle kilkaset wierszy, w rzadkich przypadkach może nawet 5-10 tys. Dlatego mam przeczucie,
BULK INSERT
że niezalogowana operacja nie zrobi tak dużej różnicy (ale oczywiście nie jestem pewien, stąd pytanie).Wprowadzanie jest w rzeczywistości częścią znacznie większego procesu wsadowego opartego na potokach i musi następować wiele razy z rzędu; dlatego wydajność jest krytyczna.
Powody, dla których chciałbym zastąpić BULK INSERT
TVP to:
Pisanie pliku tekstowego przez NetBIOS prawdopodobnie już kosztuje trochę czasu i jest dość makabryczne z architektonicznego punktu widzenia.
Uważam, że stół pomostowy można (i należy) wyeliminować. Głównym powodem jest to, że wstawione dane muszą zostać użyte do kilku innych aktualizacji w tym samym czasie wstawiania, a próba aktualizacji z ogromnej tabeli produkcyjnej jest o wiele bardziej kosztowna niż użycie prawie pustego przemieszczania stół. W przypadku TVP parametrem w zasadzie jest stół pomostowy, mogę z nim zrobić wszystko, co chcę, przed / po głównej wkładce.
Mogłem prawie całkowicie pozbyć się sprawdzania dupleksu, czyszczenia kodu i całego narzutu związanego z wstawianiem zbiorczym.
Nie musisz martwić się rywalizacją o blokady w tabeli pomostowej lub tempdb, jeśli serwer pobierze kilka takich transakcji naraz (staramy się tego uniknąć, ale zdarza się).
Oczywiście zamierzam to sprofilować przed wprowadzeniem czegokolwiek do produkcji, ale pomyślałem, że dobrym pomysłem byłoby najpierw zapytać o to, zanim spędzę cały ten czas, sprawdzić, czy ktoś ma jakieś poważne ostrzeżenia dotyczące używania TVP do tego celu.
Zatem - jaki jest werdykt dla każdego, kto jest wystarczająco wygodny w SQL Server 2008, aby spróbować lub przynajmniej zbadać tę sprawę? W przypadku wstawek, powiedzmy kilkuset do kilku tysięcy rzędów, zdarzających się dość często, czy TVP tną musztardę? Czy istnieje znacząca różnica w wydajności w porównaniu z wkładkami zbiorczymi?
Aktualizacja: teraz z 92% mniej znaków zapytania!
(Inna nazwa: Wyniki testu)
Końcowy wynik jest teraz w produkcji po 36-stopniowym procesie wdrażania. Oba rozwiązania zostały dokładnie przetestowane:
- Wyrywanie kodu folderu współdzielonego i
SqlBulkCopy
bezpośrednie używanie klasy; - Przejście do procedury składowanej z TVP.
Aby czytelnicy mogli zorientować się, co dokładnie zostało przetestowane, aby rozwiać wszelkie wątpliwości co do wiarygodności tych danych, oto bardziej szczegółowe wyjaśnienie tego, co faktycznie robi ten proces importu :
Zacznij od tymczasowej sekwencji danych, która zwykle składa się z około 20-50 punktów danych (chociaż czasami może dochodzić do kilkuset);
Wykonaj na nim całą masę szalonych procesów, które są w większości niezależne od bazy danych. Ten proces jest równoległy, więc około 8-10 sekwencji w (1) jest przetwarzanych w tym samym czasie. Każdy proces równoległy generuje 3 dodatkowe sekwencje.
Weź wszystkie 3 sekwencje i oryginalną sekwencję i połącz je w partię.
Połącz partie ze wszystkich 8-10 zakończonych już zadań przetwarzania w jedną dużą super partię.
Zaimportuj go, korzystając ze
BULK INSERT
strategii (patrz następny krok) lub strategii TVP (przejdź do kroku 8).Użyj
SqlBulkCopy
klasy, aby zrzucić całą super partię do 4 stałych tabel pomostowych.Uruchom procedurę składowaną, która (a) wykonuje kilka kroków agregacji na 2 tabelach, w tym kilka
JOIN
warunków, a następnie (b) wykonuje operacjęMERGE
na 6 tabelach produkcyjnych, używając zarówno danych zagregowanych, jak i niezagregowanych. (Skończone)LUB
Wygeneruj 4
DataTable
obiekty zawierające dane do scalenia; 3 z nich zawierają typy CLR, które niestety nie są odpowiednio obsługiwane przez ADO.NET TVP, więc muszą być wstawione jako reprezentacje ciągów, co nieco obniża wydajność.Przekaż TVP do procedury składowanej, która zasadniczo przetwarza to samo co (7), ale bezpośrednio z otrzymanymi tabelami. (Skończone)
Wyniki były dość zbliżone, ale podejście TVP ostatecznie wypadło średnio lepiej, nawet jeśli dane nieznacznie przekraczały 1000 wierszy.
Zwróć uwagę, że ten proces importu jest uruchamiany kolejno wiele tysięcy razy, więc bardzo łatwo było uzyskać średni czas po prostu zliczając, ile godzin (tak, godzin) zajęło zakończenie wszystkich połączeń.
Początkowo średnie scalanie trwało prawie dokładnie 8 sekund (przy normalnym obciążeniu). Usunięcie kludge NetBIOS i przejście na SqlBulkCopy
skróciło czas do prawie dokładnie 7 sekund. Przejście na TVP dodatkowo skróciło czas do 5,2 sekundy na partię. To 35% wzrost przepustowości procesu, którego czas pracy mierzy się w godzinach - więc wcale nie jest zły. To także ~ 25% poprawa w stosunku do SqlBulkCopy
.
Jestem właściwie przekonany, że prawdziwa poprawa była znacznie większa. Podczas testów okazało się, że ostateczne scalenie nie jest już ścieżką krytyczną; zamiast tego usługa sieciowa, która zajmowała się całym przetwarzaniem danych, zaczynała się rozpadać pod liczbą napływających żądań. Ani procesor, ani baza danych we / wy nie były tak naprawdę wyczerpane i nie było znaczącej aktywności blokującej. W niektórych przypadkach widzieliśmy przerwę kilku sekund bezczynności między kolejnymi połączeniami. Wystąpiła niewielka przerwa, ale znacznie mniejsza (około pół sekundy) podczas używania SqlBulkCopy
. Ale przypuszczam, że stanie się to opowieścią na inny dzień.
Wniosek: Parametry wyceniane w tabeli naprawdę działają lepiej niż BULK INSERT
operacje dla złożonych procesów importu i transformacji działających na średnich zbiorach danych.
Chciałbym dodać jeszcze jedną kwestię, żeby złagodzić obawy ze strony ludzi, którzy są za stołami pro-staging. W pewnym sensie cała ta usługa jest jednym gigantycznym procesem przejściowym. Każdy etap procesu jest poddawany szczegółowej kontroli, więc nie potrzebujemy tabeli pomostowej, aby określić, dlaczego jakieś konkretne scalanie nie powiodło się (chociaż w praktyce prawie nigdy się to nie zdarza). Wszystko, co musimy zrobić, to ustawić flagę debugowania w usłudze, która zepsuje debuger lub zrzuci swoje dane do pliku zamiast bazy danych.
Innymi słowy, mamy już więcej niż wystarczający wgląd w proces i nie potrzebujemy bezpieczeństwa tabeli pomostowej; jedynym powodem, dla którego mieliśmy stół pomostowy na pierwszym miejscu, było uniknięcie rzucania się na wszystkie stwierdzenia INSERT
i UPDATE
, których musielibyśmy użyć w przeciwnym razie. W pierwotnym procesie dane przemieszczania i tak znajdowały się w tabeli pomostowej tylko przez ułamki sekundy, więc nie dodały żadnej wartości pod względem konserwacji / utrzymania.
Pamiętaj również, że nie zastąpiliśmy każdej BULK INSERT
operacji TVP. Kilka operacji, które zajmują się większymi ilościami danych i / lub nie muszą robić nic specjalnego z danymi poza wyrzuceniem ich do bazy danych, nadal są używane SqlBulkCopy
. Nie sugeruję, że TVP to panaceum na wydajność, tylko że udało im się SqlBulkCopy
to w tym konkretnym przypadku, obejmującym kilka transformacji między początkową inscenizacją a ostatecznym połączeniem.
Więc masz to. Wskazuje na TToni w celu znalezienia najbardziej odpowiedniego linku, ale doceniam również inne odpowiedzi. Dzięki jeszcze raz!