Java a SQL

Witam,
czy ktoś mógłby mi wytłumaczyć dlaczego lepiej wykonywać różne obliczenia czy zapytania w sql np tworząc procedurę a nie w javie przez pobierając dane z sql?

Różnie. Często jak po stronie bazy przefiltrujesz dane, masz ich potem mniej do przesłania przez sieć = szybciej. Dodatkowo też mniejsza szansa, że w aplikacji poleci jakiś OOM.

Wcale nie lepiej. To oczywista bzdura. W pewnych sytuacjach zrobienie przetwarzania w procedurze składowanej może być wydajniejsze, ale nie musi. Ma za to inne wady: zwykle kijowy język z ery kamienia lizanego, problemy z użyciem cache w aplikacji, przywiązanie do bazy, utrudnienie testowania itp.
Widziałem nawet akcje, że wyniesienie bardziej skomplikowanych obliczeń do sql (transact sql) totalnie spowalniało system.(ale możliwe, że ktoś coś zrypał w tym mssqlu )

danek napisał(a):

Różnie. Często jak po stronie bazy przefiltrujesz dane, masz ich potem mniej do przesłania przez sieć = szybciej. Dodatkowo też mniejsza szansa, że w aplikacji poleci jakiś OOM.

Jeśli jest jakakolwiek szansa na OOM to można spokojnie sobie przestreamować dane i problem w dużej mierze znika ;)

Co do procedur, kiedyś byłem w projekcie gdzie baza danych i procedury składowane były "szyną integracyjną". Zabawa na całego, czasami dodanie bzdurnego feature'a zajmowało 2 tygodnie bo się odpalały procedury mielące pół systemu, a niekoniecznie tego oczekiwałeś :P

Procedury składowane to zło, którego należy unikać jak ognia. Oprócz szpetnego języka programowania, problemów z testowaniem i wdrażeniam oraz braku dobrego IDE jest jeszcze to, że bazę danych o wiele trudniej się skaluje niż aplikację. Dlatego coraz częściej mówi się o tym, że baza danych powinna być głupia i służyć tylko do zapisu i odczytu danych

@rchudy:jak zwykle - to zależy.
IMHO jeśli masz wszystkie dane na jednym node (zwykle raportowym) i robisz jakąś agregację to nie ma sensu pchać danych przez sieć, obciążać CPU po stronie klienta, robisz to tam gdzie są dane i jest pozamiatane. Niektóre zapytania lecą przez pół bazy danych (szczególnie raporty) i też nie za bardzo jest to sens pchać do logiki biznesowej. Szczególnie jeśli masz jakiś system do wyświetlania raportów, który podpinasz tylko pod dane wyjściowe i chodzi o wyplucie jakieś tabelki. Biznes przychodzi sobie z wymaganiem, typu: chcę to co w zeszłym miesiącu, ale jeszcze to i tamto - Ctrl+C, Ctrl+V refactoring działa, rzeźbisz sobie taki PL/SQL czy inne SSRS w kilka min i zrobione. Po stronie bazy możesz też sobie pozakładać index'y tak jak chcesz i przyspieszyć tym join'y wszelakie (w kodzie musiałbyś to replikować pakując dane w hashtable etc). W momencie w którym pojawia się cursor, lub jakaś inna pętla wydajność tego podejścia bierze w łeb oczywiście. To samo z jakimś ad-hoc update, robionym na potrzeby samego zapytania, aczkolwiek jeśli dane się liczą przez kilka minut to miałem rozwiązania, którego wyniki się cache'ują (w pamięci bazy, per wywołanie procki & parametrów), a te dłuższe, klikadziesiąt wrzucałem do temp tabelek. Można by było postawić server, na nim robić cache etc, ale po co jak to wszystko się robi tylko dla Mariana z księgowości.
Główną jego zaletą jest to, że te 50-lat optymalizacji SQL'a działa na Twoją korzyść. Da się PL/SQL i SSRS potestować, są IDE do obu, także tragedii nie ma. Narzędzie jak każde inne, można korzystać. Sama koncepcja 'procedury składowanej' sprowadza się tu do opakowania zapytania w jednostkę, która jest skompilowana vs. baza danych (więc jak zmienisz scheme to od razu zobaczysz, że coś jest nie tak a nie po pierwszym odpaleniu) oraz odrobnie większej kontroli nad dostępem (można tak ustawić prockę, że np.dane wypluje nawet jeśli user nie ma dostępu do tabeli).
@jarekr000000 @Kamil Żabiński powodzenia życzę w wygenerowaniu raportu w stylu: tygodniowa agregata wartości tranzakcji pracowników którzy przez ostatnie 10 lat choć raz osiągnęli miesięczny wzrost sprzedaży większy niż 4% (mielący na bazie danej obejmującej tak z ~2400 oddziałów, kilkanaście tysięcy pracowników (aktywny, na urlopie, etc), miliony tranzakcji, w różnych walutach (wszystko ma być w USD), czujecie to? Niestety nie wszędzie jest już infrastruktura w której da się odpalić map-reduce a i nawet z jego pomocą czasem to po prosty nie ma sensu, kurzy się sobie jakiś leciwy Spark z jedynym 1TB RAM, który jakiś szaleniec zakupił zaraz przed krachem i na nim to poleci przez te 16min 32sec, które tak pieczołowicie się profiluje ;-)

@stic ja Ci życzę powodzenia w pisaniu systemów w PL/SQLu czy jakimkolwiek języku do procedur składowanych. Oby jak najwięcej.

Co innego napisać tego typu raport z używciem sprytnych aggregacji w SQL, a co innego wchodzienie w PL/SQL.
Być może gdzieś są przykłady, że to ma sens, ale jeszcze nie spotkałem :-)

Za to spotkałem systemy klękające na produkcji, nieskalowalne, od których uciekali programiści, bo miały popisane cąłą logikę w procedurach, któś ich oszukał, że to będzie wydajnie i fajnie.
Nie jest wydajnie bo jezyki do procedu składowanych mają prawie nic doświadczenia w robieniu "general purpose". Jak czegoś nie da się zrobić na kursorach to następuje dramat.
Zresztą - gdzie jest PL/SQL czy inny *.SQL w takich zestawieniach : https://benchmarksgame-team.pages.debian.net/benchmarksgame/index.html ? Może czas dopisać.
Może czas na Quake PL/ SQL - edition - w innych językach juz zrobili. (http://www.quakejs.com/)
Dodatkowym problemem w takich systemach jest kiepska utrzymywalność (procedury składowane generalnie to języki w stylu szalone lata 80te...), beznadziejne narzędzia do edycji, refaktoringu i monitorowania. Debug nie jest już dramatem, ale w porównaniu do innych jezyków to nadal kamień lizany.

Bardzo kiepsko jest w wydajnością całych systemów:
Jeszcze nie widziałem sytuacji, żeby nie wyszło w praktyce, że my tu musimy działać na jednej instancji de fakto.... (nawet Oracle RAC swego czasu bardzo nas zawiódł).

Zabwane, że takie raporty jak pisałeś robiłem swego czasu w javie i było ciężko. Choćby dlatego, że to były czasy javy 32 bitowej i RAM był naprawdę problemem. Tylko PL/SQL nie dawało się zupełnie użyć ->
albowiem iż dane były w 3 różnych bazach danych (oracle, mssqll i myssql :) ) i do tego w plikach CSV. Pionieważ ja czasem piszę w PL/SQL to wiem jakie to beznadziejny pomysł byłby, nawet jeśli potencjalnie możliwy do realizacji - IMO wtedy do było niemożliwe. Koniec końców walczyłem z RAMem w javie, ale całkiem spoko dawałem radę :-)

W tym, roku wystarczy mi że pisałem PL/SQL, który korzystał z danych w JSON. Oracle przecież wspiera JSONy w kolumnach. Rzeczywiście w porównaniu do ręcznego parsowania/ przycinania VARCHARów to jest jakieś wsparcie...

Koniec końców - system, w którym jest kilka procedur napisanych do przetwarzania dużych danych, które ktoś (jakis wariat chyba) wrzucił do bazy danych SQL może miec sens.
Ale system, który jest cały popisany na procedurach to dramat - widziałem takie, nawet kilka. Nein, danke.

Ciekawe jakie byłyby opinie na jakimś forum DBA ;)

BTW pracowałem kiedyś przy systemie Spring+Hibernate, gdzie liderem technicznym można powiedzieć był bazodanowiec. To był koszmar, bo zespół dev był rozliczany z każdej sqlki wyplutej z Hibernate. Odpowiedzią na większość ficzerów było „to można zrobić prostą procką na bazie”. Wniosek w sumie jeden: ten projekt to był KOSZMAR. Aczkolwiek uważam, że jest ten 1% przypadków, gdzie procedura będzie faktycznie szybsza (zwykle nie są to przyczyny obiektywnie techniczne, zawsze działamy w jakimś kontekście, jakieś decyzje zostały dawno temu podjęte itp.)

@jarekr000000: to jak byś ugryzł ten problem? Masz tak z ~3TB danych do przemielenia w celu wyplucia agregacji, w modelu masz wykonane transakcje (tak z 5-7 obiektów i z każdej trzeba sprawdzić kilka kilka wartość, ilość, cenę, datę, typ produkty, etc), sprzedawcę (tak 3-5 obiektów, np. jeśli przyniósł się pomiędzy oddziałami, musisz to wsiąść po uwagę, etc), dane dodatkowe (kurs walut na koniec dnia, info, stan w magazynie, etc). Teraz masz porównać sprzedaż pomiędzy dwoma miesiącami (windowing) po poprzednich wynikach.

Jeśli masz "nowa" architekturę, w której można taką agregację dorzucić do modelu i pod początku odświeżać wraz z występującymi wydarzeniami to spoko, ale jeśli masz starszy system, w którym nikt Ci za to nie zapłaci (np. ktoś już zjadł budżet przypisując system z 3-raz) a raport trzeba wypluć bo się regulator obrazi (masz na to 2-3 dni max), to nie znam narzędzia które zrobi to szybciej niż SQL. Gdy dostałem trochę kasy spróbowałem wykorzystać Pinot (ale nie miałem ludzi i czasu aby postawić infra pod niego), dorzuciłem więc do projektu ActivePivot (i tak akurat załatwiłem ok. 2/3 lżejszych raportów).

Także, procki na pewno nie są rozwiązaniem dla CRUDa (chyba, że z jakiegoś powodu, klient jest pisany np. w C i ktoś się strasznie uparł, że to będzie 2-tier architektura (np. IoT + panel raportujący), innego nie widzę). Ale widzę je wciąż jako odpowiednie w raportowaniu (które w raz z upływem czasu staje się coraz ważniejsza funkcja wielu systemów). Zgadzam się, że od samego początku dobrze przygotować się na agregację (i umożliwić ich odpalenie na podstawie danych historycznych), ale nie zawsze jest to możliwe i nie wszystko da się przewidzieć.

Odnośnie jednej instancji zaś, zazwyczaj jest jakiś parametr po którym można podzielić system. W przypadku w którym pisze, miałem ok. 3-4TB danych do przemielenia per region i takie też partycje w bazie (żeby nie było, w ActivePivot miałem instancje per region w państwie, agregujące do państwa, i jakoś to działo na 12+2 instancjach, na więcej niestety nie było kasy, szczególnie, że SQL dawało odpowiedź tak do 20min, a jak się agregację w AP wykrzaczyla (albo ktoś chciał dodać nowe axis), to przeliczenie zajmowało ok. 3-dniowy batch). Trochę zapewne sam tu sobie utrudniłem zadanie, ponieważ mój 'biznes' kumał już SQL'a i nawet powoli zaczął ogarniać czemu to zabiera tyle czasu.

W sumie, myślę że dziś użyłby SQL (pakowanego w procki) tam gdzie jest szybszym i łatwiejszym w modyfikacji i późniejszym utrzymaniu rozwiązaniem problemu.
I tu herezja, mam teraz w systemie live (dane transakcji, które w nim są aż do osiągnięcia end-state, tak do < 2-tygodni) i history (log operacji, z wersją i timestamp). Wrzucanie do loga było przez Kafka (także była transakcja po bazie danych - creat/update na live, Kafka, add na history). Okazało się że czas sprawdzania czy operacja już nie jest w history (jak masz kilkadziesiąt serwisów, rozproszony system, itd to się zdarzy kolizja), blokował nam dość szybko cały system. Trochę hakiem, wrzuciliśmy create/update live + add to history, do procki (w pre-Kafka-era było na triggerach, także proszę nie bić), w tle replikacja pomiędzy bazami wciąż po Kafka. Nie miałem za dużo czasu żeby się w temat wgłębić, ale działa sprawnie.
Ma może ktoś ma na to lepszy pattern?
Ponieważ jak o tym piszę to coś mi tu śmierdzi.

Próbowaliście podejścia z zrzucaniem eventów na Hadoopa i generowania raportów za pomocą np. Sparka i Airflow?

@Charles_Ray: piszesz o tych koszmarnych raportach? Nie, ktoś tam wspomniał Airflow ale nikt się za temat nie zabrał od tej strony. Jak by to miało działać? Agregacja by była w data-pipline? Nie byłoby tu za dużo zależności jeśli masz takie zapytanie potworka które musi sprawdzi pół schemy ?
Pracowałem wtedy w banku, gdzie też zaczęliśmy przechodzić na ActivePivot (wtedy był jeszcze semi-open-source, a firma za nim się nazywała Quartet FS, teraz ActiveViam). Wiedziałem więc co to mniej więcej oferuje, co ważniejsze miałem nie tak daleko do konsultantów z tejże firmy, od których sporo się nauczyłem.
Nie sądzę, żebyśmy naszym po-godzinowym zespołem dali radę coś podobnego wydłubać (projekt z tymi szalonymi raportami to był zdalny support w którym byłem tylko podwykonawcą podwykonawców). Klient zgodził się na ActivePivot po tym jak mu oszacowaliśmy, że podobne coś na architekturze która teraz jest już w Apache, czyli Pinot (to siedzi na Helix, Hadoop, etc), zajmie nam z x10 tyle czasu, no i będzie musiał mieć kogoś kto to będzie później ogarniał.
Nie znałem (w sumie do dziś dobrze nie znam) Spark'a, tak z dystansu wydaje mi się to to trochę bardziej nastawione na batch' processing?
Wiem, że w banku zaczęto później używać Hazelcast, w podobnym przypadku (ale mniejszej ilości danych) ludzie w mojej robocie używaja Ignite I Infinispan. Generalnie gdybym miał coś takiego zacząć od nowa to pewnie poszedłbym właśnie w tym kierunku (żeby mieć dane w cache, już załadowane, później na tym odpalać query - dużo JCache systemów wspiera to w miarę dobrze)

Teraz to mnie zabiłeś liczba wymienionych technologii, nie znam połowy. Koncepcja jest prosta - na potrzeby raportu odpytujesz inny model danych, zoptymalizowany pod zapytania analityczne (a nie transakcyjne). Możesz robić sobie dowolne agregacje offline używając np. Spark i Hive, które orają po danych zrzucanych (w jakiś sposób) na HDFS. Możesz również eksportować dane do innej bazy OLAP.