Intro
대규모 반도체 공장에서 여러 설비 데이터를 가공하고 운영하다 보면, 데이터베이스에 쌓인 여러 데이터를 활용해서 원하는 형태로 데이터를 가져올 수 있어야 하는 역량이 중요합니다. 뿐만 아니라 부하가 큰 트랜잭션 쿼리를 날리다 보니 DA주1 를 통해서 쿼리를 튜닝하는 일도 빈번한데요, API 성능개선을 하면서 Raw SQL과 ORM으로 DB에서 원하는 정보의 데이터를 가져오는 성능을 비교해 보려 합니다.
ORM vs SQL
언제부터인가 ORM이 트렌드가 되고, 누구나 다 SQL보다는 ORM으로 DB 트랜잭션을 처리하기를 원하지만, 개인적으로 어떤 장점이 있기에 ORM을 사용하고 싶은가? 라는 질문에는 여러 개발자, 친구, 선배에게 물어봐도 그럴듯한 답변을 듣지 못하고 혼자서 많이 고민해 왔습니다.
그나마 "DB 의 종류에 구애받지 않기에", "개발 생산성을 높일 수 있어서"라는 답변을 들었지만, 개인적으로 저를 설득할 만한 대답이 되지 않았습니다.
개발 생산성을 높일 수 있다.
규모가 작은 초기 시스템에서는 ERD을 작성할 필요가 없기에 개발 생산성을 높일 수 있다는 점에 공감하지만, 규모가 커진 상태에서는 개발 생산성 효과가 오히려 떨어지는 것이 아닌가 하는 의문이 있습니다. 수십 ~ 수백 개의 테이블로 구성된 DB 구조를 ERD가 아닌 수천 줄짜리 코드를 보고 테이블 관계를 이해할 수 있나?
가독성을 높일 수 있다
프로그래밍 언어로 만든 함수는 '논리'가 생명이지만, DB를 다루는 SQL은 '데이터 정합성'이 생명입니다. 똑같은 SQL도 개발 DB, 운영 DB 그리고 시간에 따라 데이터가 다르게 조회됩니다. 아래 본문처럼, ORM은 SQL 블록 단위로 코드를 작성한 구조가 얼핏 보면 가독성이 좋아 보일 수 있으나, 블록 단위 데이터를 까볼 수는 없습니다. 반대로 Raw SQL은 거대한 SQL 구조에서 내부 블록 단위로 쿼리를 실행해 데이터를 확인해 볼 수 있고, 어떻게 수정해야 할 지 바로 확인이 가능합니다.
성능에 대한 저하
기본적으로 ORM 으로 데이터를 처리하는 게 SQL을 활용하는 것보다, 성능이 월등히 떨어집니다. (ORM 내부적으로 SQL로 변환하는 과정을 거치며, 조인이 많아질수록 성능을 기하급수적으로 감소합니다.) 또한 튜닝이 필요한 시점에서는 결국 다시 SQL로 작성해야 하는 단점이 있습니다.
하지만 최근 지하철을 타며 회사를 출근하다가 문득 이 논쟁에 대한 정답을 찾았습니다. 그것은 바로 OLTP 와 OLAP 서비스에 따라 다르다는 것!
OLTP와 OLAP
이 용어는 주로 Database에 대해서 공부해본 사람이라면 한 번쯤은 들어 봤을 것으로 보입니다.
OLTP (Online Transaction Processing)은 현재의 데이터 처리가 얼마나 정확하고, 무결한지가 중요합니다. 그렇기 때문에 주로 데이터의 저장, 삭제, 수정 등의 실질적인 데이터를 수정하는 작업을 의미하고, 단순 CRUD가 많은 서비스에서 주로 사용됩니다. 예시로 도메인 주도 설계에서, 여러 도메인을 정의하고 해당 도메인간의 연관관계 이벤트가 많이 발생하는 서비스라면 OLTP 에 가깝다고 표현할 수 있을 것 같습니다.
OLAP ( Online Analytical Processing)는 이미 지정된 데이터를 바탕으로 어떤 정보를 제공하는지가 중요합니다. 따라서 OLAP는 데이터가 무결하고 정확하다는 전제를 바탕으로 고객 또는 사용자가 원하는 정보를 어떤식으로 표현하고 제공하는지를 의미합니다. 예시로 설비의 여러 생산성 지표나 모니터링 같은 데이터를 추출하거나, 회사의 년간 각 부서별 임금 상승률 같은 분석용 데이터를 주로 보여주는 서비스라면 OLAP에 가깝다고 표현할 수 있습니다.
| 구분 | OLTP | OLAP |
|---|---|---|
| 속도 | 수 초 이내 | 수 초 이상 수 분 이내 |
| 관리단위 | 테이블 | 분석된 정보 |
| 최적화 방법 | 트랜잭션 효율화, 무결성의 극대화 | 조회속도, 정보의 가치, 편의성 |
| 데이터 특성 | 트랜잭션 중심 | 정보 중심 |
| 예시 | 회원정보 수정 | 1년 간의 주문 이기 트렌드 |
| 상품 주문 | 한달간의 방문율 수익, 제품 | |
| 댓글 남기기 및 수정 | 10년간 A 회사의 직원별 매출 상승률 |
정리를 하자면 단순한 데이터의 조회 및 저장이 주로 발생하는, 가볍지만 트래픽이 많은 서비스의 경우에는 ORM이 트랜드가 되고, 데이터를 가공하고 분석하는, 무겁지만 트래픽 건수가 상대적으로 적은 서비스에서는 SQL이 많이 쓰이는 것으로 보입니다.
수정 API
companies/{company_id}/enterprise-dga-grade-status변압기의 DGA 가스를 측정해서, AI 알고리즘을 돌린 후, 최신 진단한 상태값과 바로 직전 진단한 DGA 의 상태값을 보내 설비 별 상태값의 추이를 보여주도록 하는 API 입니다.
DB 구조
해당 시스템에서는 PostgresSQL DB를 사용했으며, 설비를 지칭하는 Asset 테이블과 DGA(유증류 가스)를 측정한 DataMtrBody 그리고 설비와, DGA 정보를 이용해 AI 알고리즘 결과를 저장한 AiMtrBodyDga 테이블을 이용하여 데이터를 가져옵니다. 구체적인 테이블 칼럼 설명은 보안상 생략합니다.
Before (ORM)
# time 측정
start = time.time()
math.factorial(100000)
# asset 별 모든 측정일과 이전 측정일 추출
asset_info = (
session.query(
Asset.id.label("asset_id"),
Asset.asset_name.label("asset_name"),
DataMtrBodyDga.acquisition_date.label("acquisition_date"),
func.lead(DataMtrBodyDga.acquisition_date, 1)
.over(
partition_by=[Asset.id, Asset.asset_name],
order_by=DataMtrBodyDga.acquisition_date.desc(),
)
.label("prev_acquisition_date"),
)
.select_from(Asset)
.join(DataMtrBodyDga, DataMtrBodyDga.asset_id == Asset.id)
.filter(
Asset.company_id == company_id,
Asset.asset_type == "MTR",
Asset.serial_no.in_(available_licenses),
)
.order_by(
Asset.id, Asset.asset_name, DataMtrBodyDga.acquisition_date.desc()
)
.subquery()
)
# dataMtrBodyDga 테이블 기준 asset 별 최신 측정일과 바로 이전 측정일 추출
asset_acquisition_info = (
session.query(
asset_info.c.asset_id,
asset_info.c.asset_name,
func.max(asset_info.c.acquisition_date).label("acquisition_date"),
case(
[
(
func.max(asset_info.c.prev_acquisition_date) == None,
func.max(asset_info.c.acquisition_date),
),
(
func.max(asset_info.c.prev_acquisition_date) != None,
func.max(asset_info.c.prev_acquisition_date),
),
]
).label("prev_acquisition_date"),
)
.select_from(asset_info)
.group_by(asset_info.c.asset_id, asset_info.c.asset_name)
.subquery()
)
# asset 측정일 기준별 진단 결과 건수 추출
diag_info = (
session.query(
DataMtrBodyDga.asset_id.label("asset_id"),
DataMtrBodyDga.acquisition_date.label("acquisition_date"),
AiMtrBodyDga.ai_diagnosis_result.label("ai_diagnosis_result"),
)
.select_from(DataMtrBodyDga) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.outerjoin(Asset, DataMtrBodyDga.asset_id == Asset.id)
.outerjoin(
AiMtrBodyDga,
DataMtrBodyDga.id == AiMtrBodyDga.data_mtr_body_dga_id,
)
.filter(
Asset.company_id == company_id,
Asset.asset_type == "MTR",
Asset.serial_no.in_(available_licenses),
)
.subquery()
)
# asset 별 최신 측정일 기준 상태 건수
query1 = (
session.query(
func.count(diag_info.c.ai_diagnosis_result).label("total_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 0, "NORMAL")])
).label("normal_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 1, "CAUTION")])
).label("caution_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 2, "WARNING")])
).label("warning_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 3, "CRITICAL")])
).label("critical_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 4, "FAULT")])
).label("fault_cnt"),
)
.select_from(asset_acquisition_info) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.join(
diag_info,
and_(
asset_acquisition_info.c.asset_id == diag_info.c.asset_id,
asset_acquisition_info.c.acquisition_date
== diag_info.c.acquisition_date,
),
)
.first()
)
# asset 별 이전 측정일 기준 상태 건수
query2 = (
session.query(
func.count(diag_info.c.ai_diagnosis_result).label("total_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 0, "NORMAL")])
).label("normal_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 1, "CAUTION")])
).label("caution_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 2, "WARNING")])
).label("warning_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 3, "CRITICAL")])
).label("critical_cnt"),
func.count(
case([(diag_info.c.ai_diagnosis_result == 4, "FAULT")])
).label("fault_cnt"),
)
.select_from(asset_acquisition_info) # LEFT OUTER JOIN 의 기준 테이블을 설정하기 위해 사용
.join(
diag_info,
and_(
asset_acquisition_info.c.asset_id == diag_info.c.asset_id,
asset_acquisition_info.c.prev_acquisition_date
== diag_info.c.acquisition_date,
),
)
.first()
)
end = time.time()
print("****************************************************")
print(f"{end - start:.5f} sec")
print("****************************************************")| 1회 시도 (DB 캐시 제거) | 2회 시도 (DB 캐시 제거) | 3회 시도 (DB 캐시 제거) | |
|---|---|---|---|
| 1차 시도 | 6.62579초 | 6.28475초 | 6.25671초 |
| 2차 시도 | 5.42588초 | 5.30851초 | 4.69481초 |
| 3차 시도 | 4.86993초 | 4.90473초 | 4.73450초 |
After (SQL)
ANY (select unnest(:available_licenses))
# time 측정
start = time.time()
math.factorial(100000)
sql_statement = text(
"""
WITH asset_info AS (
SELECT a.asset_id, a.asset_name
,MAX(a.acquisition_date) AS acquisition_date
,CASE WHEN MAX(a.prev_acquisition_date) IS NULL THEN MAX(a.acquisition_date)
WHEN MAX(a.prev_acquisition_date) IS NOT NULL THEN MAX(a.prev_acquisition_date)
END AS prev_acquisition_date
FROM (
SELECT a.id AS asset_id
,a.asset_name AS asset_name
,dmbd.acquisition_date
,LEAD(dmbd.acquisition_date,1)
OVER (PARTITION BY a.id, a.asset_name ORDER BY dmbd.acquisition_date DESC )
AS prev_acquisition_date
FROM substation.asset a INNER JOIN substation.data_mtr_body_dga dmbd
ON a.id = dmbd.asset_id
WHERE a.asset_type = 'MTR'
AND a.company_id = :company_id
AND a.serial_no = ANY (SELECT UNNEST(:available_licenses))
ORDER BY a.id, a.asset_name, dmbd.acquisition_date DESC
) a
GROUP BY a.asset_id, a.asset_name
), diag_info AS (
SELECT dmbd.asset_id, dmbd.acquisition_date , ambd.ai_diagnosis_result
FROM substation.data_mtr_body_dga dmbd LEFT OUTER JOIN substation.asset a
ON dmbd.asset_id = a.id LEFT OUTER JOIN substation.ai_mtr_body_dga ambd
ON dmbd.id = ambd.data_mtr_body_dga_id
WHERE a.company_id = :company_id
AND a.serial_no = ANY (SELECT UNNEST(:available_licenses))
AND a.asset_type = 'MTR'
)
SELECT COUNT(*) AS TOTAL_CNT
,COUNT(CASE WHEN b.ai_diagnosis_result = 0 THEN 'NORMAL' END ) AS NORMAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 1 THEN 'CAUTION' END ) AS CAUTION
,COUNT(CASE WHEN b.ai_diagnosis_result = 2 THEN 'WARNING' END ) AS WARNING
,COUNT(CASE WHEN b.ai_diagnosis_result = 3 THEN 'CRITICAL' END ) AS CRITICAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 4 THEN 'FAULT' END ) AS FAULT
FROM asset_info a LEFT OUTER JOIN diag_info b
ON a.asset_id = b.asset_id
WHERE a.acquisition_date = b.acquisition_date
UNION ALL
SELECT COUNT(*) AS TOTAL_CNT
,COUNT(CASE WHEN b.ai_diagnosis_result = 0 THEN 'NORMAL' END ) AS NORMAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 1 THEN 'CAUTION' END ) AS CAUTION
,COUNT(CASE WHEN b.ai_diagnosis_result = 2 THEN 'WARNING' END ) AS WARNING
,COUNT(CASE WHEN b.ai_diagnosis_result = 3 THEN 'CRITICAL' END ) AS CRITICAL
,COUNT(CASE WHEN b.ai_diagnosis_result = 4 THEN 'FAULT' END ) AS FAULT
FROM asset_info a LEFT OUTER JOIN diag_info b
ON a.asset_id = b.asset_id
WHERE a.prev_acquisition_date = b.acquisition_date
"""
)
result = session.execute(sql_statement,{
"available_licenses": available_licenses,
"company_id": company_id
})
end = time.time()
print("****************************************************")
print(f"{end - start:.5f} sec")
print("****************************************************")| 1회 시도 (DB 캐시 제거) | 2회 시도 (DB 캐시 제거) | 3회 시도 (DB 캐시 제거) | |
|---|---|---|---|
| 1차 시도 | 4.37999초 | 4.64817초 | 4.29508초 |
| 2차 시도 | 1.76666초 | 1.64863초 | 1.58068초 |
| 3차 시도 | 1.61587초 | 1.56709초 | 1.59817초 |
정리
SQL의 처리 과정이나 로직에 대해서는 따로 설명하지는 않지만, DB 캐시에 의한 성능 효과를 고려해서 1 try 기준의 평균값으로 시간을 측정했을 때 Before (ORM) 6.38908, After(SQL) 4.44108 아래와 같이 구해집니다.
(4.44108 / 6.38908 ) x 100 = 69.51 %
소요 시간을 기존 대비 30.48%가량 줄일 수 있었습니다.다만 흥미로운 부분으로 DB 캐시 효과를 고려한 3try 기준의 평균값으로 측정하면 아래와 같이 구해집니다. Before (ORM) 4.83638, After (SQL) 1.59371
(1.59371 / 4.83638 ) x 100 = 32.95 %
소요 시간을 기존 대비 67%가량 줄일 수 있었습니다.이 수치는 개발 환경에서 진행된 테스트로, 테이블에 적재된 데이터 용량과 DB 부하 상태에 따라서 수치에 영향을 받을 수 있으며, 그럴 경우 성능은 더 차이가 날 것으로 보입니다.
- DB 조인이 더 많아지는 경우에도 마찬가지입니다.
성능 저하 원인
그렇다면 'ORM 이 변경한 쿼리 구조가 성능이 나쁜 건지' 혹은 'ORM 이 SQL로 변경하는 과정에서 오랜 걸린 건지? 에 대한 궁금중이 생길 수 있을 것 같은데요. ORM 이 변경한 쿼리로 직접 실행했을 때의 결과는 아래와 같았습니다.
ORM 으로 변환된 SQL
start = time.time()
math.factorial(100000)
session = DatabaseFactory.create_session()
try:
company_name = (
session.query(Company.company_name)
.filter(Company.id == company_id)
.first()
.company_name
)
available_licenses = LicenseUtils.license_check_all(company_name=company_name)
query_1 = text(
"""
SELECT count(anon_1.ai_diagnosis_result) AS total_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 0) THEN 'NORMAL' END) AS normal_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 1) THEN 'CAUTION' END) AS caution_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 2) THEN 'WARNING' END) AS warning_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 3) THEN 'CRITICAL' END) AS critical_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 4) THEN 'FAULT' END) AS fault_cnt
FROM (
SELECT anon_3.asset_id AS asset_id
, anon_3.asset_name AS asset_name
, MAX(anon_3.acquisition_date) AS acquisition_date
, CASE WHEN (MAX(anon_3.prev_acquisition_date) IS NULL) THEN MAX(anon_3.acquisition_date)
WHEN (MAX(anon_3.prev_acquisition_date) IS NOT NULL) THEN MAX(anon_3.prev_acquisition_date)
END AS prev_acquisition_date
FROM (
SELECT substation.asset.id AS asset_id
, substation.asset.asset_name AS asset_name
, substation.data_mtr_body_dga.acquisition_date AS acquisition_date
, LEAD(substation.data_mtr_body_dga.acquisition_date, 1)
OVER (PARTITION BY substation.asset.id, substation.asset.asset_name
ORDER BY substation.data_mtr_body_dga.acquisition_date DESC
) AS prev_acquisition_date
FROM substation.asset JOIN substation.data_mtr_body_dga
ON substation.data_mtr_body_dga.asset_id = substation.asset.id
WHERE substation.asset.company_id = :company_id
AND substation.asset.asset_type = 'MTR'
AND substation.asset.serial_no = ANY (SELECT UNNEST(:available_licenses))
ORDER BY substation.asset.id, substation.asset.asset_name, substation.data_mtr_body_dga.acquisition_date DESC
) AS anon_3
GROUP BY anon_3.asset_id, anon_3.asset_name) AS anon_2 JOIN
(
SELECT substation.data_mtr_body_dga.asset_id AS asset_id
, substation.data_mtr_body_dga.acquisition_date AS acquisition_date
, substation.ai_mtr_body_dga.ai_diagnosis_result AS ai_diagnosis_result
FROM substation.data_mtr_body_dga LEFT OUTER JOIN substation.asset
ON substation.data_mtr_body_dga.asset_id = substation.asset.id
LEFT OUTER JOIN substation.ai_mtr_body_dga
ON substation.data_mtr_body_dga.id = substation.ai_mtr_body_dga.data_mtr_body_dga_id
WHERE substation.asset.company_id = :company_id
AND substation.asset.asset_type = 'MTR'
AND substation.asset.serial_no = ANY (SELECT UNNEST(:available_licenses))
) AS anon_1
ON anon_2.asset_id = anon_1.asset_id
AND anon_1.acquisition_date = anon_2.acquisition_date
LIMIT 1
"""
)
sql_result1 = session.execute(
query_1,
{
"available_licenses": available_licenses,
"company_id": company_id,
},
)
query_2 = text(
"""
SELECT count(anon_1.ai_diagnosis_result) AS total_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 0) THEN 'NORMAL' END) AS normal_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 1) THEN 'CAUTION' END) AS caution_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 2) THEN 'WARNING' END) AS warning_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 3) THEN 'CRITICAL' END) AS critical_cnt
, count(CASE WHEN (anon_1.ai_diagnosis_result = 4) THEN 'FAULT' END) AS fault_cnt
FROM (
SELECT anon_3.asset_id AS asset_id
, anon_3.asset_name AS asset_name
, MAX(anon_3.acquisition_date) AS acquisition_date
, CASE WHEN (MAX(anon_3.prev_acquisition_date) IS NULL) THEN MAX(anon_3.acquisition_date)
WHEN (MAX(anon_3.prev_acquisition_date) IS NOT NULL) THEN MAX(anon_3.prev_acquisition_date)
END AS prev_acquisition_date
FROM (
SELECT substation.asset.id AS asset_id
, substation.asset.asset_name AS asset_name
, substation.data_mtr_body_dga.acquisition_date AS acquisition_date
, LEAD(substation.data_mtr_body_dga.acquisition_date, 1)
OVER (PARTITION BY substation.asset.id, substation.asset.asset_name
ORDER BY substation.data_mtr_body_dga.acquisition_date DESC
) AS prev_acquisition_date
FROM substation.asset JOIN substation.data_mtr_body_dga
ON substation.data_mtr_body_dga.asset_id = substation.asset.id
WHERE substation.asset.company_id = :company_id
AND substation.asset.asset_type = 'MTR'
AND substation.asset.serial_no = ANY (SELECT UNNEST(:available_licenses))
ORDER BY substation.asset.id, substation.asset.asset_name, substation.data_mtr_body_dga.acquisition_date DESC
) AS anon_3
GROUP BY anon_3.asset_id, anon_3.asset_name) AS anon_2 JOIN
(
SELECT substation.data_mtr_body_dga.asset_id AS asset_id
, substation.data_mtr_body_dga.acquisition_date AS acquisition_date
, substation.ai_mtr_body_dga.ai_diagnosis_result AS ai_diagnosis_result
FROM substation.data_mtr_body_dga LEFT OUTER JOIN substation.asset
ON substation.data_mtr_body_dga.asset_id = substation.asset.id
LEFT OUTER JOIN substation.ai_mtr_body_dga
ON substation.data_mtr_body_dga.id = substation.ai_mtr_body_dga.data_mtr_body_dga_id
WHERE substation.asset.company_id = :company_id
AND substation.asset.asset_type = 'MTR'
AND substation.asset.serial_no = ANY (SELECT UNNEST(:available_licenses))
) AS anon_1
ON anon_2.asset_id = anon_1.asset_id
AND anon_1.acquisition_date = anon_2.prev_acquisition_date
LIMIT 1
"""
)
sql_result2 = session.execute(
query_2,
{
"available_licenses": available_licenses,
"company_id": company_id,
},
)
results1 = [rowproxy._mapping for rowproxy in sql_result1]
results2 = [rowproxy._mapping for rowproxy in sql_result2]
end = time.time()
print("****************************************************")
print(f"{end - start:.5f} sec")
print("****************************************************")
responses = {"current": results1[0], "prev": results2[0]}| 1회 시도 (DB 캐시 제거) | 2회 시도 (DB 캐시 제거) | 3회 시도 (DB 캐시 제거) | |
|---|---|---|---|
| 1차 시도 | 4.68825초 | 4.95818초 | 4.51720초 |
| 2차 시도 | 1.86746초 | 1.88096초 | 1.97292초 |
| 3차 시도 | 1.83698초 | 1.87081 초 | 1.87755초 |
| 1회 시도 (DB 캐시 제거) | 2회 시도 (DB 캐시 제거) | 3회 시도 (DB 캐시 제거) | |
|---|---|---|---|
| 1차 시도 | 6.62579초 | 6.28475초 | 6.25671초 |
| 2차 시도 | 5.42588초 | 5.30851초 | 4.69481 초 |
| 3차 시도 | 4.86993초 | 4.90473초 | 4.734509초 |
DB 캐싱에 의한 성능향상을 고려해서 1try 의 경우에 한해서, 비교해 보면 ORM 으로 조회했을 때와 Raw SQL 로 조회 했을때를 비교해보면 아래와 같습니다.
ORM -> SQL 로 변경하는데 소요되는 시간 약 1.67 초 정도 소요
직접 짠 Raw SQL 구조 대비 약 0.28 초 정도 성능 저하
직접 짠 SQL 은 UNION ALL 을 사용한 반면에, ORM 에서는 쿼리 2번을 조회 하도록 했습니다. 다만 실제 비교 수치에서 유의미한 차이는 없었고, ORM 에서는 가독성을 향상 시킬 수 있어서(코드 감소) UNION ALL 대신 2개로 분할 해서 사용했었습니다.
ORM 으로 실행한 코드는 DB 캐싱에 의한 이득에 있어서, Raw SQL 보다 매우 비효율적인 모습을 보였습니다. 해당 원인으로 추정해보기에는, IN 절을 사용하는 것에 있는것으로 판단됩니다.
사실 SQL 튜닝 관점에서도 수많은 데이터를 IN 절을 통해서 받도록 하는 것은 좋지 못한 행동이지만, ORM 이 수많은 파라미터를 IN 절로 받도록 변환하는 과정에서 엄청 긴 SQL 을 만들게 되고, SQL 로 가공하는 시간과 더불어 캐싱효과를 제대로 누리지 못하게 한 것으로 보입니다.
ORM 이 가공한 SQL 로그
AND substation.asset.serial_no IN (%(serial_no_1_1)s, %(serial_no_1_2)s, %(serial_no_1_3)s, %(serial_no_1_4)s, %(serial_no_1_5)s, %(serial_no_1_6)s, %(serial_no_1_7)s
, %(serial_no_1_8)s, %(serial_no_1_9)s, %(serial_no_1_10)s, %(serial_no_1_11)s, %(serial_no_1_12)s, %(serial_no_1_13)s, %(serial_no_1_14)s, %(serial_no_1_15)s
, %(serial_no_1_16)s, %(serial_no_1_17)s, %(serial_no_1_18)s, %(serial_no_1_19)s, %(serial_no_1_20)s, %(serial_no_1_21)s, %(serial_no_1_22)s, %(serial_no_1_23)s
, %(serial_no_1_24)s, %(serial_no_1_25)s, %(serial_no_1_26)s, %(serial_no_1_27)s, %(serial_no_1_28)s, %(serial_no_1_29)s, %(serial_no_1_30)s, %(serial_no_1_31)s
, %(serial_no_1_32)s, %(serial_no_1_33)s, %(serial_no_1_34)s, %(serial_no_1_35)s, %(serial_no_1_36)s, %(serial_no_1_37)s, %(serial_no_1_38)s, %(serial_no_1_39)s
, %(serial_no_1_40)s, %(serial_no_1_41)s, %(serial_no_1_42)s, %(serial_no_1_43)s, %(serial_no_1_44)s, %(serial_no_1_45)s, %(serial_no_1_46)s, %(serial_no_1_47)s
, %(serial_no_1_48)s, %(serial_no_1_49)s, %(serial_no_1_50)s, %(serial_no_1_51)s, %(serial_no_1_52)s, %(serial_no_1_53)s, %(serial_no_1_54)s, %(serial_no_1_55)s
, %(serial_no_1_56)s, %(serial_no_1_57)s, %(serial_no_1_58)s, %(serial_no_1_59)s, %(serial_no_1_60)s, %(serial_no_1_61)s, %(serial_no_1_62)s, %(serial_no_1_63)s
, %(serial_no_1_64)s, %(serial_no_1_65)s, %(serial_no_1_66)s, %(serial_no_1_67)s, %(serial_no_1_68)s, %(serial_no_1_69)s, %(serial_no_1_70)s, %(serial_no_1_71)s
, %(serial_no_1_72)s, %(serial_no_1_73)s, %(serial_no_1_74)s, %(serial_no_1_75)s, %(serial_no_1_76)s, %(serial_no_1_77)s, %(serial_no_1_78)s, %(serial_no_1_79)s
, %(serial_no_1_80)s, %(serial_no_1_81)s, %(serial_no_1_82)s, %(serial_no_1_83)s, %(serial_no_1_84)s, %(serial_no_1_85)s, %(serial_no_1_86)s, %(serial_no_1_87)s
, %(serial_no_1_88)s, %(serial_no_1_89)s, %(serial_no_1_90)s, %(serial_no_1_91)s, %(serial_no_1_92)s, %(serial_no_1_93)s, %(serial_no_1_94)s, %(serial_no_1_95)s
, %(serial_no_1_96)s, %(serial_no_1_97)s, %(serial_no_1_98)s, %(serial_no_1_99)s, %(serial_no_1_100)s, %(serial_no_1_101)s, %(serial_no_1_102)s, %(serial_no_1_103)s
, %(serial_no_1_104)s, %(serial_no_1_105)s, %(serial_no_1_106)s, %(serial_no_1_107)s, %(serial_no_1_108)s, %(serial_no_1_109)s, %(serial_no_1_110)s, %(serial_no_1_111)s
, %(serial_no_1_112)s, %(serial_no_1_113)s, %(serial_no_1_114)s, %(serial_no_1_115)s, %(serial_no_1_116)s, %(serial_no_1_117)s, %(serial_no_1_118)s, %(serial_no_1_119)s
, %(serial_no_1_120)s, %(serial_no_1_121)s, %(serial_no_1_122)s, %(serial_no_1_123)s, %(serial_no_1_124)s, %(serial_no_1_125)s, %(serial_no_1_126)s, %(serial_no_1_127)s
, %(serial_no_1_128)s, %(serial_no_1_129)s, %(serial_no_1_130)s, %(serial_no_1_131)s, %(serial_no_1_132)s, %(serial_no_1_133)s, %(serial_no_1_134)s, %(serial_no_1_135)s
, %(serial_no_1_136)s, %(serial_no_1_137)s, %(serial_no_1_138)s, %(serial_no_1_139)s, %(serial_no_1_140)s, %(serial_no_1_141)s, %(serial_no_1_142)s, %(serial_no_1_143)s
, %(serial_no_1_144)s, %(serial_no_1_145)s, %(serial_no_1_146)s, %(serial_no_1_147)s, %(serial_no_1_148)s, %(serial_no_1_149)s, %(serial_no_1_150)s, %(serial_no_1_151)s
, %(serial_no_1_152)s, %(serial_no_1_153)s, %(serial_no_1_154)s, %(serial_no_1_155)s, %(serial_no_1_156)s, %(serial_no_1_157)s, %(serial_no_1_158)s, %(serial_no_1_159)s
....
....
... Raw SQL 로 실행한 로그
AND a.serial_no = ANY (SELECT UNNEST(%(available_licenses)s))비고
ORM 에서 성능 관련 얘기를 할 때는 보통 기본적으로 N+1 이슈에 대해서 많이 다루는데요, 이번 글에서는 ORM 의 구조적인 형태로 발생하는 트랜잭션 비효율을 다루기보다는 동일한 형태의 테이블 조인으로 데이터를 조회했을 때, 발생하는 성능 이슈에 대해서 비교해 보는 글을 적어봤습니다.
또한 해당 글은 Backend 서버와 Database 서버 간 로컬 망에서 랜선 연결을 통해 측정한 데이터이며, DB 데이터 모수도 그저 5,500여 건의 데이터를 가지고 측정한 데이터이기에, 실 운영환경에서는 좀 더 극적인 효과를 기대할 수 있을 것으로 기대합니다.
마치며
ORM 진영과 SQL 진영의 열띤 토론에 참여했었던 1인으로서, 개인적으로 2년 넘게 고민해 온 주제였습니다. 이 글의 내용만 본다면 SQL 진영의 승리처럼 보일 수 있지만, 이글을 작성하고 준비하면서 새로운 인사이트를 얻게 되었습니다. 바로 ORM 만이 가지고 있는 '특별한 장점'이 있다는 점과 DB 연산 비용 못지않게 중요한 요소가 바로 '네트워크 비용'이라는 점입니다. MSA 정말 좋은 건가?
또한 이런 결괏값은 어디까지나 OLAP 시스템 관점에서 작성한 글로, 복잡하지 않은 쿼리를 가진 OLTP 시스템이라면 ORM 도 충분히 사용하기 좋은 매력적인 도구라고 생각됩니다, ORM 이 가지고 있는 장점과, 성능 개선 포인트가 궁금하시다면 다음 편을 참고해 주세요.