Korzystając z SQL Server Business Intelligence Development Studio, robię dużo płaskich plików do docelowych przepływów danych OLE DB w celu zaimportowania danych do moich tabel SQL Server. W „trybie dostępu do danych” w edytorze docelowym OLE DB domyślnie jest to „tabela lub widok”, a nie „tabela lub widok - szybkie ładowanie”. Jaka jest różnica; jedyną zauważalną różnicą, którą dostrzegam, jest to, że szybkie ładowanie przenosi dane znacznie szybciej.
Tryb dostępu do danych SSIS Data Flow - jaki jest sens „tabeli lub widoku” w porównaniu z szybkim ładowaniem?
Odpowiedzi:
Tryby dostępu do danych OLE DB Destination Component występują w dwóch wersjach - szybkiej i nie-szybkiej.
Szybkie, „tabela lub widok - szybkie ładowanie” lub „zmienna nazwy tabeli lub widoku - szybkie ładowanie” oznacza, że dane będą ładowane w sposób oparty na zestawie.
Powolne - albo „tabela lub widok” albo „zmienna nazwy tabeli lub widoku” spowoduje, że SSIS wyda singletonowe instrukcje wstawiania do bazy danych. Jeśli ładujesz 10, 100, a może nawet 10000 wierszy, prawdopodobnie nie ma zauważalnej różnicy wydajności między tymi dwiema metodami. Jednak w pewnym momencie zamierzasz nasycić swoje wystąpienie programu SQL Server wszystkimi tymi paskudnie małymi żądaniami. Ponadto zamierzasz nadużyć cholery z dziennika transakcji.
Dlaczego miałbyś kiedykolwiek chcieć nie szybkich metod? Złe dane Gdybym wysłał 10000 wierszy danych, a 9999-ty rząd miał datę 29.02.2015, miałbyś 10 000 atomowych wstawek i zatwierdzeń / wycofań. Gdybym używał metody szybkiej, cała partia 10 000 wierszy albo zapisze wszystkie, albo nie zapisze żadnego z nich. A jeśli chcesz wiedzieć, które wiersze zostały pominięte, najniższy poziom szczegółowości, jaki będziesz mieć, to 10 000 wierszy.
Istnieją sposoby na jak najszybsze załadowanie jak największej ilości danych i nadal obsługę brudnych danych. Jest to kaskadowe podejście polegające na niepowodzeniu i wygląda mniej więcej tak
Chodzi o to, aby znaleźć odpowiedni rozmiar, aby wstawić jak najwięcej w jednym ujęciu, ale jeśli otrzymasz złe dane, spróbujesz ponownie zapisać dane w kolejno mniejszych partiach, aby dostać się do złych wierszy. Tutaj zacząłem o maksymalnym rozmiarze (wkładka popełnić FastLoadMaxInsertCommit) od 10000. Z usposobienia Błąd Row, zmieniam go Redirect Rowod Fail Component.
Następny cel jest taki sam jak powyżej, ale tutaj próbuję szybko załadować i zapisać go w partiach po 100 rzędów. Ponownie przetestuj lub udawaj, że masz rozsądny rozmiar. Spowoduje to wysłanie 100 partii 100 wierszy, ponieważ wiemy gdzieś tam, że istnieje co najmniej jeden wiersz, który naruszył ograniczenia integralności dla tabeli.
Następnie dodaję trzeci składnik do miksu, tym razem oszczędzam partiami 1. Lub możesz po prostu zmienić tryb dostępu do tabeli z dala od wersji Fast Load, ponieważ da to ten sam wynik. Będziemy zapisywać każdy wiersz osobno, co pozwoli nam zrobić „coś” z pojedynczymi złymi wierszami.
Wreszcie mam bezpieczne miejsce docelowe. Być może jest to ta sama tabela co miejsce docelowe, ale wszystkie kolumny są zadeklarowane jako nvarchar(4000) NULL. Wszystko, co skończy się przy tej tabeli, musi zostać zbadane i oczyszczone / odrzucone, lub cokolwiek to jest proces przetwarzania złych danych. Inni zrzucają do płaskiego pliku, ale tak naprawdę, cokolwiek ma sens, jak chcesz śledzić złe dane.
Szybkie ładowanie jest dobrze udokumentowane w opcjach SZYBKIE ŁADOWANIE
Zachowaj wartości tożsamości z importowanego pliku danych lub użyj unikalnych wartości przypisanych przez SQL Server.
Zachowaj wartość zerową podczas operacji ładowania luzem.
Sprawdź ograniczenia w tabeli docelowej lub widoku podczas operacji importu zbiorczego.
Uzyskaj blokadę na poziomie stołu na czas operacji ładowania luzem. Podaj liczbę wierszy w partii i rozmiar zatwierdzenia.
Jaka jest różnica; jedyną zauważalną różnicą, którą dostrzegam, jest to, że szybkie ładowanie przenosi dane znacznie szybciej.
Pod maską table or viewużyje indywidualnego polecenia SQL dla każdego wiersza do wstawienia vs table or view - with fast loadużyje polecenia WSTAWIANIE BULK.
Jeśli zobaczysz powyższe opcje, które są dostępne w BULK INSERT, np. number of rows in the batch= ROWS_PER_BATCHI commit size=BATCHSIZE
Kolejnym scenariuszem będzie ...
Domyślny maksymalny rozmiar zatwierdzenia wstawiania (2147483647) jest zbyt wysoki. Na przykład wstawiasz 500 000 wierszy, a z powodu naruszenia PK partia nie działa. W tym scenariuszu cała partia zakończy się niepowodzeniem, gdy użyjesz opcji FAST LOAD. Opis błędu również nie będzie dostępny.
W tym miejscu można ustawić table or viewjako docelowy wynik błędu. Tak więc z 500 KB używasz SZYBKIEGO OBCIĄŻENIA, zaczynając od rozmiaru zatwierdzenia wstawiania 5 KB. Jeśli 1 wiersz w tej partii nie powiedzie się, przekierujesz tę partię 5 KB do table or viewzaładowania - która używa wstawiania wiersz po wierszu TYLKO dla wierszy 5 KB i możesz również przekierować błąd table or viewdo płaskiego pliku .. tak, że jeśli dowolny wiersz zawiedzie partię jeśli 5K, będziesz w stanie wskazać, co spowodowało awarię.
Zaletą powyższej metody jest to, że jeśli żaden z wierszy nie ulegnie awarii, użyje WSTAWKI BULK (szybkie ładowanie) dla całej partii.
Miłośnik SSIS Billinkc odpowiedział na podobne pytanie dotyczące Stackoverflow .