이전 글에서 'Row_Number'를 이용하여 검색된 결과에 순서대로 번호를 부여한 후 이 번호를 기반으로 'bettwen'으로 필요한 범위를 잘라내는 방법을 설명하였습니다.

동적코드 없이도 코드가 깔끔하지만 속도가 너무 안 났고 그 원인을 분석해보니 셀랙트 부분에 있는 'Case'문이 문제였습니다.

(참고 : [MSSQL] MSSQL의 페이징 쿼리 고찰 - 1. Row_Number + bettwen)

 

이 문제를 해결 할 수 있도록 튜닝 해봅시다.

 

*글을 읽기전에 이전글( [MSSQL] MSSQL의 페이징 쿼리 고찰 - 0. 테스트 환경 만들기(클릭) )을 보고 오시는 것이 좋습니다.*

 

 

1. 사용 방법

사용 방법은 다음과 같습니다.

 

Select Row_Number() Over ( Order By [넘버링 기준 컬럼] Asc ) AS [Row_Number()로 만든 순서 컬럼명], [출력할 컬럼]
From [테이블]
Where [조건] Order by [Case 문]

 

'[Case 문]'은 'Order by'절에서 'case' 사용하기와 동일 합니다.

(참고 : [MSSQL] 'Order by'절에서 'case'문 사용하기)

 

2. 페이징 코드

이제 튜닝된 코드를 확인해 봅시다.

 

2-1. 전체 코드

 

CREATE PROCEDURE [dbo].[spPageing_RowNumber_bettwen_Tuning]
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 Row_Number() Over ( Order By nIndex Asc ) 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 + '%'))
			Order by case When  @nSort <> 1 And @nSort <> 2
							Then nIndex end Desc
					, case when @nSort = 1
							Then (Case @nOrderColumn
									When 1 Then Data1
									When 2 Then Data2
									When 3 Then Data3
									When 4 Then Data4
									Else nIndex
									End) end Asc
					, case when @nSort = 2
							Then (Case @nOrderColumn
									When 1 Then Data1
									When 2 Then Data2
									When 3 Then Data3
									When 4 Then Data4
									Else nIndex
									End) end Desc


	--완성된 리스트를 가지고 필요한 정보를 추출한다.

	--글수 구하기
	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
			Order By rowNum Asc;

	 
    SET @dataEnd = CURRENT_TIMESTAMP
    SELECT DATEDIFF(ms, @dataStart, @dataEnd)

END

 

코드 라인끝

 

2-1. 코드 분석

코드 분석은 테스트하기 전 왜 이런 코드가 되었는지를 설명하기 위한 것입니다.

결과에서 테스트를 통해 예상과 다르게 동작 할 수 있으므로 주의가 필요합니다.

 

일단 Selet에 있던 'case'를 'Where'절로 옮겼습니다.

그리고 여러개의 'case'를 이용하여 정렬을 하고 있습니다.

'Order by'절에서 사용한 'case'문의 자세한 설명은 '[MSSQL] 'Order by'절에서 'case'문 사용하기'에서 했으므로 넘어가겠습니다.

(참고 : [MSSQL] 'Order by'절에서 'case'문 사용하기)

 

조건에

"case When  @nSort <> 1 And @nSort <> 2 Then nIndex end Desc"

가 있는 이유는 자주 쓰는 조건을 빨리 명중 시키기 위해서 디폴트가 되는 조건을 미리 정의 한것입니다.

코드를 더 간결하게 만들고 싶으시면 없어도 됩니다.

 

3. 결과

속도 (ms)

  1회 2회 3회 4회 5회
30만 523 446 596 540 503
100만 8556 8676 8393 8843 8990

 

이전 코드보다 속도가 많이 증가 하였습니다.

하지만 100만은 여전히 시간이 꽤 걸리네요 ㅡ,.ㅡ;

 

 

 

4. 결론

주관적인 점수(10점 만점) 

 가독성

 8

 일반적으로 가독성이 높으면 중복코드가 적다.

 속도

 5

 속도가빠르면 상대적으로 튜닝 가능성이 낮아 진다.

 중복 코드

 7

 중복이 없을 수록 높은 점수

 튜닝 가능성

 1

 튜닝을 통해 성능개선이 얼마나 가능한지를 나타냄.(5%당 1점)

 

 

가독성이 좋은 코드를 살짝 수정하여 성능을 개선한 코드라 가독성은 여전히 좋은 편입니다.

문제는 코드가 중복 된다는 것인데...어쩔 수 없죠 ㅎㅎㅎ

동적쿼리를 쓰지 않은 것 치고는 중복코드가 없는 편입니다.

속도도 나쁘지 않습니다.

문제는 검색개수가 많을수록 느려진다는 것인데....대부분의 페이징쿼리가 이런 문제가 있죠.

그래서 보통 일정개수만큼 잘라서 페이징하고 나머지는 페이지가 넘어가면 처리하는 방식도 많이 사용합니다.

(예> 100만 개까지 'top'으로 자른 후 검색하고 100만 개 이후는 앞에 100만 개를 빼고 검색하는 방식)

 

저번 글에서도 이야기했던 'case'문 문제가 여기서도 발생합니다.

그래서 저런 코드가 되는 것이죠.

여러 개의 'case'문을 연결해도 속도 저하가 없다면 코드가 훨씬 깔끔해 질 텐데 그게 아쉽습니다.

 

100만 개 검색에서는 속도가 늦은 편이기 때문에 너무 크지 않은 곳에서 쓸만 할 듯합니다.

 

 



 

 

  1. 초보자 2015.10.13 01:25 신고  Address  Edit/Delete  Reply

    공부하다가 설명해 놓으신 글을 보게 됐습니다. 초보인 저한테는 굉장한 도움이 됐습니다. 감사합니다^^
    ROW_NUMBER() 사용해서 번호를 매기면 1,2,3,4 이렇게 오름차순으로 번호가 매겨지는데요.
    가령 10개의 레코드가 있다면 내림차순으로 번호를 매길수도 있는지 궁금합니다.

    10, 9, 8 , 7, 6, 5 이런식으로요.
    검색을 해 보았는데 이렇게 되는 방법은 없어 보이는데요. 만약 이렇게 할 수 있는 방법이 있을까요?

  2. 초보자 2015.10.14 20:48 신고  Address  Edit/Delete  Reply

    네 해봤는데요^^
    정렬 순서는 역순으로 바뀌는데 넘버링은 무조건 1 부터 시작을 해서요.
    넘버링 자체를 역순으로 할 방법이 있는지 궁금해서요

    • Favicon of http://blog.danggun.net BlogIcon 당근천국 2015.10.14 21:24 신고  Address  Edit/Delete

      'ROW_NUMBER()'의 출력자체가 역순으로 나오지 않습니다.
      'ROW_NUMBER()'로 넘버링된걸 'order by'로 정렬 하셔야합니다.
      'ROW_NUMBER()'자체는 넘버링을 위한 정렬이라 순차로 나오기 때문에 이걸 염두하시고 sql을 작성하셔야 합니다.

  3. 초보자 2015.10.16 00:55 신고  Address  Edit/Delete  Reply

    아 답변 감사합니다. 넘버링된걸 다시 order by 하면 데이터 양이 많아지면 많이 느려지겠네요...
    알겠습니다~

    • Favicon of http://blog.danggun.net BlogIcon 당근천국 2015.10.18 01:39 신고  Address  Edit/Delete

      그렇긴 한데 다른 요인들에 비하면 단순 넘버링 반전시키는 오더바이는 신경쓸만한 비용이 발생하지 않습니다.
      물론 없는것보단 많이 듭니다 ㅎㅎㅎ

댓글 작성

이름
패스워드
홈페이지
비밀글

티스토리 툴바