쿼리 튜닝 ( MS-SQL Query tuning)
(원래 Cursor를 사용 하던 쿼리였다.)
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN -- 트랜잭션 시작
DECLARE @RegUser CHAR(8)
SET @RegUser='20070073'
DECLARE @iCnt INT -- Loop 변수
, @iTotalCnt INT -- 전체 데이터 수
, @sPrevColGrp VARCHAR(30)
SELECT @iCnt=0, @iTotalCnt=0, @sPrevColGrp=''
-- 임시 테이블 선언
DECLARE @tbTmp TABLE (
Idx INT IDENTITY(1,1)
,ColGrp INT
,Col01 VARCHAR(30)
,Col02 VARCHAR(30)
,Col03 VARCHAR(30)
,RegDate DATETIME DEFAULT(GETDATE())
)
DECLARE @tbTmp2 TABLE (
ColGrp INT
,ColGrpSeq INT
,RegDate DATETIME DEFAULT(GETDATE())
)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('1', 5477)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('2', 8901)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('3', 9901)
INSERT INTO @tbTmp (ColGrp, Col01, Col02, Col03)
SELECT *
FROM (
SELECT '1' AS ColGrp, 'Col01_1' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '2' AS ColGrp, 'Col01_2' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '2' AS ColGrp, 'Col01_3' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '3' AS ColGrp, 'Col01_4' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '3' AS ColGrp, 'Col01_5' AS Col01, '' AS Col02, '' AS Col03
) A
SELECT @iTotalCnt=COUNT(*) FROM @tbTmp -- 선택된 항목의 COUNT
WHILE (@iTotalCnt>@iCnt) -- Loop 시작
BEGIN
SET @iCnt = @iCnt+1 -- Loop count 증가
PRINT ('@iTotalCnt is ' + CAST(@iTotalCnt AS VARCHAR) + ', @iCnt is ' + CAST(@iCnt AS VARCHAR))
IF (((SELECT ColGrp FROM @tbTmp WHERE Idx=@iCnt) <> @sPrevColGrp) OR (@sPrevColGrp = ''))
BEGIN
SELECT @sPrevColGrp = ColGrp FROM @tbTmp WHERE Idx=@iCnt
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq)
SELECT A.ColGrp, (MAX(B.ColGrpSeq)+1) AS ColGrpSeq
FROM @tbTmp A
JOIN @tbTmp2 B
ON A.ColGrp = B.ColGrp
WHERE A.Idx =@iCnt
GROUP BY A.ColGrp
UPDATE @tbTmp
SET Col02 = A.Col01 + '_' + CAST(ISNULL( (SELECT MAX(ColGrpSeq) FROM @tbTmp2 WHERE ColGrp = A.ColGrp) , 1) AS VARCHAR)
FROM @tbTmp A
WHERE A.Idx>=@iCnt
END
UPDATE @tbTmp
SET Col03 = (
A.Col01 + '_' + Col02 +'_' + CAST(Idx AS VARCHAR)
)
FROM @tbTmp A
WHERE A.Idx=@iCnt
END
SELECT * FROM @tbTmp
SELECT * FROM @tbTmp2
ROLLBACK TRAN
원본 쿼리 실행 결과
원본 쿼리 통계
이 쿼리에서 Loop를 제거하고 아래와 같이 만들었다.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN
DECLARE @RegUser CHAR(8)
SET @RegUser='20070073'
DECLARE @iCnt INT -- Loop 변수
, @iTotalCnt INT -- 전체 데이터 수
, @sPrevColGrp VARCHAR(30)
SELECT @iCnt=0, @iTotalCnt=0, @sPrevColGrp=''
-- 임시 테이블 선언
DECLARE @tbTmp TABLE (
Idx INT IDENTITY(1,1)
,ColGrp INT
,Col01 VARCHAR(30)
,Col02 VARCHAR(30)
,Col03 VARCHAR(30)
,RegDate DATETIME DEFAULT(GETDATE())
)
DECLARE @tbTmp2 TABLE (
ColGrp INT
,ColGrpSeq INT
,RegDate DATETIME DEFAULT(GETDATE())
)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('1', 5477)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('2', 8901)
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq) VALUES ('3', 9901)
INSERT INTO @tbTmp (ColGrp, Col01, Col02, Col03)
SELECT *
FROM (
SELECT '1' AS ColGrp, 'Col01_1' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '2' AS ColGrp, 'Col01_2' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '2' AS ColGrp, 'Col01_3' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '3' AS ColGrp, 'Col01_4' AS Col01, '' AS Col02, '' AS Col03
UNION
SELECT '3' AS ColGrp, 'Col01_5' AS Col01, '' AS Col02, '' AS Col03
) A
SELECT @iTotalCnt=COUNT(*) FROM @tbTmp -- 선택된 항목의 COUNT
INSERT INTO @tbTmp2 (ColGrp, ColGrpSeq)
SELECT A.ColGrp, (MAX(B.ColGrpSeq)+1) AS ColGrpSeq
FROM @tbTmp A
JOIN @tbTmp2 B
ON A.ColGrp = B.ColGrp
GROUP BY A.ColGrp
UPDATE @tbTmp
SET Col02 = A.Col01 + '_' + CAST(ISNULL( (SELECT MAX(ColGrpSeq) FROM @tbTmp2 WHERE ColGrp = A.ColGrp) , 1) AS VARCHAR)
FROM @tbTmp A
WHERE A.Idx>=@iCnt
UPDATE @tbTmp
SET Col03 = (
A.Col01 + '_' + Col02 +'_' + CAST(Idx AS VARCHAR)
)
FROM @tbTmp A
SELECT * FROM @tbTmp
SELECT * FROM @tbTmp2
ROLLBACK TRAN
수정된 쿼리 실행 결과
수정된 쿼리 통계
딱 봐도 속도가 많이 빨라졌다...ㅋ
'Dev & Mng' 카테고리의 다른 글
Openfiler (0) | 2009.03.28 |
---|---|
쿼리 튜닝 ( MS-SQL Query tuning) (0) | 2009.02.23 |
jQuery Intellisense in Visual Studio 2008 (0) | 2009.02.16 |
Windows 7에서 Visual Studio 2005 Service Pack 1이 설치가 안된다. (1) | 2009.02.13 |