WITH edm_tree_cte AS ( SELECT tid FROM edm_tree WHERE tid = 'T1' OR tid = 'K0' OR tid = 'C0' UNION ALL SELECT c.tid FROM edm_tree_cte p INNER JOIN edm_tree c ON p.tid = c.pid ) SELECT TOP 1000 f.ext + d.lock_status + d.c_status AS ext, d.doc_name, d.commts, d.maintain_id, d.doc_type, d.insert_date, d.company_id, d.update_date, d.file_id, d.grade_id, d.sys_rev_no, d.doc_seq, d.i_user_id, d.status, d.size_id, d.lock_status,d.rev_no, d.tree_id, d.dockind_id, d.doc_no, d.com_doc_no, d.create_date, d.u_user_id, d.project_name, d.rev_srev, d.com_code, d.mr_code, d.mr_file_id, d.manage_team, d.pdf_file_id, d.master_doc_no, d.[left], d.bottom, d.[right], d.[top], d.ecn_no, d.type1, d.type2, d.type3, d.type4, d.type5, d.c_status, '' as hasextcol, '0' as mr_count, case when d.lock_status = 'T' then ISNULL(dbo.GET_CHECK_LIST(d.doc_seq),dbo.GET_CHECKOUT_COMMT(d.doc_seq)) else '' end checkout_info, d.cp_no FROM edm_doc d, edm_file f WHERE d.rev_no = (select max(rev_no) from edm_doc where doc_no = d.doc_no and status = 'E') and F.F_CATEGORY = 'EDM_DOC' and f.f_category_id = d.doc_seq AND STATUS = 'E' AND d.tree_id IN (SELECT tid FROM edm_tree_cte) AND D.DOCKIND_ID IN (SELECT tid FROM edm_tree_cte) ORDER BY tree_id, doc_no, rev_no
다음과 같은 쿼리가있는데요.
여기서 조건문의
AND d.tree_id IN (SELECT tid FROM edm_tree_cte)
AND D.DOCKIND_ID IN (SELECT tid FROM edm_tree_cte)
때문에 CTE 테이블을 생성했는데 이부분때문에 쿼리가 많이 느려지네요.
CTE 테이블과
AND d.tree_id IN (SELECT tid FROM edm_tree_cte)
AND D.DOCKIND_ID IN (SELECT tid FROM edm_tree_cte)
이 조건문을 제외하고
SELECT TOP 1000 f.ext + d.lock_status + d.c_status AS ext, d.doc_name, d.commts, d.maintain_id, d.doc_type, d.insert_date, d.company_id, d.update_date, d.file_id, d.grade_id, d.sys_rev_no, d.doc_seq, d.i_user_id, d.status, d.size_id, d.lock_status,d.rev_no, d.tree_id, d.dockind_id, d.doc_no, d.com_doc_no, d.create_date, d.u_user_id, d.project_name, d.rev_srev, d.com_code, d.mr_code, d.mr_file_id, d.manage_team, d.pdf_file_id, d.master_doc_no, d.[left], d.bottom, d.[right], d.[top], d.ecn_no, d.type1, d.type2, d.type3, d.type4, d.type5, d.c_status, '' as hasextcol, '0' as mr_count, case when d.lock_status = 'T' then ISNULL(dbo.GET_CHECK_LIST(d.doc_seq),dbo.GET_CHECKOUT_COMMT(d.doc_seq)) else '' end checkout_info, d.cp_no FROM edm_doc d, edm_file f WHERE d.rev_no = (select max(rev_no) from edm_doc where doc_no = d.doc_no and status = 'E') and F.F_CATEGORY = 'EDM_DOC' and f.f_category_id = d.doc_seq AND STATUS = 'E'
이것만 조회 시키면 꽤 빠르구요..
해당하는 쿼리를 어덯게 튜닝하는게 좋을까요?
ps. 토드 오라클에선 튜닝하는 툴이 있었는데 MS-SQL에선 동일한 기능을 해주는 툴은 없을까요?