데이터베이스 질문과 답변
안녕하세요, 선배님들. 현재, 시스템의 어떠한 함수에서 다음의 쿼리를 네 번 실행합니다. 다만, WHERE 조건에서 A3(T_ITEM) 테이블의 C_PROCURE 값만 변경하여 쿼리를 실행합니다. (EX: A3.C_PROCURE = 'A' / A3.C_PROCURE = 'B' / A3.C_PROCURE = 'C') <<< 이러한 형태로 말이지요. 각 테이블마다 16~17만건 가까이 레코드가 쌓여있기에 쿼리 최적화가 필요할 듯한데요. WHERE 조건 하나 때문에 아래의 쿼리를 네 번 실행한다는 건 효율성이 떨어진다는 판단을 하였습니다. 결론적으로 A, B, C, D 데이터는 각각의 데이터이나, C_PROCURE를 좀 더 효율적으로 처리할 수 있는 방법이 있을까요? 선배님들의 조언과 피드백 미리 감사드립니다 :)
SELECT
A1.SALES_ORDER_NO -- 판매 주문 번호
, A1.SALES_YMD -- 판매 일자
, A1.ORDER_DIV_CODE -- 소속 코드 -발주
, A1.ITEM_SEQ -- 제품 일련 번호
, A1.ABBR_CODE -- 단축 코드 (바코드)
, A1.TRN_UNIT_AMT -- 판매 단가
, A1.SALES_CNT -- 판매 수량
, A1.SUM_AMT -- 판매 합계 금액
, A1.CLT_INDIV_SEQ -- 개인 고객 일련번호
, A1.REL_NAME -- 관계자명
, A1.C_SALE_PROC -- 판매 처리 공통 코드
, A1.C_PYMT_MTHD -- 결제 방식 공통 코드
, A1.RATE_DC -- 할인률
, A1.RMK_REPARE -- 수선 비고
, A2.DIV_NAME -- 소속명
, A3.ITEM_NAME -- 제품 자재명
, A3.C_PROCURE -- 조달 구분 공통 코드
, A4.CLT_INDIV_NAME -- 개인 고객명
, A4.C_CLT_INDIV -- 개인 고객 유형 공통 코드
, (CASE WHEN (A4.C_CLT_INDIV IS NOT NULL) THEN (SELECT CODE_NAME FROM T_CODE WHERE CODE = A4.C_CLT_INDIV) ELSE '' END) AS DUTY_NAME -- 개인 고객 유형
, A5.CLT_ORG_NAME -- 기관 고객명
, A5.CLT_ORG_SEQ -- 기관 고객 일련번호
-- , A6.C_PYMT_MTHD -- 이전 결제 방식 공통 코드
, A6.C_PYMT_TYPE -- 결제 종류 공통 코드
, A7.CODE_NAME AS COLOR -- 색상
, A8.CODE_NAME AS FABRIC -- 원단
FROM
T_SALES AS A1 -- 판매
LEFT OUTER JOIN T_DIVISION AS A2 ON A1.ORDER_DIV_CODE = A2.DIV_CODE AND (A2.C_DIV_TYPE = 'C' AND A2.USE_YN = 'Y') -- 센터
LEFT OUTER JOIN T_ITEM AS A3 ON A1.ITEM_SEQ = A3.ITEM_SEQ -- 제품
LEFT OUTER JOIN T_CLIENT_INDIV AS A4 ON A1.CLT_INDIV_SEQ = A4.CLT_INDIV_SEQ -- 개인 고객
LEFT OUTER JOIN T_CLIENT_ORG AS A5 ON A1.C_DIOCESE = A5.C_DIOCESE AND A1.CLT_ORG_SEQ = A5.CLT_ORG_SEQ -- 고객 유형 (사목지)
LEFT OUTER JOIN (
SELECT SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
FROM (
SELECT SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
FROM T_PAYMENT
limit 100
) AS P
GROUP BY SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
LIMIT 1
) AS A6 ON A1.SALES_ORDER_NO = A6.SALES_ORDER_NO AND A1.C_PYMT_MTHD = A6.C_PYMT_MTHD -- 결제
LEFT OUTER JOIN (SELECT CODE, CODE_NAME FROM T_CODE WHERE UPP_CODE = 'C_COLOR') AS A7 ON A3.C_COLOR = A7.CODE -- 색상
LEFT OUTER JOIN (SELECT CODE, CODE_NAME FROM T_CODE WHERE UPP_CODE = 'C_FABRIC') AS A8 ON A3.C_FABRIC = A8.CODE -- 원단
WHERE
(A1.C_SALE_PROC = 2 OR A1.C_SALE_PROC = 3) -- 판매 처리 공통 코드 (Pix)
AND A3.C_ITEM_USE != 'B' -- 제품 사용 구분 공통 코드 (Pix)
AND A3.C_PROCURE = #{cProcure} -- 조달 구분 공통 코드 (Required)
4번 실행한 결과를 어떻게 사용하나요?
한번에 다 나와야 한다면? 그냥 IN 조건 쓰면 되죠.
A3.C_PROCURE IN (1, 2, 3, 4)
마농 선생님, 답변 감사드립니다!
한 번에 다 나와야 하는 결과가 아닌,
한 페이지에 네 개의 영역이 있고,
각 영역마다 1, 2, 3, 4에 해당하는 데이터를 필요로 합니다.
객체 하나당 1, 2, 3, 4번 각각이 실행되어야 합니다.
쿼리를 보면 이상한 부분이 많네요.
조회 항목은 A1 만 사용되고 있는데?
나머지 항목들은 왜 조인을 하죠?
A3 은 그나마 조건절이라도 사용하는데, 나머지 들은 전혀 사용이 안되고 있네요.
특히나 A6 부분의 LIMIT 는 이해가 안가는 부분이네요.
아.. 나머지 항목들도 쓰이고 있습니다만, 쿼리 문이 너무 길어지기에 제외했습니다 ^^..
A6의 LIMIT은 임의로 넣어둔 쿼리이기에 신경쓰지 않아주셔도 됩니다!
전체 쿼리를 보고 비효율을 찾아야 하는데
일부 감추거나 왜곡된 쿼리를 보고 비효율을 찾기 힙듭니다.
쿼리가 느리다면? 인덱스 정보, 건수 정보 등을 여러 정보들을 알려 주셔야 합니다.
쿼리가 느려서 질문하신 건가요?
아니면 단순히 4번 호출하는게 맘에 들지 않아 질문하신 건가요?
전체 쿼리를 다시 올리는 게 좋을까요?
인덱스는 하나도 걸려있지 않은 상황입니다...
건수는 테이블당 170,000개 정도의 레코드가 포함된 상태입니다.
쿼리가 느리다기보다, 마지막에 말씀하신 것처럼,
네 번의 호출이 걸려서 질문을 올리게 되었습니다!
속도적으로 문제가 없다면, 네 번의 호출밖에는 방법이 없는 걸까요?
답변 감사드립니다, 마농 선생님 :)
select
A1.SALES_ORDER_NO -- 판매 주문 번호
, A1.SALES_YMD -- 판매 일자
, A1.ORDER_DIV_CODE -- 소속 코드 -발주
, A1.ITEM_SEQ -- 제품 일련 번호
, A1.ABBR_CODE -- 단축 코드 (바코드)
, A1.TRN_UNIT_AMT -- 판매 단가
, A1.SALES_CNT -- 판매 수량
, A1.SUM_AMT -- 판매 합계 금액
, A1.CLT_INDIV_SEQ -- 개인 고객 일련번호
,A3.C_PROCURE -- 페이지 구분자
from
t_sales as A1 -- 판매
left outer join t_division as A2 on A1.ORDER_DIV_CODE = A2.DIV_CODE and (A2.C_DIV_TYPE = 'C' and A2.USE_YN = 'Y') -- 센터
left outer join t_item as A3 on A1.ITEM_SEQ = A3.ITEM_SEQ -- 제품
AND A3.C_PROCURE IN ('A','B','C','D')
left outer join t_client_indiv as A4 on A1.CLT_INDIV_SEQ = A4.CLT_INDIV_SEQ -- 개인 고객
left outer join t_client_org as A5 on A1.C_DIOCESE = A5.C_DIOCESE and A1.CLT_ORG_SEQ = A5.CLT_ORG_SEQ -- 고객 유형 (사목지) >>>>> 추가 확인 필요
left outer join (
SELECT SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
FROM (
SELECT SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
FROM T_PAYMENT
limit 100
) AS P
GROUP BY SALES_ORDER_NO, C_PYMT_TYPE, C_PYMT_MTHD
LIMIT 1
) as A6 on A1.SALES_ORDER_NO = A6.SALES_ORDER_NO and A1.C_PYMT_MTHD = A6.C_PYMT_MTHD -- 결제
LEFT OUTER JOIN (SELECT CODE, CODE_NAME FROM T_CODE WHERE UPP_CODE = 'C_COLOR') AS A7 ON A3.C_COLOR = A7.CODE -- 색상
LEFT OUTER JOIN (SELECT CODE, CODE_NAME FROM T_CODE WHERE UPP_CODE = 'C_FABRIC') AS A8 ON A3.C_FABRIC = A8.CODE -- 원단
where
(A1.C_SALE_PROC = '2' or A1.C_SALE_PROC = '3')
and A3.C_ITEM_USE != 'B'
and A3.C_PROCURE = 2
and A1.ORDER_DIV_CODE = 'C16'
AND (A1.SALES_YMD >= '20150101' AND A1.SALES_YMD <= '20150201') -- 판매일
order by
A1.SALES_YMD, A1.SALES_ORDER_NO, SALES_SORT_ORD
;
이런식으로 처리하면 될까 싶네요.
우주민님, 답변 감사드립니다 :)
한 객체에 A, B, C, D에 대한 데이터가 필요한 것이 아닌,
네 개의 리스트 각각에 A, B, C, D에 대한 데이터가 필요한 상황이라,
IN 절은 사용할 수 없는 상황입니다!
A3.C_PROCURE -- 페이지 구분자
이 부분으로 구분지어 사용하는 형태를 생각했는데....
그냥 쿼리 셋 4개가 필요한가 보군요.
인덱스가 하나도 없는데 느리지 않다는것은 이해가 좀 안가구요.
쿼리를 고치신 듯 한데? 의문의 LIMIT 는 그대로네요.
4번 호출이 싫다면? 1번 호출 후 어플리케이션에서 나누어 사용하는 벙법도 있겠지요.
LEFT 조인 일색인데? INNER JOIN 과 OUTER JOIN 을 적재 적소에 구별하여 사용할 필요가 있습니다.
무분별한 OUTER JOIN 사용은 지양해야 합니다.
A3 는 Where 절 조건을 주고 있으므로 아우터 조인이 아닌 이너조인이 맞습니다.
나머지 테이블들은 진짜로 아우터 조인이 필요한지 고민이 필요합니다.
답변 감사드립니다, 선생님 :)
솔직히 말씀드리자면, 기존에 ASP(MSSQL)로 구동하던 시스템을
이번에 JAVA(MYSQL) 환경으로 리뉴얼 및 마이그레이션 하게 되었는데요.
변명으로 느끼실 수 있겠지만, 회사 성격상, "일정이 우선이고 성능은 그 뒤에 개선하자"라는 마인드입니다..
우선은 기능을 구현해두고, 선생님 말씀처럼 성능 개선을 위한 충분한 고민이 필요하다고 생각합니다..
질문글 올릴 때마다 피드백 주셔서 너무너무 감사드립니다!
오늘도 좋은 하루 되시고, 행복한 주말 보내시길 바라요 :)
아.. 선생님, 궁금한 점이 있는데요!
JOIN 이후에 WHERE 조건에 포함되는 테이블은 무조건적으로 INNER JOIN이 맞는 걸까요?
무조건이 아니라 쿼리 목적에 따라 다릅니다.
지금 쿼리는 무조건 OUTER JOIN 이네요.
이너 조인을 기본으로 필요시에 아우터 조인을 사용합니다.
a LEFT OUTER JOIN b 의 경우에는
조인에 성공한 b 가 없더라도 a 는 다 나오게 하는 것인데
여기에 b 를 조건을 줘서 걸러 버리면 아우터 조인 하나마나가 한 결과가 됩니다.
그렇군요...! SQL 공부 더 열심히 해야겠습니다 ㅠㅠ...
답변 너무 너무 감사드려요, 선생님 :)
) 버튼을 클릭하여 작성 하시면 됩니다.