프로시저 속도 문제.. 0 10 4,565

by COMAVLER [SQLServer] 프로시저 속도 유니온 [2023.04.04 15:06:55]


SELECT A.CH_TYPE_NM
	 , A.ITEM_CD
	 , IO_DATE = dbo.DTRIM(A.IO_DATE, '-')
	 , CH_DATE
	 , B.ITEM_NM
	 , B.ITEM_SPEC
	 , B.UNIT
FROM
(
	SELECT DIV_CD
		 , CH_TYPE
		 , CH_TYPE_NM = '등록' 
		 , ITEM_CD
		 , IO_DATE
		 , CH_DATE
	FROM
	(
		SELECT TOP 1 *
		FROM BZ990T_HIST
		WHERE (@iIO_NO = '' OR IO_NO = @iIO_NO)
		  AND (@iIO_SEQ IS NULL OR IO_SEQ = @iIO_SEQ)
		  AND (@iDIV_CD = '' OR DIV_CD = @iDIV_CD)
		  AND ((@iIO_DATE_FROM = '' OR IO_DATE >= @iIO_DATE_FROM) AND (@iIO_DATE_TO = '' OR IO_DATE <= @iIO_DATE_TO))
		ORDER BY CH_DATE ASC
	) AS A WHERE CH_TYPE = 'A'
	
	UNION ALL
	
	SELECT DIV_CD
		 , CH_TYPE
		 , CH_TYPE_NM = '수정' 
		 , ITEM_CD
		 , IO_DATE
		 , CH_DATE
	FROM
	(
		SELECT *
		FROM BZ990T_HIST A
		WHERE (@iIO_NO = '' OR IO_NO = @iIO_NO)
		  AND (@iIO_SEQ IS NULL OR IO_SEQ = @iIO_SEQ)
		  AND (@iDIV_CD = '' OR DIV_CD = @iDIV_CD)
		  AND ((@iIO_DATE_FROM = '' OR IO_DATE >= @iIO_DATE_FROM) AND (@iIO_DATE_TO = '' OR IO_DATE <= @iIO_DATE_TO))
		  AND CH_SEQ <> (SELECT TOP 1 CH_SEQ FROM BZ990T_HIST B WHERE B.IO_NO = A.IO_NO AND B.IO_SEQ = A.IO_SEQ AND B.CH_TYPE = 'A' ORDER BY B.CH_DATE ASC)
	) AS A WHERE CH_TYPE = 'A'
	
	UNION ALL
	
	SELECT DIV_CD
		 , CH_TYPE
		 , CH_TYPE_NM = '삭제' 
		 , ITEM_CD
		 , IO_DATE
		 , CH_DATE
	FROM
	(
		SELECT TOP 1 *
		FROM BZ990T_HIST
		WHERE (@iIO_NO = '' OR IO_NO = @iIO_NO)
		  AND (@iIO_SEQ IS NULL OR IO_SEQ = @iIO_SEQ)
		  AND (@iDIV_CD = '' OR DIV_CD = @iDIV_CD)
		  AND ((@iIO_DATE_FROM = '' OR IO_DATE >= @iIO_DATE_FROM) AND (@iIO_DATE_TO = '' OR IO_DATE <= @iIO_DATE_TO))
		ORDER BY CH_DATE DESC
	) AS A WHERE CH_TYPE = 'D'
) AS A 
	JOIN BC200T B ON A.ITEM_CD = B.ITEM_CD
ORDER BY A.CH_DATE

안녕하세요. SQL을 사용하여 개발을 시작한지 1년이 안된 개발자입니다.
해당 프로시저의 목적은 히스토리를 조회하는 단순한 역할입니다. 특정 행의 데이터가 등록/수정/삭제 되었는지 판단 해야하는데 수정하는 프로세스가 단순 수정이 아니라 '삭제->등록'과 같이 이루어지다 보니 히스토리 상에서 해당 내용을 판단하려다 보니 이렇게 내용이 길어지게 되었네요....

현재 히스토리에 약 40만 건의 데이터가 있고 조회 조건을 통해서 한 번에 약 10 - 20개 정도의 데이터를 조회하게 됩니다. 시간이 약 5-10초 정도 소요되어서 시간을 줄일 수 있는 방법이 없을까 하고 질문하게 되었습니다.

조언 부탁드립니다. 감사합니다.

by 마농 [2023.04.04 15:38:25]

등록/수정/삭제를 판별하는 기준이 이상합니다.
왜 이렇게 하는 걸까요?


by COMAVLER [2023.04.04 15:42:02]

현재 히스토리 테이블에 등록/삭제를 판단하는 기준은 존재하지만 수정은 따로 없고, '삭제 후 재등록'과 같은 프로세스로 진행됩니다.
그래서 해당 IO_NO, IO_SEQ로 등록된 ROW 중에 가장 첫번째 등록이 진짜 등록, 그 이후에 생긴 등록은 '수정' 이라고 판별하게 되었습니다.

구조가 이상하지만 우선은 이 상황 안에서 해결해야 하기에 방법을 찾고 있는 중입니다. 


by 마농 [2023.04.04 15:47:09]

그건 입력 조건이 입력되엇을 때의 얘기인 것 같습니다.
지금 쿼리는 조건이 있을 때와 없을 때를 아우르는 쿼리네요?
조건이 없을 때는 어찌 판별하나요? 똑같이 판별하면 안될 것 같은데요?

조건이 없을 때의 조건도 상이하네요?
= '' 조건과 IS NULL 조건이 각각 다르게 쓰이네요?
이 조건들이 쓰임새가 있는 조건인지?
조건이 입력되지 않는 경우가 있는것인지?


by 마농 [2023.04.04 15:58:22]

원본테이블에 데이터 조건 걸어 조회시 데이터는 몇건이 나오나요?
간단한 예시 자료 보여주실 수 있는지요?
원본 대비 결과표.


by COMAVLER [2023.04.04 16:01:30]

네 맞습니다. 조건이 있을 때와 없을 때 모두 아우르는데 생각해보니 클라이언트에서 해당 조건이 입력되지 않을 경우는 없을 것 같네요.
추가로 IS NULL을 사용한 것은 @iIO_SEQ 값이 numeric이라서 이렇게 넣었는데 여기도 NULL일 경우는 없을 것 같아요.

혹시 조건이 없는 경우는 없다고 가정하고 해당 쿼리를 수정했을 때 추가로 개선해야 할 부분이 있을까요?


by COMAVLER [2023.04.04 16:06:44]

현재 원본테이블에는 총 32만건의 데이터가 있고 조건을 걸었을 때에는 약 11건 정도의 데이터가 나옵니다.
예를들어,
SELECT * FROM HISTORY
WHERE IO_NO = 'IO124124124' AND IO_SEQ = 11 이렇게요.

제가 잘 답변했는지 모르겠네요...ㅎㅎㅎ


by 마농 [2023.04.04 18:02:52]
SELECT div_cd
     , ch_type
     , CASE WHEN ch_type = 'D' THEN '삭제'
            WHEN ch_type = 'A' AND rn = 1 THEN '등록'
            WHEN ch_type = 'A' AND rn > 1 THEN '수정'
        END ch_type_nm
     , item_cd
     , io_date
     , ch_date
  FROM (SELECT div_cd
             , ch_type
             , item_cd
             , io_date
             , ch_date
             , ROW_NUMBER() OVER(PARTITION BY div_cd, io_no, io_seq, ch_type ORDER BY ch_date) rn
          FROM bz990t_hist
         WHERE io_no    = @iio_no
           AND io_seq   = @iio_seq
           AND div_cd   = @idiv_cd
           AND io_date >= @iio_date_from
           AND io_date <= @iio_date_to
           AND ch_type IN ('A', 'D')
        ) a
;

 


by COMAVLER [2023.04.05 09:12:29]

제 쿼리를 보시고 해당 조건으로 파티션을 나누어서 ROW_NUMBER를 매겨야겠다고 생각하신 것은 혹시 어떤 근거로 그렇게 판단하신 것일까요? 공부를 하면 당연히 사용할 수 있는 정석적인 방법인가요?


by 마농 [2023.04.05 09:42:15]

1. 올려주신 쿼리는 의도와 다르게 잘못 작성된 쿼리라고 생각했습니다.
2. 쿼리 및 댓글을 통해 작성 의도를 파악하려고 했습니다.
3. 작성 의도를 파악하여 그에 맞는 쿼리를 작성했습니다. (정확하게 파악했는지는 미지수)


by COMAVLER [2023.04.05 10:05:26]

제 의도와는 맞았습니다. 감사합니다.
다만 이렇게 판단하게 되신 근거가 궁금했습니다. 감사합니다 마농님

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입