Jak zoptymalizować UPDATE około 100tys wierszy?

Jak zoptymalizować UPDATE około 100tys wierszy?
aolo23
  • Rejestracja:ponad 7 lat
  • Ostatnio:30 dni
  • Postów:186
0

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.


Exception oznacza więcej niż tysiąc słów.
edytowany 1x, ostatnio: Riddle
EH
  • Rejestracja:ponad 2 lata
  • Ostatnio:około rok
  • Postów:1208
2

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.

Zobacz pozostałe 3 komentarze
PI
Zgadzam się z @ehhhhh (see my post)
KR
5 minut na 300k operacji bazodanowych to straszny żółw. To jest raptem ok 1 tys. zapytań na sekundę - współczesne bazy danych potrafią znacznie, znacznie szybciej.
PI
@Krolik: Tylko że nie chodzi o to czy to szybko czy wolno, tylko czy wystarczająco.
S4
Ale wystarczająco do czego. Może tam lata 50 takich zadań i się nie wyrabiają w odpowiednim czasie
PI
Napisał, że raz na 24h. W tym kontekście, skoro żaden użytkownik nie czeka na te dane, 5 minut to wystarczająco szybko.
YA
  • Rejestracja:prawie 10 lat
  • Ostatnio:około 13 godzin
  • Postów:2367
1

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

S4
  • Rejestracja:około 3 lata
  • Ostatnio:około rok
  • Postów:1268
5

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

aolo23
  • Rejestracja:ponad 7 lat
  • Ostatnio:30 dni
  • Postów:186
0
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


Exception oznacza więcej niż tysiąc słów.
YA
  • Rejestracja:prawie 10 lat
  • Ostatnio:około 13 godzin
  • Postów:2367
5

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.
R7
  • Rejestracja:około 6 lat
  • Ostatnio:7 miesięcy
  • Postów:216
1

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.

DR
  • Rejestracja:prawie 12 lat
  • Ostatnio:około 15 godzin
  • Postów:1129
0
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

edytowany 1x, ostatnio: Riddle
RA
  • Rejestracja:około 3 lata
  • Ostatnio:około 19 godzin
  • Postów:54
0

a tej paginacji nie robisz czasem offsetem?

PI
  • Rejestracja:ponad 9 lat
  • Ostatnio:3 miesiące
  • Postów:2787
0

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.

KR
Moderator
  • Rejestracja:prawie 21 lat
  • Ostatnio:około 10 godzin
  • Postów:2964
1

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.

AK
A Cassandra nie buforuje w RAM ? NoSQL-e lubią nie flushowac zbyt gorliwie danych
SO
Aktualizacja 150k wierszy w Cassandra zajmuje ok 1 sekundę... na laptopie... sprzed 7 lat No to taki 7 letni laptop potrafi być sporo szybszy od jakiejś chmurowej bazki stojącej na PaaSie z dyskiem sieciowym zamiast lokalnego :D
KR
Każda baza buforuje i flushuje co jakiś czas zgodnie z tym jak jest skonfigurowana. Jeśli chcesz zmienić 150k rowków to nie ma sensu robić fsync po każdym z nich.
AK
  • Rejestracja:ponad 6 lat
  • Ostatnio:około rok
  • Postów:3561
1

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.


Bo C to najlepszy język, każdy uczeń ci to powie
edytowany 2x, ostatnio: AnyKtokolwiek
ZI
  • Rejestracja:ponad 8 lat
  • Ostatnio:około 13 godzin
  • Postów:228
1

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.

AK
  • Rejestracja:ponad 6 lat
  • Ostatnio:około rok
  • Postów:3561
0

@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


Bo C to najlepszy język, każdy uczeń ci to powie
Koziołek
Moderator
  • Rejestracja:prawie 18 lat
  • Ostatnio:około miesiąc
  • Lokalizacja:Stacktrace
  • Postów:6821
2

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


Sięgam tam, gdzie wzrok nie sięga… a tam NullPointerException
aolo23
czytałem o tym ale nie widziałem libki Ciekawe - na pewno przetestuje to podejście w odniesieniu do poprzedniego
Kliknij, aby dodać treść...

Pomoc 1.18.8

Typografia

Edytor obsługuje składnie Markdown, w której pojedynczy akcent *kursywa* oraz _kursywa_ to pochylenie. Z kolei podwójny akcent **pogrubienie** oraz __pogrubienie__ to pogrubienie. Dodanie znaczników ~~strike~~ to przekreślenie.

Możesz dodać formatowanie komendami , , oraz .

Ponieważ dekoracja podkreślenia jest przeznaczona na linki, markdown nie zawiera specjalnej składni dla podkreślenia. Dlatego by dodać podkreślenie, użyj <u>underline</u>.

Komendy formatujące reagują na skróty klawiszowe: Ctrl+B, Ctrl+I, Ctrl+U oraz Ctrl+S.

Linki

By dodać link w edytorze użyj komendy lub użyj składni [title](link). URL umieszczony w linku lub nawet URL umieszczony bezpośrednio w tekście będzie aktywny i klikalny.

Jeżeli chcesz, możesz samodzielnie dodać link: <a href="link">title</a>.

Wewnętrzne odnośniki

Możesz umieścić odnośnik do wewnętrznej podstrony, używając następującej składni: [[Delphi/Kompendium]] lub [[Delphi/Kompendium|kliknij, aby przejść do kompendium]]. Odnośniki mogą prowadzić do Forum 4programmers.net lub np. do Kompendium.

Wspomnienia użytkowników

By wspomnieć użytkownika forum, wpisz w formularzu znak @. Zobaczysz okienko samouzupełniające nazwy użytkowników. Samouzupełnienie dobierze odpowiedni format wspomnienia, zależnie od tego czy w nazwie użytkownika znajduje się spacja.

Znaczniki HTML

Dozwolone jest używanie niektórych znaczników HTML: <a>, <b>, <i>, <kbd>, <del>, <strong>, <dfn>, <pre>, <blockquote>, <hr/>, <sub>, <sup> oraz <img/>.

Skróty klawiszowe

Dodaj kombinację klawiszy komendą notacji klawiszy lub skrótem klawiszowym Alt+K.

Reprezentuj kombinacje klawiszowe używając taga <kbd>. Oddziel od siebie klawisze znakiem plus, np <kbd>Alt+Tab</kbd>.

Indeks górny oraz dolny

Przykład: wpisując H<sub>2</sub>O i m<sup>2</sup> otrzymasz: H2O i m2.

Składnia Tex

By precyzyjnie wyrazić działanie matematyczne, użyj składni Tex.

<tex>arcctg(x) = argtan(\frac{1}{x}) = arcsin(\frac{1}{\sqrt{1+x^2}})</tex>

Kod źródłowy

Krótkie fragmenty kodu

Wszelkie jednolinijkowe instrukcje języka programowania powinny być zawarte pomiędzy obróconymi apostrofami: `kod instrukcji` lub ``console.log(`string`);``.

Kod wielolinijkowy

Dodaj fragment kodu komendą . Fragmenty kodu zajmujące całą lub więcej linijek powinny być umieszczone w wielolinijkowym fragmencie kodu. Znaczniki ``` lub ~~~ umożliwiają kolorowanie różnych języków programowania. Możemy nadać nazwę języka programowania używając auto-uzupełnienia, kod został pokolorowany używając konkretnych ustawień kolorowania składni:

```javascript
document.write('Hello World');
```

Możesz zaznaczyć również już wklejony kod w edytorze, i użyć komendy  by zamienić go w kod. Użyj kombinacji Ctrl+`, by dodać fragment kodu bez oznaczników języka.

Tabelki

Dodaj przykładową tabelkę używając komendy . Przykładowa tabelka składa się z dwóch kolumn, nagłówka i jednego wiersza.

Wygeneruj tabelkę na podstawie szablonu. Oddziel komórki separatorem ; lub |, a następnie zaznacz szablonu.

nazwisko;dziedzina;odkrycie
Pitagoras;mathematics;Pythagorean Theorem
Albert Einstein;physics;General Relativity
Marie Curie, Pierre Curie;chemistry;Radium, Polonium

Użyj komendy by zamienić zaznaczony szablon na tabelkę Markdown.

Lista uporządkowana i nieuporządkowana

Możliwe jest tworzenie listy numerowanych oraz wypunktowanych. Wystarczy, że pierwszym znakiem linii będzie * lub - dla listy nieuporządkowanej oraz 1. dla listy uporządkowanej.

Użyj komendy by dodać listę uporządkowaną.

1. Lista numerowana
2. Lista numerowana

Użyj komendy by dodać listę nieuporządkowaną.

* Lista wypunktowana
* Lista wypunktowana
** Lista wypunktowana (drugi poziom)

Składnia Markdown

Edytor obsługuje składnię Markdown, która składa się ze znaków specjalnych. Dostępne komendy, jak formatowanie , dodanie tabelki lub fragmentu kodu są w pewnym sensie świadome otaczającej jej składni, i postarają się unikać uszkodzenia jej.

Dla przykładu, używając tylko dostępnych komend, nie możemy dodać formatowania pogrubienia do kodu wielolinijkowego, albo dodać listy do tabelki - mogłoby to doprowadzić do uszkodzenia składni.

W pewnych odosobnionych przypadkach brak nowej linii przed elementami markdown również mógłby uszkodzić składnie, dlatego edytor dodaje brakujące nowe linie. Dla przykładu, dodanie formatowania pochylenia zaraz po tabelce, mogłoby zostać błędne zinterpretowane, więc edytor doda oddzielającą nową linię pomiędzy tabelką, a pochyleniem.

Skróty klawiszowe

Skróty formatujące, kiedy w edytorze znajduje się pojedynczy kursor, wstawiają sformatowany tekst przykładowy. Jeśli w edytorze znajduje się zaznaczenie (słowo, linijka, paragraf), wtedy zaznaczenie zostaje sformatowane.

  • Ctrl+B - dodaj pogrubienie lub pogrub zaznaczenie
  • Ctrl+I - dodaj pochylenie lub pochyl zaznaczenie
  • Ctrl+U - dodaj podkreślenie lub podkreśl zaznaczenie
  • Ctrl+S - dodaj przekreślenie lub przekreśl zaznaczenie

Notacja Klawiszy

  • Alt+K - dodaj notację klawiszy

Fragment kodu bez oznacznika

  • Alt+C - dodaj pusty fragment kodu

Skróty operujące na kodzie i linijkach:

  • Alt+L - zaznaczenie całej linii
  • Alt+, Alt+ - przeniesienie linijki w której znajduje się kursor w górę/dół.
  • Tab/⌘+] - dodaj wcięcie (wcięcie w prawo)
  • Shit+Tab/⌘+[ - usunięcie wcięcia (wycięcie w lewo)

Dodawanie postów:

  • Ctrl+Enter - dodaj post
  • ⌘+Enter - dodaj post (MacOS)