Substation API 성능 개선기 (ORM vs SQL) 2편
- 2024-02-19
1편을 읽고 오시면 더 유익하게 이 글을 즐길 수 있습니다.
[기웃 거리기]
원프레딕트에서 일하면서 할말은 많지만, 적어도 같이 일하는 구성원의 역량에 대해서는 신입, 경력 가릴것 없이 좋은 사람들로 구성되어 있어서, 자주 기웃 거리며 좋은 인사이트를 얻어가곤 합니다.
이전 글에서 정리한 지표를 보면 Raw SQL 로 사용했을 경우 ORM 대비 좋은 성능을 기대할 수 있었습니다. 관련해서 Pro-Serve 팀원 중 한 분에게서 ORM 으로 사용했을 때 좀 더 최적화 하는 방법에 대한 힌트를 받았고, ORM 을 활용해서 DB 트랜잭션 처리 시 성능을 개선할 수 있는 방법과 ORM에 대한 제 생각에 대해서도 이야기 하려 합니다.
- DB 서버는 이전 편의 '네트워크 트래픽 이슈'를 고려하지 않기 위해서 Local PC 에서 진행됐습니다.
[성능 비교 Tool]
성능에 대한 지표로는 2023년 Naver Deview 의 'ML/AI 개발자를 위한 단계별 Python 최적화 가이드라인' 에서 영감을 얻었던 line-profiler 라이브러리를 활용해서 비교를 진행했습니다. 고마워요 갓 네이버 '문주혁'님...
https://github.com/pyutils/line_profiler
https://deview.kr/2023/sessions/541
[성능 개선 Point 1]
https://docs.sqlalchemy.org/en/14/core/connections.html#quick-guidelines-for-lambdas
Sqlalchemy ORM 에서 성능을 개선할 수 있는 방법으로 Lambda 를 사용하는 방법이 있습니다. Lamda 방식은 미리 적어둔 ORM 코드 블록을 미리 쿼리로 가공한 함수처럼 지정해서, 사용하는 방식입니다.
Lamda를 활용한 테스트 비교는 이전 글에서 작성한 'enterprise-mtr-grade-status' API 의 일부분에 대해서만 진행했습니다.
1. 회사명을 조회
2. 회사별 라이센스 조회 (외부 API)
3. asset 별 모든 DGA 가스 측정일과 직전 측정일 추출
Before (ORM)
line-profiler 총 소요시간 : 1.5103 sec
Total time: 1.5103 s
File: line_profiler_test_2.py
Function: test_func at line 34
Line # Hits Time Per Hit % Time Line Contents
==============================================================
34 def test_func():
35 1 13000.0 13000.0 0.0 Session = sessionmaker(bind=engine)
36 1 138000.0 138000.0 0.0 session = Session()
37 1 0.0 0.0 0.0 company_id = 16
38
39 # 회사명 조회
40 1 0.0 0.0 0.0 company_name = (
41 1 78492000.0 78492000.0 5.2 session.query(Company.company_name)
42 1 147000.0 147000.0 0.0 .filter(Company.id == company_id)
43 .first()
44 .company_name
45 )
46
47 # 회사별 라이센스 조회
48 1 673974000.0 673974000.0 44.6 available_licenses = LicenseUtils.license_check_all(company_name=company_name)
49
50 # asset 별 모든 측정일과 이전 측정일 추출
51 1 0.0 0.0 0.0 asset_info = (
52 1 196459000.0 196459000.0 13.0 session.query(
53 1 109000.0 109000.0 0.0 Asset.id.label("asset_id"),
54 1 34000.0 34000.0 0.0 Asset.asset_name.label("asset_name"),
55 1 10000.0 10000.0 0.0 DataMtrBodyDga.acquisition_date.label("acquisition_date"),
56 1 345000.0 345000.0 0.0 func.lead(DataMtrBodyDga.acquisition_date, 1)
57 .over(
58 1 2000.0 2000.0 0.0 partition_by=[Asset.id, Asset.asset_name],
59 1 65000.0 65000.0 0.0 order_by=DataMtrBodyDga.acquisition_date.desc(),
60 )
61 1 1000.0 1000.0 0.0 .label("prev_acquisition_date"),
62 )
63 1 0.0 0.0 0.0 .select_from(Asset)
64 1 66000.0 66000.0 0.0 .join(DataMtrBodyDga, DataMtrBodyDga.asset_id == Asset.id)
65 .filter(
66 1 131000.0 131000.0 0.0 Asset.company_id == company_id,
67 1 104000.0 104000.0 0.0 Asset.asset_type == "MTR",
68 1 21173000.0 21173000.0 1.4 Asset.serial_no.in_(available_licenses),
69 )
70 1 24000.0 24000.0 0.0 .order_by(Asset.id, Asset.asset_name, DataMtrBodyDga.acquisition_date.desc())
71 .all()
72 )
73
74 1 539010000.0 539010000.0 35.7 print(asset_info)After (ORM Lamda)
line-profiler 총 소요시간: 0.795556 sec
Total time: 0.795556 s
File: line_profiler_test_3.py
Function: test_func at line 34
Line # Hits Time Per Hit % Time Line Contents
==============================================================
34 def test_func():
35 1 12000.0 12000.0 0.0 Session = sessionmaker(bind=engine)
36 1 134000.0 134000.0 0.0 session = Session()
37 1 0.0 0.0 0.0 company_id = 16
38
39 # 회사명 조회
40 1 53103000.0 53103000.0 6.7 company_name = (
41 1 1000.0 1000.0 0.0 lambda session, company_id: session.query(Company.company_name)
42 .filter(Company.id == company_id)
43 .first()
44 .company_name
45 1 0.0 0.0 0.0 )(session, company_id)
46
47 # # 회사별 라이센스 조회
48 1 716299000.0 716299000.0 90.0 available_licenses = LicenseUtils.license_check_all(company_name=company_name)
49
50 # # asset 별 모든 측정일과 이전 측정일 추출
51 1 14105000.0 14105000.0 1.8 asset_info = (
52 1 1000.0 1000.0 0.0 lambda session, company_id, available_licenses: session.query(
53 Asset.id.label("asset_id"),
54 Asset.asset_name.label("asset_name"),
55 DataMtrBodyDga.acquisition_date.label("acquisition_date"),
56 func.lead(DataMtrBodyDga.acquisition_date, 1)
57 .over(
58 partition_by=[Asset.id, Asset.asset_name],
59 order_by=DataMtrBodyDga.acquisition_date.desc(),
60 )
61 .label("prev_acquisition_date"),
62 )
63 .select_from(Asset)
64 .join(DataMtrBodyDga, DataMtrBodyDga.asset_id == Asset.id)
65 .filter(
66 Asset.company_id == company_id,
67 Asset.asset_type == "MTR",
68 Asset.serial_no.in_(available_licenses),
69 )
70 .order_by(Asset.id, Asset.asset_name, DataMtrBodyDga.acquisition_date.desc())
71 1 1000.0 1000.0 0.0 )(session, company_id, available_licenses)
72
73 1 11900000.0 11900000.0 1.5 print(asset_info)해당 테스트를 진행하면서, Lambda 를 활용하면 ORM 으로 성능도 잡을 수 있는 희망회로를 그릴 수 있나? 라는 기대를 가져봤지만, 아쉽게도 실제 여러 테스트를 통해서 내린 결론으로는,, 희망편도 절망편도 되지 못한 애매모호한 녀석이었습니다.
Lambda를 활용한 방식은 활용도가 제한적이었고, 이전글에서 작성한 것과 같이 복잡한 ORM 은 Lambda 를 통해서 모두 구현할 수가 없었습니다.
좀 더 구체적으로, 위에 작성했었던 것과 같이 단순 테이블을 조인해서 사용하는 것은 Lambda로 가능하지만, 쿼리 블록을 또 다른 테이블처럼 만들어 subquery로서 활용하는 것은 불가능 했습니다.
믿었던 ChatGPT 와 30~40 번의 토론을 거쳤음에도... 비슷한 답변을 보여줬습니다.
ChatGPT 도 그럴듯한 답변에는 문법적인 오류가 있는 답변만을 열거하는 식의 연속이었고, SQL 튜닝 관점에서 성능을 올리는 방법에 대한 답변을 마지막으로,, 복잡한 쿼리는 Raw SQL 을 대체할 만한 녀석이 없었다는 것으로 결론을 지었습니다.
그래도 일반적인 OLTP 시스템의 경우는 좋은 성능 향상 도구가 될 것라 생각됩니다.
.png )
"람다 기능을 사용하면 때떄로 성능이 향상될 수 있지만 항상 그런 것은 아닙니다. 귀하가 제공한 쿼리는 매우 복잡하며 람다 명령문으로 변환하는 것이 간단하지 않을 수 있습니다"
.png )
"복잡한 SQL 쿼리에 람다 함수를 사용하면 코드를 읽고 유지 관리하기가 더 어려워질 수 있으므로 주의해서 사용해야 합니다"
[성능 개선 Point 2]
또 다른 성능 개선 포인트로는 'View' 테이블을 활용하는 방법을 생각해 봤습니다.
사실 이것은 이전 테크리드 면접자리에서 얻은 인사이트였고 실천해 봤습니다..
이전편에서 사용한 쿼리처럼 License 서버에서 "available_license" list 를 가져와 IN 절에서 사용하는 SQL 쿼리는 또다른 View로 만들어 사용하기 어려운 점이 있지만, 이번에는 회사 id 를 사용해 설비의 상태를 가져오는 다른 API 를 사용해서 비교를 해봤습니다.
.png )
Before (ORM)
line-profiler 총 소요시간 : 1.81674 sec
Total time: 1.81674 s
File: line_profiler_test.py
Function: test_func at line 22
Line # Hits Time Per Hit % Time Line Contents
==============================================================
22 def test_func():
23 1 24000.0 24000.0 0.0 Session = sessionmaker(bind=engine)
24 1 146000.0 146000.0 0.0 session = Session()
25
26 1 1000.0 1000.0 0.0 company_id = 16
27
28 # asset 별 최신 DGA 측정일과 직전 측정일 조회
29 1 0.0 0.0 0.0 asset_info = (
30 1 151000.0 151000.0 0.0 session.query(
31 1 44000.0 44000.0 0.0 Asset.id.label("asset_id"),
32 1 1000.0 1000.0 0.0 Asset.asset_name,
33 1 4000.0 4000.0 0.0 DataMtrBodyDga.acquisition_date,
34 1 146000.0 146000.0 0.0 func.lead(DataMtrBodyDga.acquisition_date, 1)
35 .over(
36 1 1000.0 1000.0 0.0 partition_by=[Asset.id, Asset.asset_name],
37 1 0.0 0.0 0.0 order_by=[
38 1 1000.0 1000.0 0.0 Asset.id,
39 1 0.0 0.0 0.0 Asset.asset_name,
40 1 25000.0 25000.0 0.0 DataMtrBodyDga.acquisition_date.desc(),
41 ],
42 )
43 1 0.0 0.0 0.0 .label("prev_acquisition_date"),
44 )
45 1 0.0 0.0 0.0 .select_from(Asset)
46 .outerjoin(
47 1 0.0 0.0 0.0 DataMtrBodyDga,
48 1 20000.0 20000.0 0.0 Asset.id == DataMtrBodyDga.asset_id,
49 )
50 .filter(
51 1 43000.0 43000.0 0.0 Asset.company_id == company_id,
52 )
53 .order_by(
54 1 1000.0 1000.0 0.0 Asset.id,
55 1 0.0 0.0 0.0 Asset.asset_name,
56 1 12000.0 12000.0 0.0 DataMtrBodyDga.acquisition_date.desc(),
57 )
58 .subquery()
59 )
60
61 # asset 별 DGA 최신 측정날짜와 직전 측정날짜 조회
62 1 0.0 0.0 0.0 acq_date_by_asset = (
63 1 98000.0 98000.0 0.0 session.query(
64 1 165000.0 165000.0 0.0 asset_info.c.asset_id,
65 1 28000.0 28000.0 0.0 func.max(asset_info.c.acquisition_date).label("acquisition_date"),
66 1 38000.0 38000.0 0.0 case(
67 1 0.0 0.0 0.0 [
68 1 0.0 0.0 0.0 (
69 1 32000.0 32000.0 0.0 func.max(asset_info.c.prev_acquisition_date) == None,
70 1 14000.0 14000.0 0.0 func.max(asset_info.c.acquisition_date),
71 ),
72 1 0.0 0.0 0.0 (
73 1 23000.0 23000.0 0.0 func.max(asset_info.c.prev_acquisition_date) != None,
74 1 12000.0 12000.0 0.0 func.max(asset_info.c.prev_acquisition_date),
75 ),
76 ]
77 1 1000.0 1000.0 0.0 ).label("prev_acquisition_date"),
78 )
79 1 1000.0 1000.0 0.0 .group_by(asset_info.c.asset_id)
80 .subquery()
81 )
82
83 # 설비별 AI 프로세스 진행 여부
84 1 0.0 0.0 0.0 ai_process_state = (
85 1 104000.0 104000.0 0.0 session.query(
86 1 0.0 0.0 0.0 AiProcessStatus.asset_id,
87 1 8000.0 8000.0 0.0 literal("RUNNING").label("ai_process"),
88 )
89 1 27000.0 27000.0 0.0 .filter(AiProcessStatus.is_ai_process_finish == None)
90 1 0.0 0.0 0.0 .order_by(AiProcessStatus.asset_id)
91 .subquery()
92 )
93
94 # asset 의 DGA 날짜별 AI 진단 결과 조회
95 1 1000.0 1000.0 0.0 diag_info = (
96 1 143000.0 143000.0 0.0 session.query(
97 1 0.0 0.0 0.0 DataMtrBodyDga.asset_id,
98 1 0.0 0.0 0.0 DataMtrBodyDga.acquisition_date,
99 1 1000.0 1000.0 0.0 AiMtrBodyDga.ai_diagnosis_result,
100 )
101 1 0.0 0.0 0.0 .select_from(DataMtrBodyDga)
102 .outerjoin(
103 1 1000.0 1000.0 0.0 AiMtrBodyDga,
104 1 28000.0 28000.0 0.0 AiMtrBodyDga.data_mtr_body_dga_id == DataMtrBodyDga.id,
105 )
106 .subquery()
107 )
108
109 # asset 별 최신 DGA 측정일의 AI 진단 결과 조회
110 1 0.0 0.0 0.0 lts_query = (
111 1 136000.0 136000.0 0.0 session.query(
112 1 80000.0 80000.0 0.0 acq_date_by_asset.c.asset_id,
113 1 1000.0 1000.0 0.0 acq_date_by_asset.c.acquisition_date,
114 1 191000.0 191000.0 0.0 diag_info.c.ai_diagnosis_result,
115 1 54000.0 54000.0 0.0 ai_process_state.c.ai_process,
116 )
117 1 0.0 0.0 0.0 .select_from(acq_date_by_asset)
118 1 11000.0 11000.0 0.0 .join(diag_info, acq_date_by_asset.c.asset_id == diag_info.c.asset_id)
119 .outerjoin(
120 1 0.0 0.0 0.0 ai_process_state,
121 1 9000.0 9000.0 0.0 acq_date_by_asset.c.asset_id == ai_process_state.c.asset_id,
122 )
123 .filter(
124 1 10000.0 10000.0 0.0 acq_date_by_asset.c.acquisition_date == diag_info.c.acquisition_date,
125 )
126 .subquery()
127 )
128
129 # asset 별 직전 DGA 측정일의 AI 진단 결과 조회
130 1 0.0 0.0 0.0 prev_query = (
131 1 47000.0 47000.0 0.0 session.query(
132 1 1000.0 1000.0 0.0 acq_date_by_asset.c.asset_id,
133 1 0.0 0.0 0.0 acq_date_by_asset.c.acquisition_date,
134 1 0.0 0.0 0.0 diag_info.c.ai_diagnosis_result,
135 )
136 1 0.0 0.0 0.0 .select_from(acq_date_by_asset)
137 1 8000.0 8000.0 0.0 .join(diag_info, acq_date_by_asset.c.asset_id == diag_info.c.asset_id)
138 .filter(
139 1 8000.0 8000.0 0.0 acq_date_by_asset.c.prev_acquisition_date == diag_info.c.acquisition_date,
140 )
141 .subquery()
142 )
143
144 1 0.0 0.0 0.0 main_query = (
145 1 1799239000.0 1799239000.0 99.0 session.query(
146 1 4000.0 4000.0 0.0 Asset.id.label("asset_id"),
147 1 1000.0 1000.0 0.0 Asset.asset_name,
148 1 3000.0 3000.0 0.0 Site.id.label("site_id"),
149 1 0.0 0.0 0.0 Site.site_name,
150 1 0.0 0.0 0.0 Asset.voltage_low,
151 1 1000.0 1000.0 0.0 Asset.voltage_high,
152 1 0.0 0.0 0.0 Asset.capacity_low,
153 1 1000.0 1000.0 0.0 Asset.capacity_high,
154 1 0.0 0.0 0.0 Asset.manufacturer,
155 1 40000.0 40000.0 0.0 func.to_char(Asset.manufacturing_date, "YYYY-MM-DD").label(
156 1 1000.0 1000.0 0.0 "manufacturing_date"
157 ),
158 1 32000.0 32000.0 0.0 func.to_char(Asset.operating_date, "YYYY-MM-DD").label("operating_date"),
159 1 12000.0 12000.0 0.0 Asset.usage.label("usage"),
160 1 1000.0 1000.0 0.0 Asset.resource_id,
161 1 0.0 0.0 0.0 Asset.serial_no,
162 1 109000.0 109000.0 0.0 lts_query.c.acquisition_date,
163 1 32000.0 32000.0 0.0 case(
164 1 0.0 0.0 0.0 [
165 1 0.0 0.0 0.0 (
166 1 10000.0 10000.0 0.0 lts_query.c.ai_diagnosis_result
167 1 81000.0 81000.0 0.0 != prev_query.c.ai_diagnosis_result,
168 1 0.0 0.0 0.0 True,
169 ),
170 1 1000.0 1000.0 0.0 (
171 1 6000.0 6000.0 0.0 lts_query.c.ai_diagnosis_result
172 1 0.0 0.0 0.0 == prev_query.c.ai_diagnosis_result,
173 1 0.0 0.0 0.0 False,
174 ),
175 ]
176 1 0.0 0.0 0.0 ).label("is_state_changed"),
177 1 59000.0 59000.0 0.0 case(
178 1 0.0 0.0 0.0 [
179 1 21000.0 21000.0 0.0 (lts_query.c.ai_process == "RUNNING", "UNKNOWN"),
180 1 0.0 0.0 0.0 (
181 1 15000.0 15000.0 0.0 lts_query.c.ai_diagnosis_result == 0,
182 1 1000.0 1000.0 0.0 "NORMAL",
183 ),
184 1 0.0 0.0 0.0 (
185 1 12000.0 12000.0 0.0 lts_query.c.ai_diagnosis_result == 1,
186 1 1000.0 1000.0 0.0 "CAUTION",
187 ),
188 1 0.0 0.0 0.0 (
189 1 12000.0 12000.0 0.0 lts_query.c.ai_diagnosis_result == 2,
190 1 0.0 0.0 0.0 "WARNING",
191 ),
192 1 0.0 0.0 0.0 (
193 1 11000.0 11000.0 0.0 lts_query.c.ai_diagnosis_result == 3,
194 1 1000.0 1000.0 0.0 "CRITICAL",
195 ),
196 1 0.0 0.0 0.0 (
197 1 11000.0 11000.0 0.0 lts_query.c.ai_diagnosis_result == 4,
198 1 1000.0 1000.0 0.0 "FAULT",
199 ),
200 ]
201 1 0.0 0.0 0.0 ).label("asset_state"),
202 )
203 1 0.0 0.0 0.0 .select_from(Asset)
204 .join(
205 1 0.0 0.0 0.0 Site,
206 1 20000.0 20000.0 0.0 and_(
207 1 21000.0 21000.0 0.0 Asset.site_id == Site.id,
208 1 16000.0 16000.0 0.0 Asset.company_id == company_id,
209 ),
210 )
211 .join(
212 1 0.0 0.0 0.0 lts_query,
213 1 10000.0 10000.0 0.0 Asset.id == lts_query.c.asset_id,
214 )
215 .join(
216 1 0.0 0.0 0.0 prev_query,
217 1 11000.0 11000.0 0.0 Asset.id == prev_query.c.asset_id,
218 )
219 1 0.0 0.0 0.0 .order_by(Asset.id)
220 .all()
221 )
222
223 1 1000.0 1000.0 0.0 responses = []
224 3507 1123000.0 320.2 0.1 for data in main_query:
225 3507 6683000.0 1905.6 0.4 obj = data._asdict()
226 3507 3677000.0 1048.5 0.2 obj = key_mapping.voltage(obj)
227 3507 2206000.0 629.0 0.1 obj = key_mapping.capacity(obj)
228
229 3507 1356000.0 386.7 0.1 responses.append(obj)View table 생성 (company_id 16)
필요에 따라서 각 회사별 View 테이블을 만들어서 필요한 시점에 해당 테이블을 불러와 사용할 수 있을 것으로 보입니다.
CREATE VIEW temp_view_company_16 AS (
-- [설비별 최신 계측날짜, 직전 계측날짜]
WITH acq_date_by_asset AS (
SELECT a.asset_id, MAX(a.acquisition_date) AS acquisition_date,
CASE WHEN MAX(a.prev_acquisition_date) IS NULL THEN MAX(a.acquisition_date)
ELSE MAX(a.prev_acquisition_date) END
AS prev_acquisition_date
FROM (
SELECT a.id AS asset_id,
a.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 LEFT JOIN substation.data_mtr_body_dga dmbd
ON a.id = dmbd.asset_id
WHERE a.company_id = 16
ORDER BY a.id, a.asset_name ,dmbd.acquisition_date DESC
) a
GROUP BY a.asset_id
),
-- [설비별 AI 프로세스 진행 여부]
ai_process_state AS (
SELECT aps.asset_id ,'RUNNING' AS ai_process
FROM substation.ai_process_status aps
WHERE is_ai_process_finish IS NULL
ORDER BY aps.asset_id
),
-- [설비 DGA 계측 날짜별 AI 연산 결과]
diag_info AS (
SELECT dmbd.asset_id , dmbd.acquisition_date , ambd.ai_diagnosis_result
FROM substation.data_mtr_body_dga dmbd LEFT JOIN substation.ai_mtr_body_dga ambd
ON dmbd.id = ambd.data_mtr_body_dga_id
)
SELECT a.id as asset_id, a.asset_name, s.id AS site_id, s.site_name
, a.voltage_low, a.voltage_high, a.capacity_low, a.capacity_high, a.manufacturer
, TO_CHAR(a.manufacturing_date,'YYYY-MM-DD') AS manufacturing_date
, TO_CHAR(a.operating_date,'YYYY-MM-DD') AS operating_date
, a.USAGE, a.resource_id, a.serial_no
, lts_query.acquisition_date
, CASE WHEN lts_query.ai_process = 'RUNNING' THEN 'UNKNOWN'
WHEN lts_query.ai_diagnosis_result = 0 THEN 'NORMAL'
WHEN lts_query.ai_diagnosis_result = 1 THEN 'CAUTION'
WHEN lts_query.ai_diagnosis_result = 2 THEN 'WARNING'
WHEN lts_query.ai_diagnosis_result = 3 THEN 'CRITICAL'
WHEN lts_query.ai_diagnosis_result = 4 THEN 'FAULT'
END AS asset_state
, CASE WHEN lts_query.ai_diagnosis_result <> prev_query.ai_diagnosis_result THEN TRUE
ELSE FALSE END
AS is_state_changed
FROM substation.asset a ,
substation.site s ,(
SELECT a.asset_id, b.ai_diagnosis_result, a.acquisition_date, aps.ai_process
FROM acq_date_by_asset a INNER JOIN diag_info b
ON a.asset_id = b.asset_id LEFT JOIN ai_process_state aps
ON a.asset_id = aps.asset_id
WHERE a.acquisition_date = b.acquisition_date
) lts_query,
(
SELECT a.asset_id, b.ai_diagnosis_result ,a.acquisition_date
FROM acq_date_by_asset a INNER JOIN diag_info b
ON a.asset_id = b.asset_id
WHERE a.prev_acquisition_date = b.acquisition_date
) prev_query
WHERE a.site_id = s.id
AND a.company_id = 16
AND a.id = lts_query.asset_id
AND a.id = prev_query.asset_id
ORDER BY a.id
)View Entity 생성
class TempViewCompany16(Base):
__tablename__ = "temp_view_company_16"
__table_args__ = {"schema": "substation"}
# Columns
asset_id = Column("asset_id", Integer, primary_key=True)
asset_name = Column("asset_name", String)
site_id = Column("site_id", Integer)
site_name = Column("site_name", String)
voltage_low = Column("voltage_low", Numeric())
voltage_high = Column("voltage_high", Numeric())
capacity_low = Column("capacity_low", Numeric())
capacity_high = Column("capacity_high", Numeric())
manufacturer = Column("manufacturer", String)
manufacturing_date = Column("manufacturing_date", DateTime(timezone=True))
operating_date = Column("operating_date", DateTime(timezone=True))
usage = Column("usage", String)
resource_id = Column("resource_id", String)
serial_no = Column("serial_no", String)
acquisition_date = Column("acquisition_date", DateTime(timezone=True))
asset_state = Column("asset_state", String)
is_state_changed = Column("is_state_changed", Boolean)
After (View ORM)
line-profiler 총 소요시간 : 1.81674 sec
Total time: 1.47011 s
File: line_profiler_test_copy.py
Function: test_func at line 31
Line # Hits Time Per Hit % Time Line Contents
==============================================================
31 def test_func():
32 1 13000.0 13000.0 0.0 Session = sessionmaker(bind=engine)
33 1 130000.0 130000.0 0.0 session = Session()
34 1 0.0 0.0 0.0 company_id = 16
35
36 1 1000.0 1000.0 0.0 start = time.time()
37 1 156199000.0 156199000.0 10.6 math.factorial(100000)
38
39 1 1302447000.0 1302447000.0 88.6 results = session.query(
40 1 10000.0 10000.0 0.0 TempViewCompany16.asset_id,
41 1 0.0 0.0 0.0 TempViewCompany16.asset_name,
42 1 1000.0 1000.0 0.0 TempViewCompany16.site_id,
43 1 1000.0 1000.0 0.0 TempViewCompany16.site_name,
44 1 1000.0 1000.0 0.0 TempViewCompany16.voltage_low,
45 1 1000.0 1000.0 0.0 TempViewCompany16.voltage_high,
46 1 0.0 0.0 0.0 TempViewCompany16.capacity_low,
47 1 0.0 0.0 0.0 TempViewCompany16.capacity_high,
48 1 1000.0 1000.0 0.0 TempViewCompany16.manufacturer,
49 1 1000.0 1000.0 0.0 TempViewCompany16.manufacturing_date,
50 1 0.0 0.0 0.0 TempViewCompany16.operating_date,
51 1 0.0 0.0 0.0 TempViewCompany16.usage,
52 1 0.0 0.0 0.0 TempViewCompany16.resource_id,
53 1 1000.0 1000.0 0.0 TempViewCompany16.serial_no,
54 1 0.0 0.0 0.0 TempViewCompany16.acquisition_date,
55 1 0.0 0.0 0.0 TempViewCompany16.asset_state,
56 1 1000.0 1000.0 0.0 TempViewCompany16.is_state_changed,
57 ).all()
58
59 1 0.0 0.0 0.0 responses = []
60 3507 556000.0 158.5 0.0 for data in results:
61 3507 5282000.0 1506.1 0.4 obj = data._asdict()
62 3507 3085000.0 879.7 0.2 obj = key_mapping.voltage(obj)
63 3507 1493000.0 425.7 0.1 obj = key_mapping.capacity(obj)
64
65 3507 830000.0 236.7 0.1 responses.append(obj)
66
67 1 2000.0 2000.0 0.0 end = time.time()
68 1 40000.0 40000.0 0.0 print("****************************************************")
69 1 10000.0 10000.0 0.0 print(f"{end - start:.5f} sec")
70 1 2000.0 2000.0 0.0 print("****ls************************************************")
71 1 0.0 0.0 0.0 return responses또한 View 테이블을 사용할 경우 DB의 성능에 좀 더 영향을 받을 것으로 보이며, 테스트로 사용 했었던 local DB 가 아닌 고 성능의 DB 를 사용했다면 더 성능 향상이 좋아질 것으로 보입니다. 참고로 Raw SQL 로 측정할 경우 1.05664 초로 각각 0.8 ,0.4 초의 성능 차이를 보여줬습니다.
정리하자면 ORM 을 잘 사용한다면 Raw SQL 과 비슷한 정도로 구현이 가능할 것으로 보입니다.
하지만 잘 사용하기 위해서는 기본적으로 SQL 에 대해서, 그리고 SQL 튜닝 관점에서의 지식이 밑바탕 되어야 할 것으로 보이며, 현 시점에서는 JPA ORM 강의를 열심힘 들었던... '김영한' 님과 비슷한 생각을 갖게 되었습니다.
[ ORM 의 특장점 ]
SQL과 ORM에 대해서 파고들고, 각 진영의 토론 배틀을 깊숙이 지켜본 사람으로서, ORM 만이 가지고 있는 특장점이 존재합니다. C#, Java, Python 등 객체지향 언어를 사용해서 애플리케이션을 개발 하게 된다면, 객체지향 설계 원칙을 따르게 되지만, RDBMS 는 이러한 객체지향 구조로 맞물려 개발하기에 어려움이 있습니다.
예를 들어 INSERT 의 경우, ORM 은 필요한 클래스에 필드를 추가하면 해당 테이블에 필드가 자동으로 추가되지만, Raw SQL 을 사용하는 경우에는 해당 테이블을 사용하는 모든 SQL을 수정해야 합니다. Legacy 코드가 많을 수록 이 작업은 번거로워 질 뿐만 아니라, 객체지향적 개발에서 멀어질 수 있습니다.
또한 아래 같은 경우에서는 ORM 이 더 막강한 성능을 낼 수 있습니다.
| A 라는 객체는 B,C,D,E 와 연관되어 있고, B 와 C 는 필수적으로 가져와야 하지만, D,E 는 선택적으로 가져와야 하는 상황일 경우 |
[ORM 을 사용하는 경우] - ORM 을 사용하면 A 객체를 로드할 때 B 와 C를 가져오는 쿼리를 실행합니다. 이때 B 와 C 의 정보가 필요한 경우 쿼리를 실행하여 가져옵니다. - A 객체를 사용하는 코드에서 D 나 E 에 접근이 필요한 경우, ORM 은 필요한 쿼리를 추가적으로 실행하여 D 와 E 의 정보를 가져옵니다. 이는 객체 그래프를 탐색하여 필요한 정보를 동적으로 가져올 수 있게 해줍니다. |
[Raw SQL 을 사용하는경우] - SQL 을 사용하여 B 와 C 를 가져오는 쿼리를 실행합니다. A 객체를 생성할 때 B 와 C 의 정보를 가져와서 A 객체에 매핑합니다. - D 나 E 에 접근이 필요한 경우, 추가적인 SQL 쿼리를 실행하여 D와 E 의 정보를 가져옵니다. 이를 위해 A 객체를 생성할 때 사용한 쿼리와 별도의 쿼리를 실행해야 합니다. - 이렇게 되면 처음부터 B,C,D,E 를 모두 가져오는 것은 비효율적입니다. 필요한 정보를 가져오기 위해 여러번의 쿼리를 실행해야 하므로 성능 저하와 불필요한 데이터 로딩이 발생합니다. |
[ 마치며.. ]
이전 포스팅 글과 이번 글을 함께 보신 분이라면, 각각 SQL 과 ORM 의 관점에서 쓴 글처럼 보일 수 있을 것 같습니다.
SW 엔지니어는 결국 마주하는 이슈를 해결해 나가는 사람들이고, 이러한 이슈를 해결하기 위해서 도구에 종속적인 것보다는 문제를 해결해 나가는 것에 더 초점을 맞춰야 한다고 생각합니다. 이러한 측면에서 본다면 사실 SQL 이나 ORM 같은 특정 도구에 치우치기 보다는 현재 상황에 맞게 최적의 선택을 하는 것이 더 중요한 것 같습니다.
다만 '도메인 주도 설계(DDD)' 나' 마이크로 서비스 아키텍처(MSA)' 가 트렌드가 됨에 따라서, 트렌디한 개발 환경을 가진 곳인 경우, 기존의 모놀리식 시스템에서 분리해, 서비스마다 독자적인 DB 를 갖거나, NoSql 같은 관계형 DB 에서 벗어난 아키텍처를 채택하고 있습니다.
이러한 방향은 모놀리식 구조에 비해 SQL 조인 복잡성이 줄어들게 되어 ORM 으로 충분히 성능을 챙길 수 있는 환경이 만들어지게 되고, ORM 을 사용하면 기존 SQL 이 가지고 있는 수많은 Mapping 이슈도 해결이 될 수 있는 순방향 Cycle 이 형성될 수 있다고 생각합니다.
하지만 한편으론 제조 대기업에서 일하면서 이미 이러한 방법을 알고 있는 실력 있는 '개발자' 나 '아키텍처' 가 많았을 텐데, 왜 그 사람들이 모여서 결국 거대한 모놀리식 아키텍처를 선택했을까? 를 다시금 생각해보면, 세상에 만능 '치트키' 는 없듯이 풀어나가야하는 이슈마다 최적의 선택이 다르지 않을까 싶습니다.
[Reference]