Jakie są plusy i minusy wykonywania obliczeń w sql vs. w aplikacji

Pozwól, że użyję metafory: jeśli chcesz kupić złoty naszyjnik w Paryżu, złotnik może usiąść w Kapsztadzie lub Paryżu, to kwestia umiejętności i gustu. Ale nigdy nie wysyłasz ton rudy złota z Południowej Afryki do Francji. Ruda jest przetwarzana w miejscu wydobycia (lub przynajmniej w ogólnym obszarze), tylko złoto jest wysyłane. To samo powinno dotyczyć aplikacji i baz danych.

Jeśli chodzi o PostgreSQL, możesz zrobić prawie wszystko na serwer, całkiem sprawnie. RDBMS wyróżnia się przy złożonych zapytaniach. Dla potrzeb proceduralnych możesz wybierać spośród wielu języków skryptowych po stronie serwera : TCL, python, perl i wiele innych. Najczęściej jednak używam PL / pgSQL .

Najgorszy scenariusz to wielokrotne przechodzenie do serwera dla każdego rzędu większego zestawu. (To byłoby jak wysyłanie jednej tony rudy na raz.)

Drugi w linii , jeśli wyślesz kaskadę zapytań, każde w zależności na jednym przed, podczas gdy wszystko to może być wykonane w jednym zapytaniu lub procedury na serwerze. (To tak, jakby wysyłać złoto i każdy z klejnotów oddzielnym statkiem, kolejno.)

Przechodzenie tam i z powrotem między aplikacją a serwerem jest drogie. Dla serwera i klienta. Spróbuj to ograniczyć, a wygrasz-ergo: użyj procedur po stronie serwera i / lub wyrafinowanego SQL tam, gdzie jest to konieczne.

Właśnie zakończyliśmy projekt, w którym spakowaliśmy prawie wszystkie złożone zapytania do Funkcje Postgres. Aplikacja przekazuje parametry i pobiera potrzebne zbiory danych. Szybki, czysty, prosty( dla programisty aplikacji), I/O zredukowane do minimum ... Błyszczący Naszyjnik o niskiej emisji dwutlenku węgla.

W tym przypadku jesteś prawdopodobnie nieco lepiej wykonywać obliczenia w SQL, ponieważ silnik bazy danych prawdopodobnie będzie miał bardziej wydajne procedury arytmetyki dziesiętnej niż Java.

Ogólnie rzecz biorąc, dla obliczeń poziomu wiersza nie ma dużej różnicy.

Gdzie robi różnicę to:

• obliczenia zbiorcze, takie jak SUM(), AVG(),MIN(), MAX() tutaj silnik bazy danych będzie o rząd wielkości szybszy niż Java wdrożenie.
• wszędzie tam, gdzie obliczenia są używane do filtrowania wierszy. Filtrowanie w DB jest o wiele bardziej wydajne niż odczyt wiersza, a następnie jego odrzucenie.

Nie ma czerni / bieli w odniesieniu do tego, jakie części logiki dostępu do danych powinny być wykonywane w SQL i jakie części powinny być wykonywane w Twojej aplikacji. Podoba mi się sformułowanie Marka Gravella , rozróżniające

• obliczenia złożone
• obliczenia intensywne na dane

Siła i ekspresja SQL jest mocno niedoceniana. Od czasu wprowadzenia funkcji okien , Wiele nie ściśle zorientowanych obliczeń może być wykonywane bardzo łatwo i elegancko w bazie danych.

Należy zawsze przestrzegać trzech zasad, niezależnie od ogólnej architektury aplikacji:

• zachowaj ilość danych przesyłanych między bazą danych a aplikacją slim (na rzecz obliczania danych w DB)
• zachowaj ilość danych załadowanych z dysku przez bazę danych slim (na rzecz optymalizacji instrukcji bazy danych, aby uniknąć niepotrzebnego dostępu do danych)
• nie wciskaj bazy danych do jego ograniczeń CPU ze złożonymi, równoległymi obliczeniami (na rzecz pobierania danych do pamięci aplikacji i wykonywania tam obliczeń)

Z mojego doświadczenia, z przyzwoitym DBA i przyzwoitą wiedzą na temat przyzwoitej bazy danych, nie napotkasz ograniczeń CPU DBs bardzo szybko.

Kilka dalszych lektur, gdzie te rzeczy są wyjaśnione:

• 10 często popełniane błędy programistów Java podczas pisania SQL
• 10 Najczęstsze Błędy Java Programiści robią podczas pisania SQL

Ogólnie rzecz biorąc rób rzeczy w SQL, jeśli są szanse, że również inne moduły lub komponenty w tych samych lub innych projektach będą musiały uzyskać te wyniki. operacja atomowa po stronie serwera jest również lepsza, ponieważ wystarczy wywołać zapisany proc z dowolnego narzędzia do zarządzania db, aby uzyskać ostateczne wartości bez dalszego przetwarzania.

W niektórych przypadkach nie ma to zastosowania, ale kiedy to ma sens. również w ogóle db box ma najlepszy sprzęt i wydajność.

Jeśli piszesz na ORM lub piszesz zwykłe aplikacje o niskiej wydajności, użyj dowolnego wzorca upraszcza aplikację. Jeśli piszesz aplikację o wysokiej wydajności i starannie myślisz o skali, wygrasz, przenosząc przetwarzanie do danych. Zdecydowanie opowiadam się za przeniesieniem przetwarzania do danych.

Pomyślmy o tym w dwóch krokach: (1) OLTP (mała liczba rekordów) transakcji. (2) OLAP (długie skany wielu rekordów).

W przypadku OLTP, jeśli chcesz aby być szybkim (10K-100k transakcji na sekundę), musisz usunąć blokadę, blokadę i blokadę martwą z bazy danych. Oznacza to, że musisz wyeliminować długie stragany w transakcjach: podróże w obie strony od klienta do DB, aby przenieść przetwarzanie do klienta, są jednym z takich długich straganów. Nie możesz mieć długotrwałych transakcji (do odczytu/aktualizacji) i mieć bardzo wysoką przepustowość.

Spójrzmy na OLAP - w tym przypadku powinno być oczywiste, że przeciąganie ewentualnie terrabajtów danych z powrotem do aplikacji jest okropnym pomysłem. Systemy te są zbudowane specjalnie po to, aby działać wyjątkowo wydajnie wobec skompresowanych, wstępnie zorganizowanych danych kolumnowych. Nowoczesne systemy OLAP również skalują się poziomo i mają zaawansowane planisty zapytań, które rozpraszają pracę poziomo (wewnętrzne przenoszenie przetwarzania do danych).

To, czy wykonać obliczenia na front-endzie, czy na back-endzie, jest bardzo dużo decyduje, czy możemy określić nasz cel we wdrożeniu biznesowym. Czasami kod java może działać lepiej niż kod sql zarówno dobrze napisany, jak i może być odwrotnie. Ale nadal, jeśli mylić można spróbować określić pierwszy -

1. Jeśli możesz osiągnąć coś prostego za pomocą bazy danych SQL to lepiej idź na to, ponieważ db będzie działać znacznie lepiej i wykonaj obliczenia tam, a następnie z wynikiem aport. Jeśli jednak rzeczywiste obliczenia wymagają zbyt wielu obliczeń od Tu i tam rzeczy, możesz przejść z kodem aplikacji. Dlaczego? Ponieważ scenariusz jest jak zapętlenie w większości przypadków nie są najlepiej obsługiwane przez sql, gdzie języki front end są lepiej zaprojektowane do tych rzeczy.
2. w przypadku, gdy podobne obliczenia są wymagane z wielu miejsc, to oczywiście umieszczenie kodu obliczeniowego na końcu db będzie lepiej trzymać rzeczy w tym samym miejscu.
3. Jeśli istnieje wiele obliczenia, które należy wykonać, aby osiągnąć końcowy wynik za pomocą wielu różnych zapytań, a następnie przejść do końca db, ponieważ można umieścić ten sam kod w procedurze składowanej, aby wykonać lepsze wyniki niż pobieranie wyników z backendu, a następnie obliczanie ich na początku.

Istnieje wiele innych aspektów, które możesz przemyśleć, zanim zdecydujesz, gdzie umieścić kod. Jedna percepcja jest całkowicie błędna-wszystko można zrobić najlepiej w Javie (kod aplikacji) i/lub wszystko najlepiej zrobić przez db (sql kod).

Form a performance point of view: jest to bardzo prosta operacja arytmetyczna, która prawie na pewno może być wykonana znacznie szybciej niż pobieranie danych z dysków znajdujących się pod bazą danych. Ponadto obliczanie wartości w klauzuli where prawdopodobnie będzie bardzo szybkie w dowolnym środowisku uruchomieniowym. Podsumowując, wąskim gardłem powinien być dysk IO, a nie obliczanie wartości.

Jeśli chodzi o czytelność, myślę, że jeśli używasz ORM, powinieneś to zrobić w środowisku serwera aplikacji, ponieważ ORM pozwoli Ci pracować z bazowymi danymi bardzo łatwo, używając operacji opartych na zestawach. Jeśli i tak zamierzasz pisać surowy SQL, nie ma nic złego w wykonywaniu obliczeń, Twój SQL będzie również wyglądał trochę ładniej i łatwiejszy do odczytania, jeśli zostanie poprawnie sformatowany.

Dla mnie najbardziej liczy się czas dewelopera.

Napisz zapytanie SQL. Jeśli jest zbyt wolny lub DB staje się wąskim gardłem, przemyśl to ponownie. Do tego czasu będziesz mógł porównać oba podejścia i podjąć decyzję w oparciu o rzeczywiste dane istotne dla Twojej konfiguracji (sprzęt i dowolny stos, na którym się znajdujesz).

Nie wierzę, że różnice w wydajności można uzasadnić bez konkretnych przykładów i benchmarków, ale mam inne ujęcie:

Co można lepiej utrzymać? Na przykład możesz zmienić front-end z Javy Na Flash, HTML5, C++ lub coś innego. Ogromna liczba programów przeszła taką zmianę, a nawet istnieje w więcej niż jednym języku, ponieważ muszą pracować na wielu urządzeniach.

Nawet jeśli masz odpowiedni środek layer (z podanego przykładu wydaje się, że tak nie jest), ta warstwa może się zmienić, a JBoss może stać się Ruby / Rails.

Z drugiej strony, jest mało prawdopodobne, że zastąpisz backend SQL czymś, co nie jest relacyjnym DB z SQL i nawet jeśli to zrobisz, będziesz musiał przepisać front-end od zera, więc sprawa jest dyskusyjna.

Moim pomysłem jest to, że jeśli wykonasz obliczenia w DB, znacznie łatwiej będzie napisać drugą front-end lub środkową warstwę później, bo nie musisz wszystkiego ponownie wdrażać. W praktyce jednak myślę, że "Gdzie mogę to zrobić z kodem, który ludzie zrozumieją" jest najważniejszym czynnikiem.

Aby uprościć odpowiedź, należy spojrzeć na równoważenie obciążenia. Chcesz umieścić ładunek tam, gdzie masz największą pojemność (jeśli ma to jakiś sens). W większości systemów jest to serwer SQL, który szybko staje się wąskim gardłem, więc prawdopodobnie odpowiedź brzmi: nie chcesz, aby SQL robił jedną uncję pracy więcej niż musi.

Również w większości architektur to serwer(y) SQL tworzą rdzeń systemu i systemy zewnętrzne, które są dodawane.

Ale powyższa matematyka jest tak trywialne, że jeśli nie pchasz systemu do granic możliwości, najlepszym miejscem do umieszczenia jest to, gdzie chcesz go umieścić. Gdyby matematyka nie była trywialna, taka jak obliczanie sin / cos / tan dla powiedzmy obliczenia odległości, wysiłek mógłby stać się nietrywialny i wymagać starannego planowania i testowania.

Inne odpowiedzi na to pytanie są ciekawe. O dziwo, nikt nie odpowiedział na twoje pytanie. zastanawiasz się:

[[5]] czy lepiej wrzucić do kwerendy? Nie sądzę, że obsada aby dodać cokolwiek w zapytaniu.
1. czy lepiej użyć now () w zapytaniu? Wolałbym przekazać daty do zapytania, zamiast je obliczać w zapytaniu.

Więcej informacji: Dla pytania pierwszego chcesz mieć pewność, że agregowanie ułamków roboty budowlane bez błędów zaokrąglania. Myślę, że cyfra 19,2 jest rozsądna dla pieniędzy, a w drugim przypadku liczby całkowite są OK. Korzystanie z platformy dla pieniędzy jest złe z tego powodu.

W pytaniu drugim lubię mieć pełną kontrolę jako programista nad tym, co Data jest uważana za "Teraz". Może być trudno napisać automatyczną jednostkę testuje przy użyciu funkcji takich jak now (). Również, gdy masz dłuższy skrypt transakcji może być dobrze ustawić zmienną równą now () i użyć zmiennej tak że wszystkie logika używa dokładnie tej samej wartości.

Pozwól mi wziąć prawdziwy przykład, aby odpowiedzieć na to pytanie]} Aby to zrobić, musiałem obliczyć ważoną średnią ruchomą na moich danych ohlc, mam około 134000 świec z symbolem dla każdej z nich

1. Option 1 Do it in Python / Node etc etc
2. Opcja 2 Zrób to w SQL!

Który jest lepszy?

• gdybym miał to zrobić w Pythonie, zasadniczo, musiałbym pobrać wszystkie zapisane rekordy w najgorszym przypadku, wykonać obliczenia i zapisać wszystko z powrotem, co moim zdaniem jest ogromnym marnotrawstwem IO
Na wykresie dziennym widać, że cena odbija się od poziomu 0.00000000000, co oznacza, że cena nie jest zbyt stabilna. dobra opinia w moim znaku
• W SQL, wszystko, co muszę zrobić, to prawdopodobnie napisać wyzwalacz, który oblicza i przechowuje wszystko, więc wystarczy pobrać ostateczne wartości WMA dla każdej pary co jakiś czas i to jest o wiele więcej efficient

Wymagania

• gdybym miał obliczyć WMA dla każdej świecy i zapisać ją, zrobiłbym to na Pythonie
• ale ponieważ potrzebuję tylko ostatniej wartości, SQL jest znacznie szybszy niż Python

Aby cię zachęcić, jest to wersja Pythona do wykonania ważonej średniej ruchomej

WMA done through code

import psycopg2
import psycopg2.extras
from talib import func
import timeit
import numpy as np
with psycopg2.connect('dbname=xyz user=xyz') as conn:
with conn.cursor() as cur:
t0 = timeit.default_timer()
cur.execute('select distinct symbol from ohlc_900 order by symbol')
for symbol in cur.fetchall():
cur.execute('select c from ohlc_900 where symbol = %s order by ts', symbol)
ohlc = np.array(cur.fetchall(), dtype = ([('c', 'f8')]))
wma = func.WMA(ohlc['c'], 10)
# print(*symbol, wma[-1])
print(timeit.default_timer() - t0)
conn.close()

WMA przez SQL

"""
if the period is 10
then we need 9 previous candles or 15 x 9 = 135 mins on the interval department
we also need to start counting at row number - (count in that group - 10)
For example if AAPL had 134 coins and current row number was 125
weight at that row will be weight = 125 - (134 - 10) = 1
10 period WMA calculations
Row no Weight c
125 1
126 2
127 3
128 4
129 5
130 6
131 7
132 8
133 9
134 10
"""
query2 = """
WITH
condition(sym, maxts, cnt) as (
select symbol, max(ts), count(symbol) from ohlc_900 group by symbol
),
cte as (
select symbol, ts,
case when cnt >= 10 and ts >= maxts - interval '135 mins'
then (row_number() over (partition by symbol order by ts) - (cnt - 10)) * c
else null
end as weighted_close
from ohlc_900
INNER JOIN condition
ON symbol = sym
WINDOW
w as (partition by symbol order by ts rows between 9 preceding and current row)
)
select symbol, sum(weighted_close)/55 as wma
from cte
WHERE weighted_close is NOT NULL
GROUP by symbol ORDER BY symbol
"""
with psycopg2.connect('dbname=xyz user=xyz') as conn:
with conn.cursor() as cur:
t0 = timeit.default_timer()
cur.execute(query2)
# for i in cur.fetchall():
# print(*i)
print(timeit.default_timer() - t0)
conn.close()

Wierz lub nie, zapytanie działa szybciej niż czysta wersja Pythona robi ważoną średnią ruchomą!!! poszedłem krok po kroku do pisania tego zapytania więc trzymaj się tam i będzie dobrze

Speed

0.42141127300055814 sekund Python

0.23801879299935536 seconds SQL

Mam 134000 fałszywych rekordów OHLC w mojej bazie danych podzielonych między 1000 zapasów, więc jest to przykład, gdzie SQL może przewyższyć swój serwer aplikacji