Jeśli chodzi o metodologię, wydaje mi się, że szczekasz na niewłaściwe drzewko ;-).
Co wiemy:
Najpierw skonsolidujmy i sprawdźmy, co wiemy o sytuacji:
Co możemy założyć:
Następnie możemy spojrzeć na wszystkie te punkty danych razem, aby zobaczyć, czy możemy zsyntetyzować dodatkowe szczegóły, które pomogą nam znaleźć jedno lub więcej szyjek butelek, i albo wskażemy rozwiązanie, albo przynajmniej wykluczymy niektóre możliwe rozwiązania.
Obecny kierunek myślenia w komentarzach jest taki, że głównym problemem jest transfer danych między SQL Server i Excel. Czy to naprawdę tak jest? Jeśli procedura składowana jest wywoływana dla każdego z 800 000 wierszy i zajmuje 50 ms na każde wywołanie (tj. Dla każdego wiersza), daje to 40 000 sekund (nie ms). A to odpowiada 666 minutom (hhmm ;-), czyli nieco ponad 11 godzin. Jednak cały proces miał zająć tylko 7 godzin. Łącznie mamy już 4 godziny, a nawet dodaliśmy czas na wykonanie obliczeń lub zapisanie wyników z powrotem na SQL Server. Więc czegoś tu nie ma.
Patrząc na definicję procedury składowanej, istnieje tylko parametr wejściowy dla @FileID; nie ma żadnego filtra @RowID. Podejrzewam więc, że dzieje się jeden z następujących dwóch scenariuszy:
- Ta procedura składowana nie jest faktycznie wywoływana dla każdego wiersza, ale zamiast tego dla każdego
@FileID, który wydaje się obejmować około 4000 wierszy. Jeśli podane 4000 wierszy zwróconych jest dość spójną kwotą, to tylko 200 z nich grupuje się w 800 000 wierszy. A 200 egzekucji po 50 ms to tylko 10 sekund z 7 godzin.
- Jeśli ta procedura przechowywana faktycznie zostanie wywołana dla każdego wiersza, to nie przy pierwszym
@FileIDprzekazaniu nowego zajmie trochę więcej czasu, aby pobrać nowe wiersze do puli buforów, ale wtedy kolejne 3999 wykonań zwykle powróci szybciej, ponieważ są już w pamięci podręcznej, prawda?
Myślę, że skupienie się na tej procedurze przechowywanej „filtru” lub jakimkolwiek transferze danych z SQL Server do Excela to czerwony śledź .
W tej chwili uważam, że najbardziej odpowiednie wskaźniki słabych wyników to:
- Istnieje 800 000 wierszy
- Operacja działa w jednym rzędzie na raz
- Dane są zapisywane z powrotem do SQL Server, stąd „[używa] wartości z niektórych kolumn do manipulowania innymi kolumnami ” [moja em phas to ;-)]
Podejrzewam, że:
- chociaż istnieje pole do ulepszeń w zakresie pobierania i obliczania danych, ich ulepszenie nie oznaczałoby znacznego skrócenia czasu przetwarzania.
- głównym wąskim gardłem jest wydawanie 800 000 oddzielnych
UPDATEwyciągów, co stanowi 800 000 oddzielnych transakcji.
Moja rekomendacja (na podstawie obecnie dostępnych informacji):
Twoim największym ulepszeniem byłoby zaktualizowanie wielu wierszy jednocześnie (tj. W jednej transakcji). Powinieneś zaktualizować swój proces, aby działał pod względem każdego FileIDzamiast każdego RowID. Więc:
- wczytaj wszystkie 4000 wierszy danego elementu
FileIDdo tablicy
- tablica powinna zawierać elementy reprezentujące manipulowane pola
- przeglądaj tablicę, przetwarzając każdy wiersz tak, jak obecnie
- po
FileIDobliczeniu wszystkich wierszy w tablicy (tj. dla tego konkretnego ):
- rozpocząć transakcję
- wywołać każdą aktualizację dla każdego
RowID
- jeśli nie ma błędów, zatwierdz transakcję
- jeśli wystąpił błąd, wycofaj i odpowiednio postępuj
Jeśli indeks klastrowany nie jest jeszcze zdefiniowany jako (FileID, RowID), należy to rozważyć (jak sugerował @MikaelEriksson w komentarzu do pytania). Nie pomoże tym pojedynczym AKTUALIZACJOM, ale przynajmniej nieznacznie poprawi operacje agregujące, takie jak to, co robisz w tej „filtrowanej” procedurze przechowywanej, ponieważ wszystkie są oparte FileID.
Powinieneś rozważyć przeniesienie logiki do skompilowanego języka. Sugerowałbym utworzenie aplikacji .NET WinForms lub nawet aplikacji konsolowej. Wolę aplikację konsolową, ponieważ można ją łatwo zaplanować za pomocą agenta SQL lub zaplanowanych zadań systemu Windows. Nie powinno mieć znaczenia, czy odbywa się to w VB.NET, czy w C #. VB.NET może być bardziej naturalny dla twojego programisty, ale nadal będzie trochę krzywej uczenia się.
W tej chwili nie widzę powodu, aby przejść do SQLCLR. Jeśli algorytm zmienia się często, denerwujące byłoby zmuszanie do ponownego wdrażania zestawu przez cały czas. Odbudowanie aplikacji konsoli i umieszczenie pliku .exe w odpowiednim folderze współdzielonym w sieci, tak aby po prostu uruchomić ten sam program i zdarza się, że zawsze jest aktualny, powinno być dość łatwe.
Nie sądzę, aby przeniesienie przetwarzania w pełni do T-SQL pomogłoby, jeśli podejrzewam, że problem jest taki, a ty wykonujesz jedną aktualizację na raz.
Jeśli przetwarzanie zostanie przeniesione do platformy .NET, można następnie użyć parametrów wycenionych w tabeli (TVP), aby przekazać tablicę do procedury UPDATEskładowanej, która wywołałaby wywołanie JOIN do zmiennej tabeli TVP, a zatem jest pojedynczą transakcją . TVP powinien być szybszy niż 4000 INSERTpogrupowane w jedną transakcję. Ale zysk wynikający z używania TVP przez ponad 4000 INSERTs w 1 transakcji prawdopodobnie nie będzie tak znaczący, jak poprawa widoczna przy przejściu z 800 000 oddzielnych transakcji do tylko 200 transakcji po 4000 wierszy każda.
Opcja TVP nie jest natywnie dostępna dla strony VBA, ale ktoś wymyślił obejście, które może być warte przetestowania:
Jak poprawić wydajność bazy danych, przechodząc z VBA do SQL Server 2008 R2?
JEŚLI filtr proc używa tylko FileIDw WHEREklauzuli i JEŻELI ten proc jest tak naprawdę wywoływany dla każdego wiersza, możesz zaoszczędzić trochę czasu przetwarzania, buforując wyniki pierwszego uruchomienia i wykorzystując je dla pozostałych wierszy FileID, dobrze?
Po uzyskaniu przetwarzanie odbywa się za fileid , wtedy możemy zacząć mówić o przetwarzaniu równoległym. Ale w tym momencie może to nie być konieczne :). Biorąc pod uwagę, że masz do czynienia z 3 dość dużymi, nie idealnymi częściami: transakcjami Excel, VBA i 800 tys., Każda rozmowa o SSIS lub równoległoboki lub kto-co-wie, to przedwczesna optymalizacja / typ wózka przed koniem . Jeśli uda nam się zmniejszyć ten 7-godzinny proces do 10 minut lub krócej, czy nadal zastanawiasz się nad dodatkowymi sposobami na przyspieszenie? Czy masz na myśli docelowy czas realizacji? Należy pamiętać, że po zakończeniu przetwarzania dla jednego identyfikatora pliku podstawa: jeśli masz aplikację konsoli VB.NET (tj. wiersza polecenia .EXE), nic nie powstrzyma cię przed uruchomieniem kilku tych identyfikatorów plików na raz :), czy to za pośrednictwem kroku CmdExec agenta SQL, czy Zaplanowanych zadań systemu Windows, itp.
I zawsze możesz zastosować podejście „etapowe” i wprowadzić kilka ulepszeń naraz. Na przykład, począwszy od robienia aktualizacji dla, FileIDa więc przy użyciu jednej transakcji dla tej grupy. Następnie sprawdź, czy możesz uruchomić TVP. Następnie zapoznaj się z pobieraniem tego kodu i przenoszeniem go do VB.NET (a TVP działają w .NET, więc ładnie się ładuje).
Czego nie wiemy, co może pomóc:
- Czy procedura przechowywana „filtrowania” jest uruchamiana dla RowID lub FileID ? Czy w ogóle mamy pełną definicję tej procedury składowanej?
- Pełny schemat tabeli. Jak szeroki jest ten stół? Ile jest pól o zmiennej długości? Ile pól ma wartość NULLable? Jeśli jakieś są NULLable, ile zawiera NULL?
- Indeksy dla tej tabeli. Czy jest podzielony na partycje? Czy używana jest kompresja ROW lub PAGE?
- Jak duża jest ta tabela pod względem MB / GB?
- Jak obsługiwana jest konserwacja indeksu dla tej tabeli? Jak podzielone są indeksy? Jak aktualne są statystyki?
- Czy jakieś inne procesy zapisują do tej tabeli podczas trwającego 7 godzin procesu? Możliwe źródło niezgody.
- Czy jakieś inne procesy zostały odczytane z tej tabeli podczas trwającego 7 godzin procesu? Możliwe źródło niezgody.
AKTUALIZACJA 1:
** Wydaje się, że istnieje pewne zamieszanie dotyczące tego, co VBA (Visual Basic for Applications) i co można z nim zrobić, więc to po prostu upewnienie się, że wszyscy jesteśmy na tej samej stronie internetowej:
AKTUALIZACJA 2:
Jeszcze jedna rzecz do rozważenia: w jaki sposób obsługiwane są połączenia? Czy kod VBA otwiera i zamyka połączenie dla każdej operacji, czy też otwiera połączenie na początku procesu i zamyka pod koniec procesu (tj. 7 godzin później)? Nawet przy pulowaniu połączeń (które domyślnie powinny być włączone dla ADO), nadal powinno istnieć spory wpływ na jednokrotne otwarcie i zamknięcie w przeciwieństwie do otwierania i zamykania 800, 200 lub 1 600 000 razy. Wartości te opierają się na co najmniej 800 000 AKTUALIZACJACH plus 200 lub 800k EXEC (w zależności od tego, jak często faktycznie wykonywana jest procedura przechowywana filtru).
Problem zbyt wielu połączeń jest automatycznie łagodzony przez zalecenie, które przedstawiłem powyżej. Tworząc transakcję i wykonując wszystkie AKTUALIZACJE w ramach tej transakcji, utrzymasz to połączenie otwarte i ponownie wykorzystasz je dla każdej z nich UPDATE. To, czy połączenie jest utrzymywane otwarte od pierwszego wywołania, aby uzyskać 4000 wierszy dla określonego FileID, czy zamknięte po tej operacji „get” i ponownie otwarte dla aktualizacji, ma o wiele mniejszy wpływ, ponieważ teraz mówimy o różnicy Łącznie 200 lub 400 połączeń w całym procesie.
AKTUALIZACJA 3:
Zrobiłem kilka szybkich testów. Należy pamiętać, że jest to test na małą skalę, a nie dokładnie ta sama operacja (czysty INSERT vs EXEC + UPDATE). Różnice w czasie związane ze sposobem obsługi połączeń i transakcji są jednak nadal istotne, dlatego informacje można ekstrapolować, aby mieć tutaj stosunkowo podobny wpływ.
Parametry testu:
- SQL Server 2012 Developer Edition (64-bit), SP2
Stół:
CREATE TABLE dbo.ManyInserts
(
RowID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
InsertTime DATETIME NOT NULL DEFAULT (GETDATE()),
SomeValue BIGINT NULL
);
Operacja:
INSERT INTO dbo.ManyInserts (SomeValue) VALUES ({LoopIndex * 12});
- Łączna liczba wkładek na każdy test: 10 000
- Resetuje się przy każdym teście:
TRUNCATE TABLE dbo.ManyInserts;(biorąc pod uwagę charakter tego testu, wykonanie FREEPROCCACHE, FREESYSTEMCACHE i DROPCLEANBUFFERS nie wydawało się dodawać dużej wartości).
- Model odzyskiwania: SIMPLE (i może 1 GB wolnego miejsca w pliku dziennika)
- Testy wykorzystujące transakcje używają tylko jednego połączenia, niezależnie od liczby transakcji.
Wyniki:
Test Milliseconds
------- ------------
10k INSERTs across 10k Connections 3968 - 4163
10k INSERTs across 1 Connection 3466 - 3654
10k INSERTs across 1 Transaction 1074 - 1086
10k INSERTs across 10 Transactions 1095 - 1169
Jak widać, nawet jeśli połączenie ADO z bazą danych jest już współużytkowane we wszystkich operacjach, zgrupowanie ich w partie przy użyciu jawnej transakcji (obiekt ADO powinien być w stanie to obsłużyć) jest gwarantowane znacznie (tj. Ponad 2x ulepszenie) skrócić całkowity czas procesu.