[MSSQL 2005] MSSQL의 페이징 쿼리 고찰 - 1. Row_Number + bettwen
'Row_Number'를 이용하려면 'MSSQL 2005'이상에서 사용 가능한 방법입니다.
'Row_Number'를 이용하여 검색된 결과에 순서대로 번호를 부여한 후 이 번호를 기반으로 'bettwen'으로 필요한 범위를 잘라내는 방법입니다.
동적 쿼리를 전혀 사용하지 않아도 중복되는 코드 없이 깔끔하게 코드를 만들 수 있다는 장점이 있습니다.
*글을 읽기전에 이전글( [MSSQL] MSSQL의 페이징 쿼리 고찰 - 0. 테스트 환경 만들기(클릭) )을 보고 오시는 것이 좋습니다.*
1. 사용 방법
사용 방법은 다음과 같습니다.
Select ( Row_Number() Over ( Order By [정렬 기준 컬럼] [정렬 방향] )) as [Row_Number()로 만든 순서 컬럼명], [출력할 컬럼...]
From [테이블]
Where [조건] AND [Row_Number()로 만든 순서 컬럼명] between [범위의 시작] And [범위의 끝]( 참고 : MSDN - ROW_NUMBER(Transact-SQL), BETWEEN(Transact-SQL) )
사용 방법만 보면 복잡합니다.
그래도 동적쿼리 없이 페이징할 수 있다는 것을 알 수 있습니다.
2. 페이징 코드 만들기
하지만 페이징 코드를 만들면 간결하면서도 깔끔한 코드가 가능합니다.
2-1. 전체 코드
CREATE PROCEDURE [dbo].[spPageing_RowNumber_bettwen]
AS
BEGIN
DECLARE @dataStart DATETIME, @dataEnd DATETIME
SET @dataStart = CURRENT_TIMESTAMP
Declare @nLanguage_Index int = 0
Declare @nPageNum int = 1
Declare @nOrderColumn int = 0
Declare @nSort int = 2
Declare @nSearchIndex int = 1
Declare @sSearch nvarchar(128) = ''
--한페이지에 보여질 글갯수
Declare @nPageSize int
--총 페이지 갯수
Declare @nTotalPage int
--총 글 갯수
Declare @nTotalCount int
--출력용 임시 테이블
Declare @tableData Table
( RowNum int
, nIndex int
, Data1 nvarchar(128)
, Data2 nvarchar(128)
, Data3 nvarchar(128)
, Data4 int);
--페이지 정보 저장용
Declare @tableInfo Table
( TotalCount bigint
, TotalPage int);
--한페이지에 몇개의 글을 출력할지 설정값을 받아온다.
Set @nPageSize = 10;
--조건에 맞게 테이블을 만든다.
--생성된 데이터를 빽업합니다.
Insert into @tableData Select (Case
When @nOrderColumn = 1 And @nSort = 1
Then Row_Number() Over ( Order By Data1 Asc )
When @nOrderColumn = 1 And @nSort = 2
Then Row_Number() Over ( Order By Data1 Desc )
When @nOrderColumn = 2 And @nSort = 1
Then Row_Number() Over ( Order By Data2 Asc )
When @nOrderColumn = 2 And @nSort = 2
Then Row_Number() Over ( Order By Data2 Desc )
When @nOrderColumn = 3 And @nSort = 1
Then Row_Number() Over ( Order By Data3 Asc )
When @nOrderColumn = 3 And @nSort = 2
Then Row_Number() Over ( Order By Data3 Desc )
When @nOrderColumn = 4 And @nSort = 1
Then Row_Number() Over ( Order By Data4 Asc )
When @nOrderColumn = 4 And @nSort = 2
Then Row_Number() Over ( Order By Data4 Desc )
Else Row_Number() Over ( Order By nIndex Desc )
End)
AS rowNum
, nIndex
, Data1
, Data2
, Data3
, Data4
From tb_BigData1
Where (('' = @sSearch)
Or ((Case @nSearchIndex
When 1 Then Data1
When 2 Then Data2
When 3 Then Data3
When 4 Then CONVERT(nvarchar(128), Data4)
Else Data1
End) Like + '%' + @sSearch + '%'));
--완성된 리스트를 가지고 필요한 정보를 추출한다.
--글수 구하기
Set @nTotalCount = (Select count(*)
From @tableData);
--페이지수 구하기
Set @nTotalPage = (@nTotalCount / @nPageSize) + 1;
--글수와 페이지수 완성
Insert into @tableInfo( TotalCount
, TotalPage)
Values ( @nTotalCount
,@nTotalPage);
--완성된 리스트를 가지고 필요한 정보를 추출한다.
--글수 구하기
Set @nTotalCount = (Select count(*)
From @tableData)
--페이지수 구하기
if( 0 < @nTotalCount )
Begin
Set @nTotalPage = (@nTotalCount / @nPageSize) + 1;
End
else
Begin
Set @nTotalPage = 0;
End
--글수와 페이지수 완성
Insert into @tableInfo( TotalCount
, TotalPage)
Values ( @nTotalCount
,@nTotalPage);
--완성된 정보 리턴
--페이지 정보
Select *
From @tableInfo as PageInfo;
--글목록
Select *
From @tableData as PageData
Where rowNum between ((@nPageNum - 1) * @nPageSize) + 1
And @nPageNum * @nPageSize;
SET @dataEnd = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @dataStart, @dataEnd)
END
2-2. 코드 분석
코드 분석은 테스트하기 전 왜 이런 코드가 되었는지를 설명하기 위한 것입니다.
결과에서 테스트를 통해 예상과 다르게 동작할 수 있으므로 주의가 필요합니다.
1) 일단 검색된 게시물 숫자를 계산하기 위해 테이블 변수에 검색된 데이터를 넣습니다.
이렇게 하면 속도가 떨어지는데....
실행 계획에는 비용이 10%로 되어 있지만 실제로 돌려보면 큰 차이가 없습니다.
아마도 화면에 표시하기 위한 자원과 큰 차이가 없어서 그런듯합니다.(결과를 출력하지 않는다면 큰 차이가 있을 것 같습니다.)
만약 인서트를 빼고 동작시키려면 검색을 2번 하던가 데이터에 쓰레기를 붙이는 방법뿐이 없을 듯합니다.
2) 검색할 땐 'Case'
MSSQL에서 'Case'는 c언어에서 사용하는 스위치(switch)문과 비슷한 역할을 합니다.
(참고 : MSDN - CASE(Transact-SQL))
'where'절에 'csae'를 사용하면 동적 쿼리 없이도 원하는 조건을 선택하여 비교할 수 있습니다.
문제는 이것이 매번 조건을 검색한다고 합니다.
('row' 개수만큼 'case'를 비교한다는 얘기. 하지만 'order by'에 사용한다면?, 오!더!바!이!)
그러므로 'case'를 회피하기 위해 'or'을 사용하여 검색어가 없을 때는 비교하지 않도록 합니다.
일반적인 언어들은 효율성을 위해 비교문을 사용할 때 뒤 내용과 상관없이 결과가 나오면 뒤 내용을 무시하도록 되어 있습니다.
(그런데 MSSQL도 그렇게 돼 있는지는 모르겠네요-_-a)
3) 결과 정렬은 'Row_Number()'를 이용하여
결과를 'Row_Number()'를 이용하여 순번을 부여하고 이 순번을 이용하여 재정렬하면 코드상으로는 깔끔한 코드가 가능합니다.
('where'절이 복잡해지는 걸 어느 정도 방지할 수 있죠.)
이것을 'case'와 같이 사용하면 동적 쿼리와 같은 효과를 정적 쿼리로 느낄 수 있습니다.
이것을 이용하면 원하는 컬럼을 기준으로 원하는 방향으로 정렬하기가 쉽습니다.
이렇게 정렬된 데이터의 순서를 기준으로 순번이 부여되기 때문에 활용하기도 좋다는 장점이 있습니다.
4) 'between'을 이용한 범위 자르기
'between'을 이용하면 쉽게 범위를 자를 수 있습니다.
보통은 날짜를 이용하여 범위를 자를 때 사용합니다.
그런데 게시물에 'between'을 사용하면 문제가.......10개의 게시물의 범위가 일정치 않다는 것입니다.
만약
1, 4, 5, 7, 10, 13, 35, 677, 685, 755, 800......
이런 식으로 인덱스가 되어 있는 결과물에서 상위 5개의 데이터를 보고 싶으면 범위가 1~10이 되어야 합니다.
정렬이나 여러 가지 문제에 의해서 순서가 뒤죽박죽인데 거기서 특정 범위를 구하는 일이 많은 페이징 코드에는 어울리지 않는 문법이라는 것이죠.
하지만 이런 단점을 커버할 수 있는 것이 'Row_Number()'와 같이 사용할 때입니다.
위에서도 설명했지만 'Row_Number()'는 결과에 순번을 부여해줍니다.
이렇게 부여된 순번은 무조건 순서대로 있는 것이기 때문에 'between'을 이용할 수 있습니다.
3. 결과
속도 (ms)
| 1회 | 2회 | 3회 | 4회 | 5회 | |
| 30만 | 35310 | 35350 | 30643 | 39076 | 32353 |
| 100만 | 103250 | 91870 | 93387 | 116250 | 99753 |
속도가 처참합니다 ㅡ,.ㅡ;;;;
밀이세컨드(ms)이니 검색된 게시물 1개당 0.1초씩 걸린 셈입니다.
4. 결론
주관적인 점수(10점 만점)
| 가독성 | 10 | 일반적으로 가독성이 높으면 중복코드가 적다. |
| 속도 | 1 | 속도가빠르면 상대적으로 튜닝 가능성이 낮아 진다. |
| 중복 코드 | 9 | 중복이 없을 수록 높은 점수 |
| 튜닝 가능성 | 2 | 튜닝을 통해 성능개선이 얼마나 가능한지를 나타냄.(5%당 1점) |
코드가 깔끔하고 중복코드가 거의 없어 가독성이 높아 유지보수나 재활용하기는 좋은데 속도가 문제입니다-_-;;;;
검색된 결과의 숫자에 비례해서 속도가 느려지기 때문에 전체 글 검색이 디폴트인 게시판의 특성상 게시물의 숫자가 늘어나면 지옥이 펼쳐집니다.
"DBA가 붙어서 작업할 예정이니 일단 결과만 나오게 하자"
라는 생각으로 쓰면 좋은 코드입니다.
유지보수나 재활용하기 좋다는 의미는 개발하는 동안 자주 바뀌게 되는 스펙에 대응하기 쉽다는 의미도 되기 때문에
"개발이 완료 되고 나서 신경 쓰겠다"
라는 생각으로 쓴다면 이보다 좋은 코드는 없다고 생각됩니다.
'동적 쿼리는 죄악이다!!!'
라는 강박에 쌓여있다면 좋은 코드입니다만......
동적 쿼리를 자제해야 하는 건 맞는데 속도만 빠르다면야 연장을 가릴 처지가 아니라는 게 모든 사람의 문제죠 ㅎㅎㅎㅎ
실행계획을 확인해 보면 소트가 여러 번 발생하는 것을 알 수 있습니다.
그냥 봐서는 'Row_Number()'가 문제인 것 같은데 몇 가지 테스트를 해보니 'case'의 효율 문제인 것 같습니다.
'select'에 있는 'case' 개수가 속도에 영향을 주고 있습니다-_-;;;;;;;;;;;;;;;;
그런데 상식적으로 이해가 되지 않는 것이 일반적인 언어에서 'case'문은 명중이 되면 더는 체크를 하지 않습니다.
그러니 초반의 조건이 맞으면 성능 차가 조건의 개수와 상관이 없어야 하는데 MSSQL에서는 상관이 있는듯합니다.
그것도 심하게 말이죠.
이 스크린샷은 조건은 2개만 남긴 후 첫 번째 조건이 명중하도록 설정하여 돌린 것입니다.
여전히 느리긴 하지만 이전의 평균속도보다 훨씬 빨라졌습니다.
어쩌면 조건과 상관없이 일단 다 정렬하는 것일지도 모르겠습니다.
모두 정렬해두고 맞는 조건을 출력해주는 것이죠.
만약 이런 방식으로 동작하고 있는 것이라면 'select'에서 'case'문을 이용하여 정렬하는 건 미친 짓이라고 할 수 있습니다.
마무리
위 테스트 결과만 놓고 보자면 튜닝 시 'case'문을 'where'절로 보내는 게 더 좋아 보입니다.
결국 이상은 높았으나 결과물이 개판인 셈이죠 ㅜㅡ
다음 글에선 이것을 튜닝하여 성능을 올려 보겠습니다.