안녕하세요
간단한 업데이트 쿼리 속도 문제로 오전내내 씨름하다 결국 도움을 요청하러 왔네요
MSSQL 2008 R2 Express 버전이구요 쿼리는 아래와 같습니다
쿼리.1
update CA set CA.checkmember = CB.MemberNum
From CTI_WaveRecord_State CA
Inner Join
(
Select a.sno, b.MemberNum
From (
Select sno, callnum
From CTI_WaveRecord_State
Where wavfilepath = '20130902' and callpath <> 'Callback' and checkmember is null
) A
Inner Join
(
Select MemberNum, Name, Replace(HP,'-','') HP From TBG_Client
) B ON A.callnum = B.HP
) CB ON CA.sno = CB.sno
쿼리.2
update CA set CA.checkmember = CB.MemberNum
From CTI_WaveRecord_State CA
Inner Join
(
Select a.sno, b.MemberNum
From (
Select sno, callnum
From CTI_WaveRecord_State
Where wavfilepath = '20131014' and callpath <> 'Callback' and checkmember is null
) A
Inner Join
(
Select MemberNum, Name, Replace(HP,'-','') HP From TBG_Client
) B ON A.callnum = B.HP
) CB ON CA.sno = CB.sno
보셔서 아시겠지만 간단한 업데이트 쿼리이구요 둘다 똑같은 쿼리에 음영으로 표시해놓은 조건만 다릅니다
대상 건수는 약 700-800건 정도구요
희안하게 쿼리.1은 1초 내외로 업데이트 처리가 완료됩니다
그런데 쿼리.2번은 빨라야 5분이고 방금 전에는 7분이 넘게 걸리더군요..
왜이렇게 차이가 날까요..
update대신 select로 조회해보면 둘다 1~2초 이내로 조회가 됩니다
업데이트시에만 속도가 너무 느려지네요
두 쿼리의 실제실행계획을 파일로 저장했는데 첨부할께요
확장자 제한때문에 압축했습니다
두 쿼리의 실행계획이 좀 다른데요
첨부파일 내용 보시면 TBG_Client 테이블 스캔 실제 행 수가 쿼리1은 12만 행인 반면 쿼리2는 9천만 행에 가깝네요..
이 때문인거 같은데 동일한 쿼리가 왜 이렇게 차이가 나는건지 모르겠습니다
그리고 현재 사용중인 DB가 SQL2000에서 백업한 데이터를 복원한 상태입니다
호환성은 SQL2000으로 설정했구요
2000버전 쓸때는 조회시간이 얼마 안걸리던 쿼리들이 2008로 옮긴 후로는 빨라진 쿼리도 있는 반면 현저히 느려진 쿼리도 있네요
그래서 해당 쿼리를 확인해서 사용된 테이블에 인덱스를 추가해주면 속도가 빨라지네요..
2000에서는 인덱스 없이도 빠르던 쿼리들이 2008로 옮기고 나서는 왜이렇게 느려지는건지..
혹시 이 현상에 대한 특별한 원인이 있을까요
답변에 필요한 충분한 정보가 되었는지 모르겠습니다만 조언 좀 부탁드릴께요
감사합니다
Comment 8
-
맨즈밤
2013.10.15 15:52
-
로지남푠
2013.10.15 17:03
답변 감사합니다
제 수준에 비해 어려운 답변 내용이지만 열심히 찾아보고 활용 해볼께요^^
일단 힌트 추가해보니 정말 빨라지는군요
리인덱스랑 통계업데이트 관련 내용들을 찾아보는 중입니다
좀 더 확인해보니 CTI_WaveRecord_State 테이블 조회 결과가 684행인데 여기다 TBG_Client를 풀스캔해서 하나하나 다 대입했나보네요..
동일 쿼리에 조건 하나만 바뀐건데 왜 이런식으로 실행을 한걸까요..
이부분이 납득이 안되네요ㅎㅎ
내공이 바닥임을 뼈저리게 느낍니다..ㅎㅎ
-
맨즈밤
2013.10.15 17:42
제가 글제주가 없다보니 어렵게 느껴지셨나 봅니다..
SQL이 실행계획을 세울때 통계라든지, 인덱스의 상태라든지 ,유니크 속성이라든지 그런 여러 변수를 종합하여 세웁니다. 그런 요소들이 충실하지 못하면 , 엉뚱한 실행계획이 세워질 가능성이 많아집니다. 운좋으면 빠르고 운나쁘면 느린 쿼리가 나올수도 있는거지요.
반대로 그런 요소들이 충실하다면 아무리 쿼리가 복잡하고 행여 개발자가 잘못짰더라도 왠만하면 좋은 성능의 실행계획이
나오게 됩니다. 그러기 때문에 DBA에게는 통계업데이트 라든지 인덱스 관리에 대한 관심이 필요합니다.
-
로지남푠
2013.10.15 18:17
아뇨 제가 워낙 아는게 없어서 그런겁니다ㅎㅎ
덕분에 오늘 중요한 내용을 배워가네요
SQL을 업그레이드 하면서 마이그레이션 작업중인데 이전까진 인덱스는 신경을 별로 안썼거든요
이번 기회에 인덱스의 중요성을 깊이 깨닿고 있는 중이랍니다ㅎㅎ
답변 감사드립니다^^
-
minsouk
2013.10.17 13:59
문제는 심플한데서 시작되었습니다.
CTI_WaveRecord_State 테이블에 wavefilepath 컬럼의 통계 업데이트 시점을 확인해 보세요, 아마 9월 2일은 데이터가 12건 있다나 표시되어 있을건데요, 10월 14일 데이터는 0건 일겁니다. 이 날짜 이후 통계가 업데이트 되지 않았기 때문입니다. 12건이 있다고 생각한 테이블은 HP 컬럼의 전화번호를 "-" -> "" 으로 변경 시켰으니 인덱스가 있더라도 사용하지 못하니 122098 행을 12번 하면 1465176 행이 출력되니 NL 조인을 감히 선택하지 못합니다.
그런데, 통계가 0건 있다고 나오는 10월 14일 데이터는 만약 HASH Join 으로 일일이 Function 연산을 해서 버킷을 찾고 리니어 서치를 해서 hash join 을 하는것 보다 그냥 단순하게 fullscan 만 한번 하면 되니 NL 조인을 선택 하는게 저 상황에서는 더 빠릅니다. 그래서, SQL Server 는 주어진 통계 환경에서 가장 멋진 실행계획을 구성했습니다.
그런데, 막상 들어가보니 CTI-WaveRecord_State 에는 0건 (1건) 이 684건이 있었고, 이걸 Clustered Index Scan 으로 전체를 읽으니 122098행 * 684번 = (83515032 건)의 행이 튀어나오게 되죠 이것을 이제 NL Join 으로 매치 하는지 확인해야 합니다. 하나씩 매치 하다보니 느려졌습니다.
Work-Around
1) 통계를 못 읽게 드라이빙의 컬럼도 변경 시켜라 그럼 평균 density 로 구해서 항상 Hash Join 할것이다.
2) 통계를 업데이트 시켜서 10월 4일도 여러건 있는걸로 알려줘라
3) 힌트로 니가 봐야할 날짜는 9월 2일껄로 알고 실행계획을 짜게 하거라 (옵티마이저 포)
4) 근본적인 수술을 해서 해당 핸드폰에 "-" 가 들어가지 않게 하거나, Computed Column 을 TBG_Client 의 HP 를 고치고 인덱스를 만들어라
등등이 있겠네요, 여기서, inline view 로 감싼것은 성능의 변화와 전혀 상관 없습니다.
그리고 이 부분은 옵티마이저가 좋아지고 나빠전것과 전혀 상관없는 문제일듯 합니다.
2000에서도 동일하게 풀릴듯 합니다.
플랜을 읽을줄 알면 성능 분석이 조금은 쉬워집니다. 꾸준한 연습이 필요 합니다.
그럼 수고하세요~
-
건우아빠
2013.10.17 14:14
부럽기만하다.
-
minsouk
2013.10.17 14:15
에구......요런건 초보 DBA 들 다 아는거에용~
프로그래머에겐..........이걸 넘어야 하는데...잘 안되죠.....그래서 밥 먹고 사는 사람들 많아요~ ㅋㅋㅋ
-
로지남푠
2013.10.18 15:30
아.. 어제 댓글 확인을 못했는데 이런 소중한 댓글이 달렸었네요..
정말 감사합니다
알려주신대로 해볼께요^^
틈틈히 기초부터 하나하나 다져놔야겠네요
잡다하게 여러가지 일을 하다보니 깊이가 없어놔서..ㅠㅠ
즐거운 주말 보내시길 바래요~
CTI_WAVERECORD_STATE 와 TBG_CLIENT 조인에서 INNER HASH JOIN 으로 힌트주시면 아마 빨라질듯합니다.
느려진 실행계획은 CTI_WAVERECORD_STATE 와 TBG_CLIENT 와의 조인이 LOOP 조인으로 풀리면서 TBG_CLIENT 의 풀스캔을
684번이나 해버렸기 때문에 느려진듯 보입니다.
관련 테이블들의 인덱스 리빌드 및 통계업데이트를 다 해주시고, 힌트 빼고 테스트해보세요.
또한 인라인뷰로 너무 감싼듯이 보이는데 이것도 해소하시면 성능에 도움이 될겁니다.