MSSQL 게시판(Paging) 쿼리

Tutor.C 2024. 10. 8. 17:28

2024. 10. 8. 17:28

개요 : 페이징 쿼리 구현

테스트 환경 구성

sales.salesOrderdetail 테이블 사용

exec sp_helpindex 'sales.salesOrderdetail'
--pk : SalesOrderID, SalesOrderDetailID

인덱스

orders_x01 : productid, modifieddate, salesorderid,salesorderdetailid

use workload 
go
if object_id('orders') is not null
   drop table orders 
go


select *
into orders
from AdventureWorks2022.sales.salesOrderdetail
go

alter table orders add constraint orders_pk primary key nonclustered (salesorderid, salesorderdetailid)
go
create nonclustered index orders_x01 on orders(productid, modifieddate, salesorderid,salesorderdetailid)
go

--2376
select count(*)
from orders 
where  productid = 922

실제 실행계획 출력 설정

SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON

앞쪽 페이지를 주로 조회할 때

업무 : 한 주문제품에 대한 고객의 주문 목록을 10건 이내로 조회하는 쿼리

select top 11 *
from orders 
where  productid = 922 
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

[실행계획]

select top 11 *  from orders   where  productid = 922   order by ModifiedDate, SalesOrderID, SalesOrderDetailID
  |--Top(TOP EXPRESSION:((11)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1003]) WITH ORDERED PREFETCH)
            |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
            
 테이블 'orders'. 스캔 수 1, 논리적 읽기 15, 실제 읽기 3

두 번째 페이지도 출력하려면 21(=2*10+1)건을 추출하고서 맨 마지막에 11건을 잘라내면 된다.

select top 11 *
from ( select top 21 *
              ,row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum
        from orders 
	   where productid = 922   
	   order by ModifiedDate, SalesOrderID, SalesOrderDetailID
	   ) a 
where a.rnum >= 11 
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

[실행계획]

select top 11 *  from ( select top 21 *                ,row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum          from orders       where productid = 922         order by ModifiedDate, SalesOrderID, SalesOrderDetailID      ) a   where a.rnum >= 11   order by ModifiedDate, SalesOrderID, SalesOrderDetailID
  |--Top(TOP EXPRESSION:((11)))
       |--Filter(WHERE:([Expr1003]>=(11)))
            |--Top(TOP EXPRESSION:((21)))
                 |--Sequence Project(DEFINE:([Expr1003]=row_number))
                      |--Segment
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1005]) WITH ORDERED PREFETCH)
                                |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922)) ORDERED FORWARD)
                                |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
                                
테이블 'orders'. 스캔 수 1, 논리적 읽기 25, 실제 읽기 3

TOP N 쿼리와 변수를 사용하여 전체 페이지 출력에 사용할 수 있다.

declare @pageno int, @rowsbypage int

set @pageno = 2 -- 페이지 번호
set @rowsbypage = 10 -- 페이지별 rows

select top (@rowsbypage + 1) *
 from ( select top (@pageno * @rowsbypage + 1) *
               ,row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum
		 from orders with(index(orders_x01))
		 where productid = 922
		 order by ModifiedDate, SalesOrderID, SalesOrderDetailID
	) a 
where a.rnum>= (@pageno -1) * @rowsbypage + 1 
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

[실행계획]

select top (@rowsbypage + 1) *   from ( select top (@pageno * @rowsbypage + 1) *                 ,row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum     from orders with(index(orders_x01))     where productid = 922     order by ModifiedDate, SalesOrderID, SalesOrderDetailID   ) a   where a.rnum>= (@pageno -1) * @rowsbypage + 1   order by ModifiedDate, SalesOrderID, SalesOrderDetailID
  |--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@rowsbypage]+(1),0)))
       |--Filter(WHERE:([Expr1003]>=CONVERT_IMPLICIT(bigint,([@pageno]-(1))*[@rowsbypage]+(1),0)))
            |--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@pageno]*[@rowsbypage]+(1),0)))
                 |--Sequence Project(DEFINE:([Expr1003]=row_number))
                      |--Segment
                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1008]) WITH ORDERED PREFETCH)
                                |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922)) ORDERED FORWARD)
                                |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
                                
테이블 'orders'. 스캔 수 1, 논리적 읽기 25, 실제 읽기 3

앞쪽 페이지 뿐만 아니라 뒤쪽 페이지도 자주 조회한다면 아래와 같은 쿼리를 사용한다. 바로 앞 페이지에서 가져온 마지막 건을 이용해서 쿼리를 작성한다.(이 것 때문에 미리 1건을 더 가져온다.)

첫 번째 페이지 쿼리

select top 11
       row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum, *
 from Orders
where productid = 922
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

두 번째 페이지부터의 쿼리

/* 앞 페이지의 11번째 데이터 저장 변수 */
declare @modifieddate char(8), @salesorderid int

set @modifieddate = '20140616'
set @salesorderid = 74677

select top 11 
       row_number() over(order by ModifiedDate, SalesOrderID, SalesOrderDetailID) as rnum, *
 from orders 
where productid = 922
   /* 앞 페이지의 마지막 데이터보다 크거나 같아야 한다. */
and ((ModifiedDate = convert(datetime, @modifieddate, 112) and salesorderid >= @salesorderid)
     or ModifiedDate > convert(datetime, @modifieddate, 112))
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

첫 번째 페이지용 쿼리와 두 번째 이상 페이지용 쿼리를 하나로 합친 쿼리

/* 앞 페이지의 11번째 데이터 저장 변수 */
declare @modifieddate char(8), @salesorderid int

/* modifieddate 컬럼에는 날짜 값만 들어 있어서 char(8) 형식으로 처리함 */
set @modifieddate = '20140616'
set @salesorderid = 74677

select top 11 *
  from orders 
where productid = 922
   /* 앞 페이지의 마지막 데이터보다 크거나 같아야 한다. */
and ((ModifiedDate = convert(datetime, @modifieddate, 112) and salesorderid >= @salesorderid)
     or ModifiedDate > convert(datetime, @modifieddate, 112))
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

[실행계획]

select top 11 *   from orders   where productid = 922  and ((ModifiedDate = convert(datetime, @modifieddate, 112) and salesorderid >= @salesorderid)       or ModifiedDate > convert(datetime, @modifieddate, 112))  order by ModifiedDate, SalesOrderID, SalesOrderDetailID
  |--Top(TOP EXPRESSION:((11)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1006]) WITH ORDERED PREFETCH)
            |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922)),  WHERE:([workload].[dbo].[orders].[ModifiedDate]=CONVERT(datetime,[@modifieddate],112) AND [workload].[dbo].[orders].[SalesOrderID]>=[@salesorderid] OR [workload].[dbo].[orders].[ModifiedDate]>CONVERT(datetime,[@modifieddate],112)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
            
테이블 'orders'. 스캔 수 1, 논리적 읽기 24, 실제 읽기 4

INDEX SEEK 조건으로 ProductID만 사용되었음 = 인덱스 엑세스 조건으로 ProductID만 사용되어 인덱스 엑세스 범위를 줄이지 못함

인덱스를 최적으로 엑세스 하기(인덱스 엑세스 범위를 줄이기) 위해 쿼리 변경

/* 앞 페이지의 11번째 데이터 저장용 변수 */
declare @modifieddate char(8), @salesorderid int

/* modifieddate 컬럼은 날짜 값만 관리하므로 char(8) 형식으로 받아옴 */
set @modifieddate = '20140616'
set @salesorderid = 74677

select top 11 *
 from (
       select * 
	   from (
	        select top 11 * 
			from orders 
            where productid = 922
            /* 앞 페이지의 마지막 데이터와 같음 */
			 and ModifiedDate = convert(datetime, @modifieddate, 112) 
			 and salesorderid >= @salesorderid
			 order by ModifiedDate, SalesOrderID, SalesOrderDetailID
			 ) a 
	   union all 
	   select *
	    from (
	        select top 11 * 
			from orders 
            where productid = 922
            /* 앞페이지의 마지막 데이터보다 큼 */
            and ModifiedDate > convert(datetime, @modifieddate, 112)
			order by ModifiedDate, SalesOrderID, SalesOrderDetailID
			) a 
        ) a 
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go

[실행계획]

select top 11 *   from (         select *       from (           select top 11 *       from orders               where productid = 922      and ModifiedDate = convert(datetime, @modifieddate, 112)       and salesorderid >= @salesorderid      order by ModifiedDate, SalesOrderID, SalesOrderDetailID      ) a       union all       select *       from (          select top 11 *       from orders               where productid = 922                and ModifiedDate > convert(datetime, @modifieddate, 112)     order by ModifiedDate, SalesOrderID, SalesOrderDetailID     ) a           ) a   order by ModifiedDate, SalesOrderID, SalesOrderDetailID
  |--Top(TOP EXPRESSION:((11)))
       |--Merge Join(Concatenation)
            |--Top(TOP EXPRESSION:((11)))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |         |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922) AND [workload].[dbo].[orders].[ModifiedDate]=CONVERT(datetime,[@modifieddate],112) AND [workload].[dbo].[orders].[SalesOrderID] >= [@salesorderid]) ORDERED FORWARD)
            |         |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((11)))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003], [Expr1020]) WITH ORDERED PREFETCH)
                      |--Index Seek(OBJECT:([workload].[dbo].[orders].[orders_x01]), SEEK:([workload].[dbo].[orders].[ProductID]=(922) AND [workload].[dbo].[orders].[ModifiedDate] > CONVERT(datetime,[@modifieddate],112)) ORDERED FORWARD)
                      |--RID Lookup(OBJECT:([workload].[dbo].[orders]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)
                      
테이블 'orders'. 스캔 수 2, 논리적 읽기 17, 실제 읽기 4

Index Seek 오퍼레이션의 SEEK:() 부분에 모두 페이지 처리용 조건이 나타남, ModifiedDate, salesorderid가 인덱스 엑세스 조건으로 사용되어 성능이 개선되었다.

앞쪽 페이지로 거슬러 올라가는 쿼리

/* 뒤 페이지의 첫 번째 데이터 저장용 변수 */
declare @modifieddate char(8), @salesorderid int

/* modifieddate 컬럼은 날짜 값만 관리하므로 char(8) 형식으로 받아옴 */
set @modifieddate = '20140616'
set @salesorderid = 74677

select *
from (
select top 11 *
 from (
       select * 
	   from (
	        select top 11 * 
			from orders 
            where productid = 922
            /* 뒤 페이지의 첫 번째 데이터와 같음 */
			 and ModifiedDate = convert(datetime, @modifieddate, 112) 
			 and salesorderid <= @salesorderid
			 order by ModifiedDate desc, SalesOrderID desc, SalesOrderDetailID desc
			 ) a 
	   union all 
	   select *
	    from (
	        select top 11 * 
			from orders 
            where productid = 922
            /* 뒤 페이지의 첫 번째 데이터보다 작음 */
            and ModifiedDate < convert(datetime, @modifieddate, 112)
			order by ModifiedDate desc, SalesOrderID desc, SalesOrderDetailID desc
			) a 
        ) a 
order by ModifiedDate desc, SalesOrderID desc, SalesOrderDetailID desc
) a
order by ModifiedDate, SalesOrderID, SalesOrderDetailID
go
go

'MSSQL > SQL튜닝(예제)' 카테고리의 다른 글

MSSQL LEFT JOIN 집계로 반복 엑세스 줄이기 (0)	2024.11.06
MSSQL LEFT JOIN -> OUTER APPLY 적용 사례 (0)	2024.10.22
MSSQL 부분범위 처리 (0)	2024.10.08
MSSQL Outer Apply & TOP N 적용 예제 (0)	2024.10.02
MSSQL Cross Apply & TOP N 적용 예제 (0)	2024.10.02

Let's have a cup of coffee.

MSSQL 게시판(Paging) 쿼리

'MSSQL > SQL튜닝(예제)' 카테고리의 다른 글

+ Recent posts

티스토리툴바