1. 서론

화창하던 어느날. 여느때와 다름없이 개발을 하던 중 API호출 중 조회 해 올 때 갑자기 결과 값이 두개 이상이다. 라는 error messsage를 마주하였다.

왜 그런지 원인을 파악하고 쿼리를 수정을 하여 현재는 정상적으로 동작하고 그 원인과 해결한 방법에 대해서 정리하려고 한다.

2. 테이블 구조

fatory, output, system_code 이 세가지 table을 이용하여 공장 별 output을 보기 좋게 조회 하는 쿼리를 작성하는 것이었다.

처음에 쿼리를 작성 할 때 단순히 JOIN을 통해서 할 수 있겠다는 생각을 하였고 그렇게 하면 되었다.

하지만 위에 그림 처럼 system_code테이블에서 여러가지 값들을 JOIN해서 가져오기 떄문에 나는 JOIN 보다 subquery를 통해서 가져오면 좋겠다고 쉽게 생각하고 접근을 하였다.

3.문제의 발생

3.1 문제의 쿼리

아래 쿼리에 작성한것 처럼 여러번의 subquery를 통해서 조회를 해오다 보니 속도도 많이 느렸고 만약 중복되는 값이 있으면 (2개 이상의 값) 이 있다면 서론에 서술한 것 처럼 error를 반환하였다.

하지만 2개 이상의 값을 반환해줄 경우에는 limit 1 을 해줌으로써 해결할 수 있었으나, 속도도 느리고 내가 값에 대한 신뢰도가 떨어진다고 생각했다.

select
	tho.year_tp
	tho.component_item_number as item_grp_ed,
	(select cd nm from posh122.tb h122 sys code where standard english id = 'TERRITORY_CD_N' and cd v = thf.territory cd n and cd_nm !='' ) as nation_nm,
	(select cd v from posh122.tb h122 sys code where standard english id = 'TERRITORY_CD_N' and cd_v = thf.territory_cd_n and cd_v !='' ) as nation_cd,
 	(select attribute2 from poshi22.tb h122 sys code where standard english id = 'TERRITORY CD N' and cd v = thf.territory_cd_n and attribute2 != '' ) as nation cd 2,
	(select attribute3 from posh122.tb_h122-sys_code where standard_english_id = 'TERRITORŸ _CO_N' and cd_v = thf.territory_cd~n and attribute3 != '') as nation center lat,
	(select attribute4 from posh122.tb h122 sys code where standard_english_id = 'TERRITORY_CD_N' and cd_v = thf.territory_ed _n and attribute4 != '' )as nation_center__Ing,
	( select cd_nm from posh122.tb_h122_sys_code as sub where sub.cd_v = tho.component_item_number ) as item_grp_nm,
	substring(tho.component_item_number, 3,1): :integer as item_grp_ord,
	case
		when thsc.cd_m is null
		then
		else thsc.cd nm 
	end as item_nm,
	case
		when tho. component_item number = 'A030'
		then 'A03010' else tho.component_item_number1
	end as item ed,
	sum(tho.product_qty) as product_qty,
	case 
		when thsc.attribute is null
		then 'Y' 
		else thsc.attribute2
	end as attribute2,
	thsc.attributel
from 
	posh122.tb h122_output tho
left join poshi22.tb h122 factory thf
	on (tho.sequence number = thf.sequence number)
left join posh122.tb h122 sys code thsc
	on (case when tho.component_item_number ='A030' then 'A03010' else tho.component_item _number1 end) = thsc.cd_v
where 
	tho.product_qty > 0
	and (case 
			when thsc.attribute2 is null
			then 'Y'
			else thsc.attribute2
		end) = 'Y'
group by
	tho.year_tp,
	tho. component_item_number,
	tho.component_item_number1,
	thsc.cd nm thsc.cd_v, 
	thf. territory_ed_n, 
	thsc.attribute2,
	thsc.attributel, 
	thsc.attribute4

3.2 개선한 쿼리

처음에 작성한 쿼리에서 발견한 여러가지 문제를 해결하기위해서 테이블을 INNER JOIN을 통해서 해당되는 값들을 가져오게끔 하였다.
이렇게 바꿧더니 가독성도 좋고 속도도 향상된 결과를 마주할 수 있었다..ㅠ
나는 바보인가..

SELECT
 	THO.YEAR_TP
	THO.COMPONENT_ITEM_NUMBER 							AS ITEM_GRP_ED, 
	THSC2.CD_NM 										AS ITEM_GRP_NM,
	CASE
		WHEN THO. COMPONENT_ITEM NUMBER = 'A030'
		THEN 'A03010' ELSE THO.COMPONENT_ITEM_NUMBER1
	END 												AS ITEM_ED,
		CASE WHEN THSC3.CD NM IS NULL
		then ''
		ELSE THSC3.CD_NM 
	END 												AS ITEM NM,
	SUBSTRING(THO.COMPONENT_ITEM_NUMBER,3,1)::INTEGER 	AS ITEM_GRP_ORD, 
	SUM(THO.PRODUCT_QTY) 								AS PRODUCT_QTY, 
	THF.TERRITORY_CD_N 									AS NATION_ED, 
	THSC.ATTRIBUTE2 									AS NATION_ED_2, 
	THSC.CD_NM 											AS NATION_NM,
	THF.AREA_TYPE_N,
	THSC.ATTRIBUTE3 									AS NATION_CENTER_LAT, 
	THSC.ATTRIBUTE4 									AS NATION_CENTER_ING, 
	THSC2.ATTRIBUTEL 									AS MAIN_ITEM_ATTR,
	CASE 
		WHEN THSC3.ATTRIBUTE2 IS NULL
		THEN 'N' 
		ELSE THSC3.ATTRIBUTE2
	END 												AS SUB ITEM ATTR 
FROM 
	POSH122.TB H122_OUTPUT THO 
	INNER JOIN POSH122.TB_H122_FACTORY THF
		ON (THO.SEQUENCE_NUMBER = THF.SEQUENCE_NUMBER)
	INNER JOIN POSH122.TB H122 SYS CODE THSC
		ON (THSC.CD_V = THF. TERRITORY_CD_N
			AND THSC.STANDARD_ENGLISH ID = 'TERRITORY CD N')
	INNER JOIN POSH122.TB_H122_SYS_CODE THSC2
		ON (THSC2.CD _V = THO. COMPONENT_ITEM _NUMBER
			AND THSC2.STANDARD ENGLISH ID = 'COMPONENT ITEM NUMBER' )
	LEFT JOIN POSH122.TB_H122_SYS_CODE THSC3
		ON (THSC3.CD V = THO. COMPONENT ITEM NUMBER1
			AND THSC3.STANDARD ENGLISH ID = 'COMPONENT ITEM NUMBER1' )
WHERE THO.PRODUCT ATY > 0
GROUP BY
	THO.YEAR_TP,
	THO.COMPONENT_ITEM_NUMBER, 
	THO.COMPONENT_ITEM_NUMBER1, 
	THSC2.CD_NM,
	THSC3.CD_NM, 
	THF.TERRITORY_CD_N, 
	THSC.ATTRIBUTE2,

3.3 속도 비교

• execution time
  실제로 쿼리를 샐행하는데 걸리는 시간을 의미.
• planning time
  쿼리 실행 계획을 수립하는데 걸리는 시간. 쿼리의 복잡성과 데이터의 크기에 따라 다양하게 변할 수 있다.

postgresql을 사용하고 있어서 EXPLAIN ANALYZE 를 이용하여 쿼리 성능을 평가해보았다.
그리고 execution time을 서로 비교해보았다.

그 결과 개선한 쿼리가 기존보다 1.971(ms)더 빨라 진걸 확인할 수 있었다.

이걸 보고 내가 얼마나 개똥같아 쿼리를 작성했는지 한번 깨닫게 되었다.

그리고 planning time의 경우 subquery를 사용했을 때 더 낮게 나왔는데 이는 subquery의 경우 쿼리 내에서 다른 쿼리를 사용하여 데이터를 추출하기 때문에 JOIN을 사용한 것보다 더 낮게 나올 수 있다고 한다.

3.3.1 문제의 쿼리 속도 (subquery)

항목	time (ms)
execution time	0.487
planning time	5.859

3.3.2 개선 쿼리 속도 (JOIN)

항목	time (ms)
execution time	0.620
planning time	3.888

4. 그럼 둘이 뭐가 달라?

그럼 왜 이런 차이를 보여지는 걸까?

4.1 SUBQUERY

서브쿼리는 쿼리 내에서 새로운 쿼리를 동적으로 조회 하기 때문에 새로운 테이블을 불러서 조회하는거나 다름없다. 서브쿼리의 경우 동적으로 데이터를 필터링하거나 결합하는 용도로 쓰이고, 반드시 단일 행, 또는 열로 반환이 되어야 한다.

하짐만, 나는 두개 이상의 값을 반환할 수 있는 가능성을 열어두었고, 굳이 닭잡는데 소 잡는 칼을 쓴 격이라고 생각이 든다.

4.2 JOIN

조인의 경우 ON 을 통해서 조건을 걸 수 있는데, 이게 where 절보다 좀 더 빠르다.

ON을 할 경우 조회하는 테이블에서 조건에 맞는 값들만 먼저 불러 오기 떄문에 테이블도 가볍고 만약 2개 이상의 값이 있어도 Error가 발생하는게 아니라 컬럼이 하나 추가 되게 된다.

그래서 조건을 잘 걸어야 한다는 생각이 든다.

4.2.1 JOIN의 ON 과 where 절 차이

5. 결론

아직 sql공부가 많이 필요할 거 같다.. ㅠㅠ
다른 사람들이 짠 쿼리들 보면 서브쿼리를 정말 자유자재로 사용해서 동적으로 잘 조회 해오던데..ㅠ 나도 그렇게 자유자재로 잘 쓰고 싶당..

그래도 이번 기회에 join과 suquery에 대해서 자세히 공부할 수 있었고, 그 차이에 대해서 배울 수 있었으니 내 개발 생활에 꼭 필요한 시간이었다고 생각한다.

나의 실수는 나를 성장 시키는 발판이라고 생각하며 마무리 해야겠다.