SQL과 응용 프로그램의 계산 수행의 장단점은 무엇입니까?

SQL과 응용 프로그램의 계산 수행의 장단점은 무엇입니까?

---

shopkeeper 표에는 다음과 같은 필드가 있습니다.

id (bigint),amount (numeric(19,2)),createddate (timestamp)

위의 테이블이 있다고 가정 해 봅시다. 어제 레코드를 가져 와서 센트로 인쇄하여 보고서를 생성하고 싶습니다.

한 가지 방법은 내 Java 응용 프로그램에서 계산을 수행하고 간단한 쿼리를 실행하는 것입니다.

Date previousDate ;// $1 calculate in application

Date todayDate;// $2 calculate in application

select amount where createddate between $1 and $2 

그런 다음 레코드를 반복하고 Java 응용 프로그램에서 금액을 센트로 변환하고 보고서를 생성합니다.

다른 방법은 SQL 쿼리 자체에서 계산을 수행하는 것과 같습니다.

select cast(amount * 100 as int) as "Cents"
from shopkeeper  where createddate  between date_trunc('day', now()) - interval '1 day'  and  date_trunc('day', now())

그런 다음 레코드를 반복하고 보고서를 생성하십시오.

한 가지 방법으로, 모든 처리는 Java 응용 프로그램에서 수행되며 간단한 쿼리가 발생합니다. 다른 경우에는 모든 변환 및 계산이 SQL 쿼리에서 수행됩니다.

위의 사용 사례는 예일 뿐이며 실제 시나리오에서는 테이블에 유사한 종류의 처리가 필요한 많은 열이있을 수 있습니다.

성능 및 기타 측면에서 어떤 접근 방식이 더 나은지, 왜 그런지 말해 주시겠습니까?

---

그것은 많은 요인에 달려 있지만 가장 중요한 것은 다음과 같습니다.

• 계산의 복잡성은 (그 규모 때문에, 응용 프로그램 서버에 복잡한 사각 사각을하고 선호 밖으로 오히려 확장되는 DB 서버보다 최대 )
• 데이터 양 (많은 데이터에 액세스 / 집계해야하는 경우 db 서버에서 수행하면 대역폭이 절약되고 인덱스 내에서 집계를 수행 할 수있는 경우 디스크 io)
• 편의성 (SQL은 복잡한 작업에 가장 적합한 언어는 아닙니다. 특히 절차 작업에는 적합하지 않지만 세트 기반 작업에는 적합하지만 오류 처리는 크지 않습니다)

당신이 경우 언제나처럼, 할 열과 행을, 응용 프로그램 서버에 데이터를 다시 가져 최소화 당신의 이점에있을 것입니다. 쿼리를 조정하고 적절하게 색인을 작성하면 두 시나리오에 도움이됩니다.

다시 참고하십시오 :

그런 다음 레코드를 반복합니다.

레코드를 반복 하는 것은 SQL에서 항상 잘못된 일입니다. 집합 기반 작업을 작성하는 것이 좋습니다.

일반적으로 데이터베이스 작업을 최소 "이 데이터 저장,이 데이터 가져 오기"로 유지하는 것이 좋습니다. 그러나 서버의 우아한 쿼리로 많은 대역폭을 절약 할 수있는 시나리오의 예가 항상 있습니다.

또한 계산 비용이 비싼 경우 어딘가에 캐시 할 수 있습니까?

정확한 "더 나은" 것을 원한다면 ; 두 가지 방법으로 코딩하고 비교하십시오 (둘 중 첫 번째 초안은 100 % 조정되지 않았을 것입니다). 그러나 일반적인 사용법을 고려하십시오. 실제로 실제로 한 번에 5 회 (별도로) 호출되는 경우 다음을 시뮬레이트합니다. 단 하나의 "1 대 1"을 비교하지 마십시오.

---

은유를 사용해 봅시다. 파리에서 황금 목걸이 를 사려면 금세 공인이 케이프 타운이나 파리에 앉아있을 수 있습니다. 그것은 기술과 맛의 문제입니다. 그러나 당신은 그것을 위해 남아공에서 프랑스로 금광을 절대로 운송 하지 않을 것 입니다. 광석은 채굴 현장 (또는 적어도 일반 지역)에서 처리되며 금만 배송됩니다. 앱과 데이터베이스도 마찬가지입니다.

지금까지로 PostgreSQL의이 우려, 당신은 아주 효율적으로, 서버에서 거의 모든 작업을 수행 할 수 있습니다. RDBMS는 복잡한 쿼리에 탁월합니다. 절차 상 필요에 따라 tcl, python, perl 등 다양한 서버 측 스크립트 언어 중에서 선택할 수 있습니다 . 대부분 PL / pgSQL을 사용 합니다.

최악의 시나리오는 더 큰 세트의 모든 단일 행마다 반복적으로 서버로 이동하는 것입니다. (한 번에 1 톤의 광석을 운송하는 것과 같습니다.)

두 번째 인라인 은 이전의 쿼리에 따라 일련의 쿼리를 보내면 모든 쿼리는 서버에서 하나의 쿼리 또는 절차로 수행 할 수 있습니다. (즉, 금과 각 보석을 별도의 선박으로 순차적으로 운송하는 것과 같습니다.)

앱과 서버 간을 이동하는 데 많은 비용이 듭니다. 서버 및 클라이언트 용. 이를 줄이려고하면 서버 측 프로 시저 및 / 또는 필요한 경우 정교한 SQL을 사용하는 것이 좋습니다.

우리는 거의 모든 복잡한 쿼리를 Postgres 함수로 압축 한 프로젝트를 마쳤습니다. 앱은 매개 변수를 넘겨 받아 필요한 데이터 세트를 얻습니다. 빠르고, 깨끗하고, 단순하고 (앱 개발자를 위해) I / O는 최소한으로 감소했습니다. 탄소 발자국이 적은 반짝이는 목걸이입니다.

---

이 경우는 아마 데이터베이스 엔진이 자바보다 더 효율적 소수점 연산 루틴을 가질 가능성이 높습니다로 약간 더 나은 SQL에서 계산을 끕니다.

일반적으로 행 수준 계산의 경우 큰 차이가 없습니다.

차이가 나는 곳은 다음과 같습니다.

• SUM (), AVG (), MIN (), MAX ()와 같은 집계 계산은 데이터베이스 엔진이 Java 구현보다 훨씬 빠릅니다.
• 어디서나 계산은 행을 필터링하는 데 사용됩니다. DB에서 필터링하는 것은 행을 읽고 버리는 것보다 훨씬 효율적입니다.

---

SQL에서 수행해야 할 데이터 액세스 논리 부분과 응용 프로그램에서 수행해야 할 부분에 대해서는 흑백이 없습니다. 나는 Mark Gravell의 표현을 좋아합니다.

• 복잡한 계산
• 데이터 집약적 인 계산

SQL의 강력 함과 표현력은 과소 평가되었습니다. 윈도우 함수 가 도입 되었으므로 데이터베이스에서 엄격하지 않은 설정 지향 계산을 매우 쉽고 우아하게 수행 할 수 있습니다.

전반적인 애플리케이션 아키텍처에 관계없이 항상 세 가지 규칙을 따라야합니다.

• 데이터베이스와 애플리케이션간에 전송되는 데이터의 양을 줄이십시오 (DB의 데이터 계산에 유리함)
• 데이터베이스에 의해 디스크에서로드 된 데이터의 양을 줄이십시오 (데이터베이스가 불필요한 데이터 액세스를 피하도록 명령문을 최적화하도록 유리함)
• 복잡한 동시 계산을 통해 데이터베이스를 CPU 한도까지 푸시하지 마십시오 (데이터를 응용 프로그램 메모리로 가져 와서 계산 수행).

내 경험상, 적절한 DBA와 괜찮은 데이터베이스에 대한 지식이 있으면 DB CPU 한도에 빨리 도달하지 못할 것입니다.

이 부분들을 설명하는 추가 자료 :

• Java 개발자가 SQL을 작성할 때 흔히 저지르는 10 가지 실수
• Java 개발자가 SQL을 작성할 때 자주 범하는 실수 10 가지

---

일반적으로 같은 프로젝트 또는 다른 프로젝트의 다른 모듈이나 구성 요소가 이러한 결과를 얻어야 할 가능성이있는 경우 SQL로 작업을 수행하십시오. 추가 작업없이 최종 값을 얻기 위해 모든 db 관리 도구에서 저장된 proc을 호출하면되기 때문에 서버 측에서 원자 적 작업이 더 좋습니다.

경우에 따라 적용되지 않지만 의미가있는 경우에는 적용됩니다. 또한 일반적으로 db 상자는 최상의 하드웨어 및 성능을 갖습니다.

---

ORM을 기반으로 작성하거나 캐주얼 한 저 성능 응용 프로그램을 작성하는 경우 응용 프로그램을 단순화하는 패턴을 사용하십시오. 고성능 애플리케이션을 작성하고 스케일에 대해 신중하게 생각하는 경우 처리를 데이터로 이동하여 이길 수 있습니다. 처리를 데이터로 옮기는 것을 강력히 권합니다.

Let's think about this in two steps: (1) OLTP (small number of record) transactions. (2) OLAP (long scans of many records).

In the OLTP case, if you want to be fast (10k - 100k transactions per second), you must remove latch, lock and dead lock contention from the database. This means that you need to eliminate long stalls in transactions: round trips from client to DB to move processing to the client are one such long stall. You can't have long lived transactions (to make read/update atomic) and have very high throughput.

Re: horizontal scaling. Modern databases scale horizontally. Those systems implement HA and fault tolerance already. Leverage that and try to simplify your application space.

Let's look at OLAP -- in this case it should be obvious that dragging possibly terrabytes of data back to the application is a horrible idea. These systems are built specifically to operate extremely efficiently against compressed, pre-organized columnar data. Modern OLAP systems also scale horizontally and have sophisticated query planners that disperse work horizontally (internally moving processing to data).

---

Whether to perform calculations at the front end or at the backend is very much decided if we can determine our goal in the business implementation. At time java code might perform better than a sql code both well written or it might be vice-versa. But still if confused you can try to determine first -

1. If you can achieve something straightforward via database sql then better go for it as db will perform much better and do computations there and then with the result fetch. However if the actual computation requires too much calculation from here and there stuff then you can go with the application code. Why? Because scenario's like looping in most cases are not best handled by sql wherease front end languages are better designed for these things.
2. In case similar calculation is required from many places then obviously placing the calculation code at the db end will be better to keep things at the same place.
3. If there are lots of calculations to be done to attain the final result via many different queries then also go for db end as you can place the same code in a stored procedure to perform better than retrieving results from backend and then computing them at the front end.

There are many other aspects which you can think before you decide where to place the code. One perception is totally wrong - Everything can be done best in Java (app code) and/or everything is best to be done by the db (sql code).

---

Form a performance point of view: This is a very simple arithmetic operation which almost certainly can be performed much faster than actually fetching the data from the disks that underly the database. Also, calculating the values in the where clause is likely to be very fast on any runtime. In summary, the bottleneck should be disk IO, not the computation of the values.

As per readability, I think if you use an ORM you should do it in your app server environment, because the ORM will let you work with the underlying data very easily, using set based operations. If you are going to write raw SQL anyway, there's nothing wrong with doing the computation there, Your SQL would also look a little nicer and easier to read if formatted properly.

---

Crucially, "performance" isn't defined.

The one that matters to me the most is developer time.

Write the SQL query. If it's too slow or the DB becomes a bottleneck, then reconsider. By that time, you'll be able to benchmark the two approaches and make your decision based on real data relevant to your setup (hardware and whatever stack you're on).

---

I don't believe the performance differences can be reasoned about without specific examples and benchmarks, but I have another take:

Which can you maintain better? For example, you might want to switch your front-end from Java to Flash, or HTML5, or C++, or something else. A vast number of programs have gone through such a change, or even exist in more than one language to begin with, because they need to work on multiple devices.

Even if you have a proper middle layer (from the example given, it seems like that's not the case), that layer might change and JBoss might become Ruby/Rails.

On the other hand, it is unlikely that you will replace the SQL-backend with something that's not a relational DB with SQL and even if you do, you will have to rewrite the front-end from scratch anyway, so the point is moot.

My idea is that if you do calculations in the DB, it will be much easier to write a second front-end or middle-layer later, because you don't have to re-implement everything. In practise however, I think "where can I do this with code that people will understand" is the most important factor.

---

To simplify how to answer this would be to look at load balancing. You want to put the load where you have the most capacity (if it makes any sense). In most systems it is the SQL server that quickly becomes a bottleneck so the probably answer is that you don't want SQL doing one ounce of work more than it has to.

Also in most architectures it is the SQL server(s) that make up the core of the system and outside systems that get added on.

But the math above is so trivial that unless you are pushing your system to the limit the best place to put it is where you want to put it. If the math were not trivial such as calculating sin/cos/tan for say a distance calculation then the effort might become non-trivial and require careful planning and testing.

---

The other answers to this question are interesting. Surprisingly, no one has answered your question. You are wondering:

1. Is it better to cast to Cents in the query? I don’t think the cast to cents adds anything in your query.
2. Is it better to use now() in the query? I would prefer to pass dates into the query instead of calculating them in the query.

More info: For question one you want to be sure that aggregating the fractions works without rounding errors. I think numeric 19,2 is reasonable for money and in the second case the integers are OK. Using a float for money is wrong for this reason.

For question two, I like to have full control as a programmer of what date is considered “now”. It can be hard to write automatic unit tests when using functions like now(). Also, when you have a longer transaction script it can be good to set a variable equal to now() and use the variable so that all of the logic uses the exact same value.

---

Let me take a real example to address this question

I needed to calculate a weighted moving average on my ohlc data, I have about 134000 candles with a symbol for each to do so

1. Option 1 Do it in Python/Node etc etc
2. Option 2 Do it in SQL itself!

Which one is better?

• If I had to do this in Python, essentially, I would have to fetch all stored records at the worst, case, perform the computation and save everything back which in my opinion is a huge wastage of IO
• Weighted moving average changes everytime you get a new candle meaning I would be doing massive amounts of IO at regular intervals which is not a good opinion in my sign
• In SQL, all I have to do is probably write a trigger that computes and stores everything so only need to fetch the final WMA values for each pair every now and then and that is so much more efficient

Requirements

• If I had to calculate WMA for every candle and store it, I would do it on Python
• But since I only need the last value, SQL is much faster than Python

To give you some encouragement, this is the Python version to do a weighted moving average

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 Through 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()

Believe it or not, the query runs faster than the Pure Python version of doing a WEIGHTED MOVING AVERAGE!!! I went step by step into writing that query so hang in there and you ll do just fine

Speed

0.42141127300055814 seconds Python

0.23801879299935536 seconds SQL

I have 134000 fake OHLC records in my database divided amongst a 1000 stocks so that is an example of where SQL can outperform your app server

참고URL : https://stackoverflow.com/questions/7510092/what-are-the-pros-and-cons-of-performing-calculations-in-sql-vs-in-your-applica