어제 악성 쿼리를 고치면서 MS-SQL 2000,2005 페이징 기법에 적힌 기법을 모두 비교해봤다. 내가 처음에 사용했던 방법은 Top 키워드를 이용한 페이징 기법이었다. 예전엔 RowCount 키워드를 이용한 페이징 기법을 주로 썼지만, 한동안 SQL을 손 놓고 있었더니 어떻게 짜는지 금방 떠오르지 않아 생각나는대로 짰다. 결국 그렇게 안일하게 생각하다 악성 쿼리로 지목 받게 된 것이다.
쿼리 튜닝은 그때그때 상황에 맞춰야 한다. 어느 칼럼에 인덱스가 있는지, 혹 인덱스를 추가해도 문제는 없는지, 데이터의 통계 및 분포는 어떤지 등등 고려할 점이 많다. 그래서 이 세 가지 기법 중 어느 것이 최고다
라고 단정지을 수는 없지만, 적어도 문제가 된 테이블에선 세번째 기법이 가장 우수했다. MSSQL 2005에 도입된 Common Table Expression (CTE)를 이용한 방식이 적게는 0.3배에서 많게는 수백배 정도 빨랐다. 안타깝게도 해당 데이터베이스에 대한 쿼리 분석 권한이 없기 때문에 실제 실행계획은 어떤지 보고 분석하진 못했다. 데이터베이스 관리자들이 직접 분석하고 검수해주던가, 아니면 최소한의 분석 권한을 주던가 해야 할텐데, 계속 이런 식으로 해야 하는지 걱정이다. 일단은 이 방식대로 쿼리를 고쳐서 좀더 상황을 지켜봐야겠다.
부연. 쿼리를 만들어달라는 요청(또는 요구)이 가끔 있는데, 이때 반드시 고려해야 할 것이 있다. 바로 그 쿼리를 얼마나 자주 쓰는지 그 빈도부터 알아야 한다. 새벽녘에 딱 한번 도는 쿼리라면 테이블 스캔을 한들 큰 문제가 안 될 수 있다. 그에 반해 초 단위로 수행되는 쿼리라면 그 속도가 아무리 빠르더라도 문제의 소지가 있다. 이런 경우엔 메모리 데이터베이스를 도입하는 방식도 고려해볼만 하다.
3번째 방법은 CTE 안에서 ROW_NUMBER와 함께 모든 데이터를 읽은 후, 이것으로 페이징을 하는데요.
조금 바꿔서…
CTE 안에서 바로 페이징 해서 테이블의 PK만을 읽은 후, 이것으로 실제 데이터를 읽어야 할 테이블을 JOIN 하면, CTE에서 전체 데이터를 읽지 않고 페이징 개수만큼만 읽기 때문에 데이터 양이 적어 조금은 더 좋은 성능을 낼 수 있을 겁니다.(아마도요…ㅋ)
어라, 댓글 달았는데 날아갔네요.
간략히 요약해서 말씀드리면 3번째 쿼리가 아마 최적의 실행계획을 탈 겁니다. ‘아마’라고 말씀드리는 이유는 제가 CTE를 완전히 이해한 것도 아니고, 실제 서버에서 실행계획을 볼 권한이 없어서 확신 못하기 때문입니다.
일단 3번째 쿼리가 보편적으로 인정 받는 듯 하구요. 나중에 자세히 알게 되면 또 글 쓰겠습니다.
reric님도 댓글을 달아주셨네요..^^
reric님이 말씀하신 방법인 PK만으로 페이징하고, PK 리스트를 실제 테이블과 조인해서 읽는 방법은 저도 얼마전까지 유용하게 써먹었던 방법입니다.
그런데 몇달전 제 친구로부터 그런 방식이 더 느리다는 말을 듣고 테스트해 본 결과..
소용량 테이블에서는 그냥 페이징하는 방식이 훨씬 빠르더군요.. -_- 제가 10만건으로 테스트해봤을 때는 속도가 2배정도 차이났던 것으로 기억납니다.
대용량 테이블에서 페이징 기법은 생각보다 다양하게 있더군요. 쿼리 튜닝만이 아니라 사용자 UI를 제한하는 대가로 성능을 얻는 방법도 있었구요..
대용량 페이징 기법은 먹고 사는데 많은 도움을 줄 기술 중 하나라고 생각해요.. ^^
웹 애플리케이션이 힘을 받는 시대니까 더욱 그렇지요. 대부분의 경우엔 한가지 기법만 쓰면 되고, 그렇게 해도 안 되는 부분은 쿼리가 아닌 물리적인 방법을 동원해야 하는 경우가 많으니까, 개발자보다는 DBA의 역할이 더욱 중요하지요. ^^