

Ostatnie kilka postów skupiało się na temacie ładowania danych za pomocą SQL wraz z logowaniem błędów LOG ERRORS.
Czy jednak warto ładować w ten sposób dane - będzie szybciej? Jak wpływa klauzula LOG ERRORS na czas wykonywania INSERTÓW? Jak wypada w porównaniu do PL/SQL'owego FORALL'a?
Żeby zaspokoić ciekawość (głównie moją :) przeprowadziłam 10 testów. W testach porównywałam ładowanie różnych wolumenów danych różnymi metodami: IAS, IAS + LOG ERRORS, FORALL. Dodatkowo sprawdziłam, jak prezentują się czasy w przypadku, gdy wszystkie rekordy są poprawne oraz sytuację, gdy wszystkie ładowane rekordy są błędne.
Testy przeprowadziłam na środowisku Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production na domowym laptopie :D
Szczegóły przeprowadzenia testów:
Testy zostały przeprowadzone na schemacie HR.
Testy polegały na załadowaniu zmultiplikowanych danych z tabeli HR.EMPLOYEES do nowo utworzonej tabeli myEmp_<nr_testu>, o strukturze identycznej jak HR.EMPLOYEES (z wyjątkiem pola employee_id, gdzie zmieniłam typ pola z number(6) do number.
Do każdej tabeli została utworzona tabela błędów ERR$_<nazwa tabeli> za pomocą funkcji dbms_errlog.create_error_log.
Wszystkim nowo utworzonym tabelom zostały naliczone statystyki.
Symulacja błędów:
Na potrzeby symulacji błędów, w odpowiednich testach długość pola last_name została zmniejszona do dwóch znaków.
Szczegółowy opis testów:
TEST 1: IAS, OK rows.
Ładowanie danych przeprowadzone metodą Insert as select. Wszystkie ładowane rekordy były poprawne i zostały poprawnie załadowane do docelowej tabeli.
TEST 2: IAS + log errors, OK rows
Ładowanie danych przeprowadzone metodą Insert as select wraz z klauzulą LOG ERRORS. Wszystkie ładowane rekordy były poprawne i zostały poprawnie załadowane do docelowej tabeli.
TEST 3: IAS + log errors, NOK rows
Ładowanie danych przeprowadzone metodą Insert as select wraz z klauzulą LOG ERRORS. Wszystkie ładowane rekordy były błędne i zostały zarejestrowane w tabeli błędów ERR$_<nazwa tabeli>.
TEST 4: FORALL + save exeptions, ok rows
Ładowanie danych zostało przeprowadzone z użyciem metod BULK COLLECT i FORALL wraz z save exceptions. Wszystkie ładowane rekordy były poprawne i zostały poprawnie załadowane do docelowej tabeli.
TEST 5: IAS + log errors counting rows (PLSQL variable), OK rows
Ładowanie danych przeprowadzone metodą Insert as select wraz z klauzulą LOG ERRORS. Dodatkowo zostały zliczone ładowane rekordy z użyciem zmiennych globalnych PL/SQL (Jak załadować dane za pomocą SQL - jak sprawdzić czy są błędy? [var]).Wszystkie ładowane rekordy były poprawne i zostały poprawnie załadowane do docelowej tabeli.
TEST 6: FORALL + save exeptions, nok rows
Ładowanie danych zostało przeprowadzone z użyciem metod BULK COLLECT i FORALL wraz z save exceptions. Wszystkie ładowane rekordy były błędne i zostały zarejestrowane w tabeli błędów ERR$_<nazwa tabeli>.
TEST 7: IAS+LOG errors , counting rows (triggers), ok rows
Ładowanie danych przeprowadzono metodą Insert as select wraz z klauzulą LOG ERRORS. Dodatkowo zostały zliczone ładowane rekordy z użyciem zmiennych globalnych PL/SQL oraz triggerów (metoda opisana na blogu Jacka https://www.oraclethoughts.com/sql/insert-log-errors-and-sqlrowcount/).Wszystkie ładowane rekordy były poprawne i zostały poprawnie załadowane do docelowej tabeli.
TEST 8: IAS + log errors counting rows (triggers), NOK rows
Ładowanie danych przeprowadzono metodą Insert as select wraz z klauzulą LOG ERRORS. Dodatkowo zostały zliczone ładowane rekordy z użyciem zmiennych globalnych PL/SQL oraz triggerów (metoda opisana na blogu Jacka https://www.oraclethoughts.com/sql/insert-log-errors-and-sqlrowcount/). Wszystkie ładowane rekordy były błędne i zostały zarejestrowane w tabeli błędów ERR$_<nazwa tabeli>.
TEST 9: IAS + log error, counting rows (plsql variable) , NOK rows
Ładowanie danych przeprowadzone metodą Insert as select wraz z klauzulą LOG ERRORS. Dodatkowo zostały zliczone ładowane rekordy z użyciem zmiennych globalnych PL/SQL (Jak załadować dane za pomocą SQL - jak sprawdzić czy są błędy? [var]). Wszystkie ładowane rekordy były błędne i zostały zarejestrowane w tabeli błędów ERR$_<nazwa tabeli>.
TEST 10: IAS + log error, counting rows (gtt) , NOK rows
Ładowanie danych przeprowadzone metodą Insert as select wraz z klauzulą LOG ERRORS. Dodatkowo zostały zliczone ładowane rekordy z użyciem Global Temporary Table (Jak załadować dane za pomocą SQL - jak sprawdzić czy są błędy?). Wszystkie ładowane rekordy były błędne i zostały zarejestrowane w tabeli błędów ERR$_<nazwa tabeli>.
Uf, trochę tego się zebrało. Ale jeśli chcemy mieć jasny obraz sytuacji trzeba sprawdzić wszystko. Szczerze mówiąc - wyniki testów mnie zaskoczyły. Wiedziałam, że mogą mnie zaskoczyć - i zaskoczyły - ale z zupełniej innej strony!
Ciekawi?
Pierwsze zestawienie pokazuje czasy ładowania danych dla testów 1,2,4,5 i 7 - czyli wszystkie testy z poprawnymi rekordami.
Zielony wiersz - test najszybszy
Czerwony wiersz - test najwolniejszy
Kolumna % pokazuje, ile razy wolniejszy był test w stosunku do testu najszybszego (zielonego).
Zdecydowanym oczywiście liderem jest zwykły IAS, nie martwiący się w ogóle o jakiekolwiek błędy.
Dodanie do IAS klauzuli LOG ERRORS - powoduje trzykrotne spowolnienie.
IAS + LOG ERRORS oraz FORALL -> zadziwiająco blisko siebie - ale prawie 3 razy wolniej niż bez logowanie błędów.
Stawkę zamykają testy IAS + LOG ERRORS z dodatkowym zliczaniem rekordów błędnych - dodanie takiego fjuczera kosztuje nas prawie drugie tyle czasu!
A jak sytuacja będzie wyglądać, jeśli wszystkie ładowanie rekordy będą błędne?
Spójrzmy:
Najszybszy - i to znacznie - FORALL!!!!
Pozostałe metody dość blisko siebie, IAS + LOG ERRORS wraz ze zliczeniem rekordów nieco wolniejszy niż wersja bez zliczania rekordów - niemniej i tak wszystkie testy prawie dwukrotnie wolniejsze od FORALL!
W przypadku IAS + LOG ERRORS - duża ilość (w tym wypadku wszystkie) błędnych rekordów zwiększa czas przetwarzania ponad ośmiokrotnie!


FORALL niewiele lepszy - spowolnienie sześciokrotne.
Spójrzmy jeszcze na porównanie IAS + LOG ERRORS i FORALL dla poprawnych rekordów:

IAS jest szybszy o 20%. Czy to dużo?

IAS jest szybszy o 20%. Czy to dużo?
Podsumowanie
Która metoda lepsza? Ta, która pasuje do Waszych danych. U mnie testy dały takie wyniki ale czy u Was będzie tak samo? Tego się nie dowiecie póki nie sprawdzicie. A na co zwrócić uwagę mam nadzieję podpowie Wam ten post.
Niemniej gdybyśmy chcieli jakieś wnioski wyciągać na podstawie powyższych testów można by zaryzykować, że jeśli mamy pewność, że dane są poprawne, zdecydowanie najszybszy będzie zwykły IAS. Jeśli zaś mogą wystąpić błędy i chcemy jest obsłużyć - to być może warto wybrać FORALL, który wcale nie jest dużo wolniejszy od IAS przy poprawnych rekordach, ale przy dużej ilości błędów jest wydajniejszy no i siłą rzeczy mamy większą kontrolę nad przetwarzaniem i logowaniem błędów.
Zaskoczyły was wyniki testów?
Mnie bardzo. To, że dodanie logowania błędów spowolni ładowanie danych - spodziewałam się. Ale nie sądziłam, że FORALL będzie tak blisko wyników IAS - a nawet w ekstremalnym przypadku wszystkich rekordów błędnych - nawet znacznie szybszy! Do tej pory hołdowałam zasadzie - co możesz zrobić w SQL - zrób w SQL. Ale teraz zmieniłabym to na - jeśli możesz coś zrobić w SQL - sprawdź najpierw jak tam czasy :D
Komentarze
Prześlij komentarz