사용하시는 어플리케이션에서 sql과 sql의 계산을 실행하는 경우의 장점과 단점은 무엇입니까?

사용하시는 어플리케이션에서 sql과 sql의 계산을 실행하는 경우의 장점과 단점은 무엇입니까?

shopkeeper테이블에는 다음 필드가 있습니다.

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

예를 들어, 위의 표가 있다고 합시다.어제의 기록을 입수해, 금액을 센트로 인쇄해 리포트를 작성하려고 합니다.

한 가지 방법은 자바 어플리케이션에서 계산을 수행하고 간단한 쿼리를 실행하는 것입니다.

Date previousDate ;// $1 calculate in application

Date todayDate;// $2 calculate in application

select amount where createddate between $1 and $2 

레코드를 루프하여 자바 어플리케이션에서 금액을 센트로 변환하여 보고서를 생성합니다.

다른 방법은 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 서버에서 수행하면 대역폭이 절약되고 집계가 인덱스 내에서 가능한 경우 Disk I/O가 절약됩니다.
• 편리성(sql은 복잡한 작업에 최적인 언어는 아닙니다.특히 절차적인 작업에는 적합하지 않습니다만, 세트 베이스의 작업에는 매우 적합합니다.단, 에러 처리는 서투릅니다).

여느 때처럼 데이터를 앱 서버로 가져오면 열과 행을 최소화하는 것이 유리합니다.쿼리가 조정되어 있고 적절히 색인화되어 있는지 확인하면 어느 시나리오에도 도움이 됩니다.

메모 참조:

기록을 뒤져서

SQL에서는 레코드를 루프하는 것이 거의 항상 잘못된 작업입니다. 세트 기반 작업을 쓰는 것이 좋습니다.

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

또한 계산 비용이 많이 드는 경우 어딘가에 캐싱할 수 있습니까?

정확한 "어느 쪽이 좋은가"를 원하는 경우, 양쪽을 코드화하여 비교합니다(어느 쪽이든 초안이 100% 튜닝되지 않을 수 있음).단, 일반적인 사용법을 고려해야 합니다.실제로 동시에 5회(별도) 호출되는 경우에는 시뮬레이션을 실시합니다.단, 1개의 「이것들 중 1개」와「이것들 중 1개」만을 비교하지 말아 주세요.

비유를 들어보자. 파리에서 황금 목걸이를 사고 싶다면 금세공인은 케이프타운이나 파리에 앉아도 된다.그것은 기술과 취향의 문제이다.하지만 당신은 그것을 위해 남아프리카에서 프랑스로 수 톤의 금광석을 운송하지 않을 것이다.광석은 채굴 현장(또는 적어도 일반 지역)에서 가공되고 금만 운송됩니다.앱과 데이터베이스도 마찬가지입니다.

Postgre까지SQL은 서버에서 거의 모든 작업을 매우 효율적으로 수행할 수 있습니다.RDBMS는 복잡한 쿼리에 탁월합니다.절차상 필요에 따라 TCL, python, perl 등 다양한 서버측 스크립트 언어 중에서 선택할 수 있습니다.하지만 주로 PL/pgSQL을 사용합니다.

최악의 시나리오는 더 큰 세트의 모든 행에 대해 서버에 반복적으로 가는 것입니다.(이는 1회에 1톤 또는 1톤을 출하하는 것과 같습니다.)

두 번째로, 캐스케이드 형식의 쿼리를 송신하는 경우, 각각은 이전의 쿼리에 따라 다르지만, 서버상의 1개의 쿼리 또는 프로시저로 모든 것을 실행할 수 있습니다.(이것은 금과 보석 각각을 순차적으로 출하하는 것과 같습니다.)

앱과 서버를 왔다 갔다 하는 것은 비용이 많이 듭니다.서버 및 클라이언트용.이를 줄이려고 노력하면 서버 측 절차 및/또는 필요에 따라 고급 SQL을 사용할 수 있습니다.

Postgres 함수에 거의 모든 복잡한 쿼리를 포함하는 프로젝트를 방금 완료했습니다.앱은 매개 변수를 제공하고 필요한 데이터 세트를 가져옵니다.빠르고 깔끔하며 심플한 (앱 개발자에게) I/O를 최소로 줄임... 탄소 배출량이 적은 반짝이는 목걸이.

이 경우 데이터베이스 엔진의 10진수 연산 루틴이 Java보다 더 효율적이기 때문에 SQL에서 계산을 수행하는 것이 약간 더 나을 수 있습니다.

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

차이점은 다음과 같습니다.

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

SQL에서 수행해야 하는 데이터 액세스 로직의 부분과 애플리케이션에서 수행해야 하는 부분에 대해서는 흑백이 없습니다.마크 그라벨의 표현이 마음에 들어요

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

SQL의 성능과 표현력은 크게 과소평가되고 있습니다.윈도우 기능이 도입된 이후, 많은 엄격한 설정 지향 계산을 데이터베이스에서 매우 쉽고 우아하게 수행할 수 있게 되었다.

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

• 데이터베이스와 애플리케이션 간에 전송되는 데이터 양을 최소화합니다(DB 내의 데이터를 계산하기 위해).
• 데이터베이스에 의해 디스크에서 로드되는 데이터의 양을 슬림하게 유지(데이터베이스가 불필요한 데이터 액세스를 피하기 위해 문을 최적화하는 데 유리함)
• 복잡한 동시 계산을 통해 데이터베이스를 CPU 제한까지 강요하지 않음(데이터를 애플리케이션 메모리에 가져와 계산 수행에 유리함)

제 경험상으로는 괜찮은 DBA와 제대로 된 데이터베이스에 대한 지식이 있으면 DB의 CPU 한계에 금방 도달하지 못할 것입니다.

다음 사항에 대한 자세한 내용은 다음과 같습니다.

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

일반적으로 동일한 프로젝트 또는 다른 프로젝트의 다른 모듈이나 컴포넌트도 이러한 결과를 얻을 필요가 있는 경우 SQL에서 작업을 수행합니다.아토믹 오퍼레이션이 완료된 서버 측도 db 관리 도구에서 저장된 proc를 호출하기만 하면 더 이상 처리하지 않고 최종 값을 얻을 수 있기 때문에 더 좋습니다.

경우에 따라서는 이것이 적용되지 않는 경우도 있습니다만, 적용되고 있는 경우는 의미가 있습니다.또한 일반적으로 db박스는 최고의 하드웨어와 성능을 갖추고 있습니다.

프런트 엔드로 계산할지 백엔드로 계산할지 비즈니스 구현 목표를 결정할지는 매우 중요합니다.Java 코드는 잘 작성된 SQL 코드보다 성능이 우수하거나 그 반대일 수 있습니다.하지만 혼란스럽다면 먼저 판단해 볼 수 있습니다.

1. 데이터베이스 sql을 통해 간단한 작업을 수행할 수 있다면 db가 훨씬 더 나은 성능을 발휘하고 그곳에서 계산을 수행한 후 결과를 가져올 수 있으므로 이를 수행하는 것이 좋습니다.하지만 실제 계산에 너무 많은 계산이 필요한 경우 애플리케이션 코드를 사용할 수 있습니다. 왜일까요?대부분의 경우 루핑과 같은 시나리오는 이러한 용도로 프런트 엔드 언어가 더 잘 설계되어 있는 sql에 의해 가장 잘 처리되지 않기 때문입니다.
2. 여러 곳에서 같은 계산이 필요한 경우 db 엔드에 계산 코드를 배치하여 같은 장소에 보관하는 것이 좋습니다.
3. 다양한 쿼리를 통해 최종 결과를 얻기 위해 많은 계산이 필요한 경우 db end를 선택합니다.같은 코드를 저장 프로시저에 배치하면 백엔드에서 결과를 가져와 프런트 엔드로 계산하는 것보다 더 나은 성능을 얻을 수 있습니다.

코드를 어디에 배치할지를 결정하기 전에 생각할 수 있는 다른 많은 측면이 있습니다.하나의 인식은 완전히 틀렸습니다 - 모든 것은 Java(앱 코드)에서 가장 잘 수행될 수 있으며/또는 db(sql 코드)에서 가장 잘 수행될 수 있습니다.

ORM 위에 글을 쓰거나 일상적인 저성능 애플리케이션을 쓰려면 응용 프로그램을 단순화하는 어떤 패턴이든 사용하십시오.고성능 어플리케이션을 작성하고 규모를 신중하게 생각하고 있다면 프로세싱을 데이터로 이행하여 성공을 거둘 수 있습니다.저는 처리를 데이터로 옮기는 것을 강력히 지지합니다.

이것에 대해, (1) OLTP(소수의 레코드) 트랜잭션과 (2) OLAP(다수의 레코드의 롱 스캔)의 2개의 순서로 생각해 봅시다.

OLTP의 경우 고속화(초당 10,000 ~100,000 트랜잭션)를 원하는 경우 데이터베이스에서 래치, 잠금 및 데드록 경합을 삭제해야 합니다.즉, 트랜잭션에서 장시간 정지 상태를 해소할 필요가 있습니다.클라이언트에서 DB로의 처리를 이동하기 위한 왕복은 매우 긴 정지 상태 중 하나입니다.(읽기/업데이트를 위해) 트랜잭션이 오래 지속될 수 없으며 처리량이 매우 높습니다.

참조: 수평 스케일링.최신 데이터베이스는 수평으로 확장됩니다.이러한 시스템은 HA 및 Fault Tolerance를 이미 구현하고 있습니다.이를 활용하여 애플리케이션 공간을 간소화하십시오.

OLAP에 대해서 생각해 봅시다.이 경우 테라바이트 단위의 데이터를 어플리케이션으로 드래그하는 것은 매우 어려운 생각입니다.이러한 시스템은 압축된 미리 구성된 기둥형 데이터에 대해 매우 효율적으로 작동하도록 특별히 구축되었습니다.또한 최신 OLAP 시스템은 수평으로 확장되며 수평으로 작업을 분산하는 정교한 쿼리 플래너를 갖추고 있습니다(내부적으로 프로세싱을 데이터로 이동).

퍼포먼스 관점 형성:이것은 매우 간단한 산술 연산이며, 데이터베이스의 기반이 되는 디스크에서 실제로 데이터를 가져오는 것보다 훨씬 빠르게 수행할 수 있습니다.또한 where 구의 값은 어떤 런타임에서도 매우 빠르게 계산될 수 있습니다.요약하면 병목현상은 값 계산이 아니라 디스크 IO여야 합니다.

읽기 쉽도록 ORM을 사용하는 경우 앱 서버 환경에서 ORM을 사용하는 것이 좋다고 생각합니다. ORM을 사용하면 설정 기반 작업을 사용하여 기본 데이터를 매우 쉽게 작업할 수 있기 때문입니다.그래도 raw SQL을 쓰는 경우 계산은 문제 없습니다.또한 올바르게 포맷하면 SQL이 보기 쉽고 읽기 쉬워집니다.

중요한 것은 "퍼포먼스"가 정의되어 있지 않다는 것입니다.

나에게 가장 중요한 것은 개발자의 시간이다.

SQL 쿼리를 작성합니다.속도가 너무 느리거나 DB가 병목현상이 되면 다시 고려해 보십시오.그때가 되면, 2개의 어프로치를 벤치마킹 해, 셋업과 관련된 실제의 데이터(하드웨어와 사용하고 있는 스택)에 근거해 결정을 내릴 수 있게 됩니다.

구체적인 예시와 벤치마크가 없으면 성능 차이를 설명할 수 없다고 생각합니다만, 또 다른 견해가 있습니다.

어떤 것을 더 잘 유지할 수 있습니까?예를 들어 프런트엔드를 Java에서 Flash, HTML5, C++ 등으로 전환할 수 있습니다.다수의 프로그램이 이러한 변화를 거쳤거나 여러 개의 언어로 구성되어 있습니다.여러 기기에서 작업할 필요가 있기 때문입니다.

적절한 중간 레이어가 있어도(이 예에서는 그렇지 않은 것 같습니다), 그 레이어가 변경되어 JBoss가 Ruby/Rails가 될 수 있습니다.

반면 SQL 백엔드를 SQL과의 관계형 DB가 아닌 것으로 대체할 가능성은 낮습니다. 교체하더라도 프런트엔드를 처음부터 다시 작성해야 하기 때문에 요점은 명확하지 않습니다.

DB에서 계산을 하면 모든 것을 다시 구현할 필요가 없기 때문에 나중에 두 번째 프런트 엔드 또는 미들 레이어를 작성하는 것이 훨씬 쉬워질 것이라고 생각합니다.다만, 실천에서는, 「사람들이 이해할 수 있는 코드로 이것을 어디에서 할 수 있는가」가 가장 중요한 요소라고 생각합니다.

이에 대한 답을 간단하게 하기 위해서는 로드밸런싱을 검토해야 합니다.용량이 가장 큰 곳에 부하를 배치하는 것이 바람직합니다(해당되는 경우).대부분의 시스템에서 병목현상이 발생하는 것은 SQL 서버이기 때문에 SQL이 필요 이상으로 많은 작업을 수행하지 않도록 하는 것이 가장 좋습니다.

또한 대부분의 아키텍처에서 시스템의 핵심을 구성하는 것은 SQL 서버이며 추가되는 외부 시스템도 있습니다.

그러나 위의 계산은 너무 사소한 것이므로 시스템을 한계까지 밀어붙이지 않는 한 시스템을 배치하는 것이 가장 좋은 방법입니다.거리 계산을 위한 sin/cos/tan 계산과 같이 수학이 사소한 것이 아니라면, 그 노력은 단순하지 않고 신중한 계획과 테스트가 필요할 수 있다.

이 질문에 대한 다른 답변들은 흥미롭다.놀랍게도 아무도 당신의 질문에 대답하지 않았습니다.궁금하신 점:

1. 질문에서 Cents에 캐스팅하는 것이 더 나을까요?내 생각엔 캐스트들이 당신의 질문에 아무것도 추가하지 않은 것 같아요.
2. 쿼리에서 now()를 사용하는 것이 좋습니까?쿼리에서 날짜를 계산하는 것보다 쿼리에 날짜를 전달하고 싶습니다.

상세 정보:질문 1에서는 반올림 오류 없이 분수를 집계할 수 있는지 확인하려고 합니다.숫자 19,2가 금액으로 적당하고 두 번째 경우 정수는 OK라고 생각합니다.돈을 벌기 위해 수레를 사용하는 것은 이러한 이유로 잘못된 것이다.

두 번째 질문에서는 프로그래머로서 "지금"으로 간주되는 날짜를 완전히 제어하고 싶습니다.지금()과 같은 함수를 사용하면 자동 단위 테스트를 작성하기가 어려울 수 있습니다.또한 트랜잭션스크립트가 길면 변수를 now()와 동일하게 설정하고 모든 로직이 동일한 값을 사용하도록 변수를 사용하는 것이 좋습니다.

이 문제를 해결하기 위한 실제 예를 들어보자

내 ohlc 데이터에 가중 이동 평균을 계산해야 했습니다. 각 데이터에 대한 기호가 있는 약 134,000개의 초가 있습니다.

1. 옵션 1 Python/Node 등에서 실행
2. 옵션 2 SQL 자체에서 실행!

어떤 게 더 나아요?

• Python에서 이 작업을 해야 한다면, 기본적으로 저장된 모든 레코드를 최악의 경우에 가져와 계산을 수행하고 모든 것을 저장해야 합니다. 내 생각에는 I/O의 엄청난 낭비입니다.
• 새 촛불을 얻을 때마다 가중 이동 평균이 변경되므로 정기적으로 대량의 I/O를 수행하게 됩니다. 제 의견으로는 좋지 않습니다.
• SQL에서는 모든 것을 계산하고 저장하는 트리거만 작성하면 되기 때문에 때때로 각 쌍의 최종 WMA 값만 가져오면 됩니다.그것이 훨씬 효율적입니다.

요구 사항들

• 초마다 WMA를 계산해서 저장해야 한다면 Python에서 할 것이다.
• 하지만 마지막 값만 필요하기 때문에 SQL이 Python보다 훨씬 빠릅니다.

격려를 드리자면, 이것은 가중 이동 평균을 수행하기 위한 Python 버전입니다.

코드를 통한 WMA 실행

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

SQL을 통한 WMA

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

믿거나 말거나 쿼리는 Pure Python 버전의 Weighted Moving Average보다 빠르게 실행됩니다!!!나는 그 질문을 차근차근 써 내려갔다 그러니 조금만 참으면 넌 잘 할 수 있을 거야

스피드

0.42141127300055814초 Python

0.23801879299935536초SQL

데이터베이스에는 134,000개의 가짜 OHLC 레코드가 1,000개의 주식으로 분할되어 있습니다.이것이 SQL이 앱 서버를 능가하는 예입니다.

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