Jak zoptymalizować UPDATE około 100tys wierszy?

Hej,

może znajdzie się tutaj ktoś kto umie szybciej wykonać masową aktualizację na bazie i podpowie ;)

Problem:
Pobranie około 100k wierszy z bazy z około 10 kolumnami i przepuszczanie je przez pewne reguły (czyt. jeśli col1 = var1and col2 = var2,..., col10 = var10 to ustaw flagę na Y/N i zapisz).

Moje podejście:
1.Pobranie liczby wierszy do aktualizacji
2.Dodanie paginacji do pobierania 5k rekordów na stronę
3. Natywna querka do pobrania dto
4. Przestreamowanie po wszystkich dto
Odfiltrowanie dto których flaga się nie zmieniła i zapis z updatem do bazy po 1000 id-ków bo tyle wynosi limit w Oracle w klauzurze IN(..)

Dla 150k wierszy proces ten zabiera około 5 min.
Z tego co sprawdzałem aktualizacja typu
Update table set flag = ? Where ID In (... 1000 id-ków tutaj) zabiera 0,3s. Proces jest cyklicznie odpalany 1 raz dziennie przez crona.

no i tu bym dalej nie kombinował, 5 minut w czasie małego obciążenia systemu to jest bardzo niedużo jak na tyle operacji.

Ile powinien trwać taki update, żebyś uznał że wydajność jest wystarczająca?

Najszybciej to bylo by to zrobic w sql na bazie zamiast przepychania danych między bazą i klientem.

S4t napisał(a):

Najszybciej to bylo by to zrobic w sql na bazie zamiast przepychania danych między bazą i klientem.

Tu się zgodzę, ale te reguły z którymi są zestawiane wiersze z bazy są zmienne i trzymane są innym systemie.

@yarel: myślałem nad 2 min może nawet kosztem zużycia procka na chwilę wyższego i ewentualnie ramu

Jeśli to Oracle, to logikę mozna wepchnąć do pakietu pl/sql i uruchamiać jako job z oraclowego schedulera.

1. Rekordy mozesz wybrać używając parallel query i zapamiętać rowidy, dla których należy zaktualizować flagę
2. Aktualizację mozna zrobić za pomocą pl/sql owego FOR ALL (bulk update) z rozmiarem batcha duzo wiekszym niz limit klauzuli IN.

Tu w odpowiedziach masz pewną propozycję. Tworzysz tabelę tymczasową (global temporary table) i do niej wstawiasz rekordy, które mają ulec modyfikacji. Tak naprawdę wystarczy wstawić tylko klucz główny i docelową wartość flagi. Oczywiście musi być tam indeks unikalny (teraz nie pamiętam czy wystarczy unikalny czy jednak trzeba dodać constraint primary key).

Potem używasz UPDATE z inline view (czyli podzapytaniem łączącym tabelę oryginalną z tymczasową po kluczu głównym - musi być zależność 1:1 między nimi) albo MERGE.

https://stackoverflow.com/questions/34983023/oracle-sql-how-to-do-massive-updates-more-efficient-and-faster

Używałem tej metody wielokrotnie na milionowych zbiorach i zwykle to była najszybsza opcja zaktualizowania dużej liczby rekordów, przy okazji zmieniając kilka-kilkanaście pól w tabeli docelowej. Jeśli to jednorazowa akcja "serwisowa" to dla przyspieszenia update zapisywałem sobie ROWID oryginalnego rekordu i UPDATE używał właśnie ROWID a nie pola ID z tabeli.

S4t napisał(a):

Najszybciej to bylo by to zrobic w sql na bazie zamiast przepychania danych między bazą i klientem.

To jest najrozsądniejsze wyjście IMO

a tej paginacji nie robisz czasem offsetem?

Z tego co piszesz, czyli cron odpalany raz na dobę, to te procesowanie może trwać zarówno 10 sekund, 1 minutę i 10 minut, różnicy żadnej nie robi. Skąd to wiem? Bo skoro stan bazy może być nieaktualny 23h 59 minut (jeśli się coś zmieni minutę po updejcie), to czekanie kilku minut tym bardziej nic nie zmieni.

Aktualizacja 150k wierszy w Cassandra zajmuje ok 1 sekundę... na laptopie... sprzed 7 lat... uderzając każdym zapytaniem osobno. Razem z czytaniem i modyfikacją nie powinno być więcej niż 2-3 sekundy na całość. Oracle jest powolniejszy ale nie aż tak. Pewnie robisz to sekwencyjnie zamiast zrównoleglić. No i Hibernate nie nadaje się do przetwarzania dużych ilości danych.

Dostęp obiektowy zawsze będzie miał ten problem, transmisji i konwersji milionów obiektów GC dla błahej operacji, i zawsze SQL będzie ogólnie wydajniejszy (w skrócie można użyć słowa szybszy)

Nie wiem czy wiesz, ale JPA przewiduje update w takim sensie, że aktualizuje w bazie bez ściągania encji do formy obiektowej na kliencie

https://www.google.com/search?client=firefox-b-d&q=jpa+mass+update

Mamy wtedy o tyle lepiej, że kod aktualizacji jest konserwowany razem z kodem Javowskim, a nie pół korytarza dalej u bazodanowców. Zwłaszcza używając Criteria API mamy jakie-takie bezpieczeństwo na zmiany nazwy pół.

https://www.google.com/search?client=firefox-b-d&q=jpa+criteria+mass+update

Jest to znacznie bardziej mniej intuicyjne niż operowanie na polach encji (nazywam "podejście obiektowe"), ale się da.

Zapewne masz tam hibernate ktory sledzi encje i robi dirty checking ktorego czas rosnie bardzo szybko w kosmos

Osobiscie jak sie z tym kiedys bawilem to mialem 100tys rekordow w jakies 20s wrzucanych przez hibernate bez zadnych zabaw poza poprawnym czyszczeniem kontekstu hibernate i ustawieniem odpowiedniego batch size. Jeszcze jak sam zauwazasz ze sam update to 0.3s to na pewno ci cos w aplikacji mieli. Najlepiej odpal sobie jakies profilowanie i zobacz na czym wisi aplikacja. Wiecej bez patrzenia w kody ciezko potwiedziec.

@Ziemiak:

+1
Ważna wzmianka o optymalizacji.

Pytanie czy / czy zawsze robić przez pobierane encje.
Sam bym zajał rozbieżne stanowiska
a) zasada aktualizacji jest złożona i będzie silnie zmienna - niech się aktualizuje już utrzymywanym kodem Javy
b) encje po aktualizacji są do czegoś potrzebne

else
c) wyżej wskazane JPA batch update albo SQL

@aolo23: użyj R2DBC zamiast JPA. Wtedy otrzymujesz strumień rekordów, na którym możesz sobie pracować.