Jaki jest najszybszy sposób wstawiania dużej liczby wierszy?

Mam bazę danych, w której ładuję pliki do tabeli pomostowej. Z tej tabeli pomostowej mam 1-2 sprzężenia, aby rozwiązać niektóre klucze obce, a następnie wstawiam te wiersze do końcowej tabeli (która ma jedną partycję na miesiąc). Mam około 3,4 miliarda wierszy na trzy miesiące danych.

Jaki jest najszybszy sposób na przeniesienie tych rzędów do stołu finałowego? SSIS Data Flow Task (który używa widoku jako źródła i ma aktywne szybkie ładowanie) lub polecenie Insert INTO SELECT ....? Próbowałem zadania przepływu danych i mogę uzyskać około 1 miliarda wierszy w ciągu około 5 godzin (8 rdzeni / 192 GB pamięci RAM na serwerze), co wydaje mi się bardzo wolne.

Jedno wspólne podejście:

1. Wyłącz / upuść indeksy / ograniczenia w tabeli docelowej.
2. INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
3. Korzystając z JNK, możesz oczywiście zrobić powyższe partie nwierszy, co może zmniejszyć obciążenie dziennika transakcji, i oczywiście oznacza, że ​​jeśli jakaś partia się nie powiedzie, musisz zacząć od tej partii. Napisałem o tym na blogu (chociaż w odniesieniu do usuwania obowiązują te same podstawowe pojęcia) tutaj: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes
4. Ponownie włącz / ponownie utwórz indeksy / ograniczenia w tabeli docelowej (a być może możesz odroczyć niektóre z nich, jeśli nie są one konieczne do wszystkich operacji, a ważniejsze jest szybkie uzyskanie danych podstawowych online).

Jeśli twoje partycje są fizyczne, a nie tylko logiczne, możesz zyskać trochę czasu, mając różne procesy zapełniające jednocześnie różne partycje (oczywiście oznacza to, że nie możesz użyć TABLOCK/ TABLOCKX). Zakłada się, że źródło jest również odpowiednie do wybierania wielu procesów bez nakładania się / blokowania itp., A także spowalniania tej strony operacji (wskazówka: utwórz indeks klastrowany na źródle, który pasuje do schematu partycjonowania w miejscu docelowym).

Możesz także rozważyć coś o wiele bardziej prymitywnego, na przykład BCP OUT/BCP IN .

Nie wiem, czy skoczyłbym do SSIS, aby pomóc w tym. Prawdopodobnie są tam pewne usprawnienia, ale nie wiem, czy wysiłek uzasadnia oszczędności.

Patrząc na twój problem z perspektywy SSIS, wydaje mi się, że powodem, dla którego mogło to zająć tak długo, było to, że nie miałeś wsadu. Może to prowadzić do zbyt dużej liczby wierszy wypełniających potok SSIS i może w rezultacie pogorszyć wydajność SSIS. To, co musisz zrobić, to zmienić ustawienia wierszy na partię i ewentualnie maksymalny rozmiar zatwierdzenia wstawiania. Teraz to, co ustawisz, będzie zależeć od ilości pamięci dostępnej na serwerze SSIS? Jaka jest prędkość dysku twojej instancji SQL Server? Najlepszym sposobem na to jest test. Pozwala na przykład użyć 10.000. Spowoduje to wysłanie partii do serwera 10 000 naraz, dzięki czemu Twój rurociąg nie zostanie przepełniony i pomoże szybciej uruchomić ten proces. Te ustawienia są ustawione w miejscu docelowym OLEDB.

Jeśli jest to problem, możesz dodać zadanie SQL do wykonania przed i po, aby zrobić to, co sugeruje @AaronBertrand i usunąć / ponownie dodać wszelkie indeksy lub ograniczenia do tabeli.