Można sobie wyobrazić wiele scenariuszy, w których aplikacje, skrypty, czy usługi dokonują operacji dodawania nowych danych do bazy SQL Server. O ile w przypadku małych ilości rekordów wydajność może być praktycznie pominięta w rozważaniach, o tyle w przypadku aplikacji (platform) integracyjnych, ilości rekordów wrzucanych do bazy mogą dochodzić do setek tysięcy/milionów dziennie. Wydajność zaczyna mieć wtedy znaczenie co najmniej istotne, a często - priorytetowe.
Załóżmy sytuację początkową - aplikacja napisana w Perlu (lub Javie), która co kilkanaście sekund łączy się do określonego serwera FTP i sprawdza istnienie pliku o danej nazwie w odpowiednim katalogu. Jeżeli plik zostanie odnaleziony, aplikacja odczytuje go i odpowiednio przetwarza jego zawartość. Efektem tego przetwarzania jest około 50000 elementów w tablicy asocjacyjnej (lub zmiennej innego typu) zawierających dane teleadresowe, które mają być wrzucone do tabeli jako 50000 rekordów. Aplikacja łączy się do bazy SQL Server przez ODBC (lub JDBC) i - co wydaje się naturalne - per każdy element tablicy wykonuje operację typu:
INSERT INTO client_data (colA, colB, colC, ...) VALUES ('aaaa', 'bbbb', 'cccc', ...)
Proces dodawania 50000 rekordów do tabeli trwa w produkcyjnych warunkach około 1-2 min.
Opisany scenariusz można pewnie spotkać w nie jednej firmie. Co w nim można zoptymalizować? Jak się okazuje - przynajmniej dwie kwestie.
Przede wszystkim chciałbym uściślić/sprecyzować jedno pojęcie, którym będę się posługiwał - jest nim
batch. Batch to zbiór instrukcji T-SQL przesłanych jednocześnie z aplikacji do SQL Server. Batchem bywa więc zarówno jedna instrukcja (SELECT, INSERT, UPDATE), jak i skomplikowany skrypt. Batch jest traktowany i przetwarzanie przez SQL Server jako łączny zestaw operacji, tzn. jest całościowo parsowany, bindowany, algebryzowany i optymalizowany (przy czym algebryzacja i optymalizacja logicznie dotyczą poszczególnych wyrażeń).
Jakie to ma znaczenie w odniesieniu do opisanego powyżej scenariusza? Otóż zasadnicze. Inserty wysyłane są do serwera pojedynczo, tzn. jeden per batch, to po prostu marnotrawstwo zasobów (istnieje tu pewna analogia do charakterystyki kursorów). Dla każdego batcha SQL Server musi wykonać te same stosunkowo złożone czynności kompilacyjne. Dodatkowo dochodzi jeszcze bardzo ważna kwestia warstwy transportu - w pakietach z jedną operacją INSERT stosunek treści (czyli kodu T-SQL) do całościowej ilości przesyłanych informacji jest bardzo niewielki. Skutkuje to tym, że sumarycznie więcej w pakietach informacji sterujących (nagłówkowych) niż meritum - transmisja ma więc minimalną efektywność, a koszty związane z jej obsługą są relatywnie bardzo wysokie. To tak jakby komunikować się za pomocą e-maili czy SMSów, wysyłając w poszczególnych wiadomościach wyłącznie jedną literę.
Należy więc doprowadzić do sytuacji, w której aplikacja będzie wysyłała do SQL Server batche zawierające np. kilkaset operacji INSERT. Wtedy wydajniejsza będzie zarówno komunikacja z serwerem, jak też i procedowanie tych operacji w bazie danych. W środowisku testowym udawało mi się uzyskiwać nawet
kilkunastokrotne zwiększenie wydajności opisanego na wstępie scenariusza. Trzeba drogą empiryczną ustalić stosunek ilości insertów w batchu, dla którego wydajność importu całej grupy rekordów jest największa. W moich testach były to okolice dziesiątej części liczby rekordów (dla 50000 było to ok. 6500 insertów w batchu).
Nie należy się raczej obawiać przekroczenia ograniczeń co do ilości znaków w batchu. Maksimum wynosi: 64 KB * rozmiar pakietu (zwykle 4 KB), co daje 256 MB.
Implementacja powyższego zalecenia w warstwie DAL aplikacji jest bardzo prosta, wystarczy w pętli agregować treść instrukcji INSERT (koniecznie oddzielanych nową linią) do zmiennej typu string, a po przekroczeniu ustalonej ich ilości, wysłać tak stworzone zapytanie do bazy danych.
Dodatkowym zabiegiem optymalizacyjnym będzie wyłączenie domyślnej opcji AutoCommit (większość providerów ustawia ją podczas zestawiania połączenia do bazy, domyślnie jest włączona) i objęcie wszystkich batchy jedną transakcją. Spowoduje to dodatkowo zwykle kilkunastoprocentowy wzrost wydajności, gdyż SQL Server nie będzie musiał zatwierdzać transakcji po każdym dodaniu rekordu, które nie zakończy się błędem (AutoCommit) - zrobi to jednorazowo dla wszystkich insertów.
