데이터베이스 질문과 답변
안녕하세요.
해당 쿼리에서 서브커리를 쓰면서 현저하게 속도가 떨어져서요.
이 서브쿼리를 조인으로 대체하면 리스트의 키값과 비교가 제대로 되지 않아 데이터가 틀려지고해서 어떻게 구성해야할가요.
SELECT SPON.SPONID
, (SELECT TOP 1 DEPODT FROM SPONAMTDEPO WHERE SPONID = SPON.SPONID ORDER BY DEPODT DESC) AS DEPODT
, (SELECT TOP 1 CD_NM FROM CODE WHERE CLS_CD = '0013' AND CD_NO = (SELECT TOP 1 FINAN_ORGAN FROM SPONAMTDEPO WHERE SPONID = SPON.SPONID ORDER BY DEPODT DESC)) AS FINAN_ORGAN
, (SELECT TOP 1 RECEAMT FROM SPONAMTDEPO WHERE SPONID = SPON.SPONID ORDER BY DEPODT DESC) AS RECEAMT
, (SELECT COUNT(1) FROM SPONAMTDEPO DEPO WHERE DEPO.SPONID = SPON.SPONID ) AS AMTDEPO_CNT
FROM SPON SPON
LEFT OUTER JOIN SOCG SOCG ON SPON.SOCGID = SOCG.SOCGID
LEFT OUTER JOIN PRSN PRSN ON SPON.PRSNID = PRSN.PRSNID
LEFT OUTER JOIN TEAM TEAM ON SPON.TEAMID = TEAM.TEAMID
LEFT OUTER JOIN CODE CODE ON CODE.CLS_CD = '0193'
AND CODE.CD_NO = SPON.RECECLSCD
각 서브쿼리 조건 컬럼에 인덱스가 있는지 확인하세요.
SELECT spon.sponid , ... , depo.depodt , depo.finan_organ , depo.receamt , depo.amtdepo_cnt , ... FROM spon spon LEFT OUTER JOIN socg socg ON socg.socgid = spon.socgid LEFT OUTER JOIN prsn prsn ON prsn.prsnid = spon.prsnid LEFT OUTER JOIN team team ON team.teamid = spon.teamid LEFT OUTER JOIN code code ON code.cd_no = spon.receclscd AND code.cls_cd = '0193' LEFT OUTER JOIN (-- 중복 조회되는 sponamtdepo 를 우선 조회해 Group By 합니다. SELECT depo.sponid , depo.depodt , code.cd_nm finan_organ , depo.receamt , a.amtdepo_cnt FROM (SELECT sponid , MAX(depodt) depodt , COUNT(*) amtdepo_cnt FROM sponamtdepo GROUP BY sponid ) a INNER JOIN sponamtdepo depo ON a.sponid = depo.sponid AND a.depodt = depo.depodt LEFT OUTER JOIN code ON code.cd_no = depo.finan_organ AND code.cls_cd = '0013' ) depo ON spon.sponid = depo.sponid ;
) 버튼을 클릭하여 작성 하시면 됩니다.