Let's have a cup of coffee.

한 테이블을 반복 엑세스 하는 문제가 발생함

주문이 완료된 건수의 주문ID만 출력하는 쿼리에서 아래의 2가지 사항을 제외함

• 상품원장의 상태가 취소인 건수
• 발주신청이 안되어 조인이 안되는 건수

[튜닝 전]

SELECT A.ORDERID, A.COUNT 
  FROM
 (SELECT ORDERID, COUNT (ID) AS COUNT 
    FROM ORDERDETAIL
   WHERE PLACE = 'SEOUL'
     AND ORDERID IN (1,2,3)
   GROUP BY ORDERID) A
 INNER JOIN 
 (SELECT A.ORDERID, COUNT(A.ORDERID) AS COUNCT
    FROM PRODUCTASK B
   INNER JOIN ORDERDETAIL A ON B.ORDERDETAILID = A.ORDERDETAILID
   INNER JOIN PRODUCTLEDGER C ON C.PRODUCTID = B.PRODUCTID
   WHERE A.PLACE = 'SEOUL'
     AND C.STATE != 'CANCEL'
     AND A.ORDERID IN (1,2,3)
   GROUP BY A.ORDERID) A2
    ON A.ORDERID = A2.ORDERID
 WHERE A.COUNT > 0
   AND A.COUNT = A2.COUNT
;

[튜닝 후]

• ROW_NUMBER를 사용하여 STATE가 NULL인 집합을 첫번째로 나오게 한다(RN=1) 그 후에 A.STATE != 'CANCEL'에서 NULL과 비교하면 거짓이므로 'CANCEL'과 NULL은 제외됨

SELECT A.ORDERID, CNT --CNT는 확인용
FROM (
SELECT A.*, ROW_NUMBER() OVER(PARTITION BY A.ORDERID ORDER BY A.STATE ) RN 
FROM (
SELECT A.ORDERID, C.STATE, COUNT(*) CNT 
 FROM ORDERDETAIL A
 LEFT JOIN  PRODUCTASK B 
   ON B.ORDERDETAILID = A.ORDERDETAILID
 LEFT JOIN PRODUCTLEDGER C 
   ON C.PRODUCTID = B.PRODUCTID
WHERE 1=1
  AND A.PLACE = 'SEOUL'
  AND A.ORDERID IN (1,2,3)
GROUP BY A.ORDERID, C.STATE 
) A 
) A 
WHERE RN = 1
  AND A.STATE != 'CANCEL' --NULL제거됨 (조인안된 건수 제거)
;

악성 SQL 추출하기 

1. 기준 페이지수 10,000번이상 읽는 쿼리 

2. 실행횟수 * 페이지수 = 총 읽은 페이지수를 기준으로 Descending하게 정렬하여 추출

--악성 sql추출
SELECT TOP 100 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) SQL_TEXT,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
  AND qs.max_logical_reads > 10000  
  AND qs.max_logical_reads > 10000  
ORDER BY qs.total_logical_reads DESC
;

1. 개요

OUTER APPLY를 적용할 사례를 찾을 수 있을까?

2. 튜닝 전 쿼리 

• LEFT JOIN 으로 연결된 블럭이 실제로 10개가 넘는다. 최대한 고치지 않고 튜닝을 해보자.
• ORACLE이라면 조인조건을 뷰안으로 집어넣어 주는 JPPD(Join Predicate Pushdown)가 동작하겠지만 MSSQL은 그렇지 못하다.

SELECT A.*, B.COL1, B2.COL1, B3.COL1
  FROM A
 LEFT JOIN (
            SELECT B.COL1, B.COL2
              FROM B
              INNER JOIN C ON C.COL1 = B.COL1
              INNER JOIN D ON D.COL1 = C.COL1 AND D.COL2 = 'CODE1'
            ) B ON B.COL1 = A.COL1
 LEFT JOIN (
            SELECT B2.COL1, B2.COL2
              FROM B2
              INNER JOIN C2 ON C2.COL1 = B2.COL1
              INNER JOIN D2 ON D2.COL1 = C2.COL1 AND D2.COL2 = 'CODE2'
            ) B2 ON B.COL1 = A.COL1

 LEFT JOIN (
            SELECT B3.COL1, B3.COL2
              FROM B3
              INNER JOIN C3 ON C3.COL1 = B3.COL1
              INNER JOIN D3 ON D3.COL1 = C3.COL1 AND D3.COL2 = 'CODE3'
            ) B3 ON B3.COL1 = A.COL1            
.....

3.튜닝 후 쿼리

• OUTER APPLY 를 사용하여 조인조건을 안으로 밀어 넣어 주어서 실행계획이 변경되었다.

SELECT A.*, B.COL1, B2.COL1, B3.COL1
  FROM A
 OUTER APPLY(
            SELECT B.COL1, B.COL2
              FROM B
              INNER JOIN C ON C.COL1 = B.COL1
              INNER JOIN D ON D.COL1 = C.COL1 AND D.COL2 = 'CODE1'
              WHERE B.COL1 = A.COL1
             ) B 
 OUTER APPLY(
            SELECT B2.COL1, B2.COL2
              FROM B2
              INNER JOIN C2 ON C2.COL1 = B2.COL1
              INNER JOIN D2 ON D2.COL1 = C2.COL1 AND D2.COL2 = 'CODE2'
             WHERE B.COL1 = A.COL1
             ) B2
 OUTER APPLY(
            SELECT B3.COL1, B3.COL2
              FROM B3
              INNER JOIN C3 ON C3.COL1 = B3.COL1
              INNER JOIN D3 ON D3.COL1 = C3.COL1 AND D3.COL2 = 'CODE3'
             WHERE B3.COL1 = A.COL1            
            ) B3
.....

개요 : 페이징 쿼리 구현 

테스트 환경 구성 

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

개요

customer 테이블에서 컬럼1(storeid or personid) , 컬럼2(customerid)로 정렬 후에 10건의 데이터 리스트를 출력한다. 

pk(clustered) : customerid

customers_x01 : personid, storeid

사전작업

1) 테이블&인덱스 생성

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

select top 10000
       identity(int, 1, 1) as customerid, personid , storeid, territoryid, accountnumber, rowguid, modifieddate 
into customers	   
from AdventureWorks2022.sales.customer
go

alter table customers add constraint customers_pk
primary key clustered(customerid)
go
create index custoemers_x01 on customers(personid, storeid)
go

2) 실제 실행계획 출력

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

튜닝전 :  I/O = 82, 실행계획은 PK 인덱스 풀 스캔

storeid, customerid로 이루어진 인덱스가 없으므로 pk컬럼(=clustered index)를 SCAN 하는 방식으로 실행

select  top 10 *
from customers 
order by storeid, customerid 
go

실행계획

select  top 10 *  from customers   order by storeid, customerid
  |--Sort(TOP 10, ORDER BY:([workload].[dbo].[customers].[storeid] ASC, [workload].[dbo].[customers].[customerid] ASC))
       |--Clustered Index Scan(OBJECT:([workload].[dbo].[customers].[customers_pk]))

메시지

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 6ms.

(10개 행이 영향을 받음)
테이블 'customers'. 스캔 수 1, 논리적 읽기 81, 실제 읽기 1, 페이지 서버 읽기 0, 미리 읽기 읽기 79, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

(3개 행이 영향을 받음)

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 6ms

튜닝 후 :  페이지 I/O = 48
customers_x01(personid, storeid) 인덱스는 클러스터형 인덱스의 키 컬럼인 customersid를 rowid대신에 가지고 있으므로 customers_x01 인덱스만 엑세스하여 storeid, customerid 순으로 정렬이 가능하다.

인라인뷰를 활용하여 10건을 먼저 읽고 customer 테이블과 NL조인하여 성능을 향상 시킨다. 조인은 한 번 늘었지만, 페이지 I/O와 수행시간은 줄어 든다.

select top 10 b.*
from (
    select  top 10 personid, storeid
    from customers 
    order by storeid, customerid 
    ) a , customers b 
where a.customerid = b.customerid
 order by a.storeid, a.customerid 
 option (force order, loop join)
 go

실행계획

select top 10 b.*  from (  select  top 10 storeid, customerid  from customers   order by storeid, customerid   ) a , customers b   where a.customerid = b.customerid   order by a.storeid, a.customerid    option (force order, loop join)
  |--Top(TOP EXPRESSION:((10)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([workload].[dbo].[customers].[customerid]))
            |--Sort(TOP 10, ORDER BY:([workload].[dbo].[customers].[storeid] ASC, [workload].[dbo].[customers].[customerid] ASC))
            |    |--Index Scan(OBJECT:([workload].[dbo].[customers].[custoemers_x01]))
            |--Clustered Index Seek(OBJECT:([workload].[dbo].[customers].[customers_pk] AS [b]), SEEK:([b].[customerid]=[workload].[dbo].[customers].[customerid]) ORDERED FORWARD)

메시지

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 4ms.

(10개 행이 영향을 받음)
테이블 'customers'. 스캔 수 1, 논리적 읽기 45, 실제 읽기 3, 페이지 서버 읽기 0, 미리 읽기 읽기 23, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

(6개 행이 영향을 받음)

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 3ms

출처 : SQL Server 튜닝 원리와 해법

개요

1) 적용사례 : 조인이 A -> B 순서로 적용될때 Top 10 정렬(Order by) 기준이 B 테이블 일 때

                  즉, Outer 테이블이 아닌 Inner 테이블의 정렬 기준으로 Top 10 조회할 때

2) 실제 실행 계획 수집 

SET SHOWPLAN_TEXT OFF 

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

3) AdventureWorks2022 사용

use AdventureWorks2022

4) Index 생성

create index IX_SalesOrderDetail_PID_CTN_MDate on sales.SalesOrderDetail(ProductID, CarrierTrackingNumber, ModifiedDate);

--drop index sales.SalesOrderDetail.IX_SalesOrderDetail_PID_CTN_MDate ;

튜닝 전 : 

SELECT *
FROM (
SELECT p.ProductID, p.Name ,s.CarrierTrackingNumber, s.ModifiedDate
	  ,ROW_NUMBER() OVER(ORDER BY s.ModifiedDate desc) rn
  FROM production.product p
 LEFT JOIN sales.SalesOrderDetail s
   ON p.ProductID = s.ProductID 
WHERE p.ProductID = 782
  AND s.CarrierTrackingNumber is null
) a
WHERE a.rn <=10
-- OPTION(LOOP JOIN) --NL조인 시 I/O=35로 증가

메시지 :

I/O = 27, Time = 28ms

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 23ms.

(30개 행이 영향을 받음)
테이블 'Worktable'. 스캔 수 0, 논리적 읽기 0, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.
테이블 'SalesOrderDetail'. 스캔 수 1, 논리적 읽기 21, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.
테이블 'Product'. 스캔 수 3, 논리적 읽기 6, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

(9개 행이 영향을 받음)

 SQL Server 실행 시간: 
 CPU 시간 = 15ms, 경과 시간 = 5ms

완료 시간: 2024-10-02T17:35:05.8986283+09:00

실행계획 :

• 조인 후 정렬 연산 수행 후 TOP N 쿼리 실행

SELECT *  FROM (  SELECT p.ProductID, p.Name ,s.CarrierTrackingNumber, s.ModifiedDate     ,ROW_NUMBER() OVER(PARTITION BY s.ProductID ORDER BY s.ModifiedDate desc) rn    FROM production.product p   LEFT JOIN sales.SalesOrderDetail s     ON p.ProductID = s.ProductID   WHERE p.ProductID IN (782,783,784)    AND s.CarrierTrackingNumber is null  ) a  WHERE a.rn <=10
  |--Filter(WHERE:([Expr1002]<=(10)))
       |--Sequence Project(DEFINE:([Expr1002]=row_number))
            |--Segment
                 |--Sort(ORDER BY:([s].[ProductID] ASC, [s].[ModifiedDate] DESC))
                      |--Filter(WHERE:([AdventureWorks2022].[Sales].[SalesOrderDetail].[CarrierTrackingNumber] as [s].[CarrierTrackingNumber] IS NULL))
                           |--Merge Join(Left Outer Join, MERGE:([p].[ProductID])=([s].[ProductID]), RESIDUAL:([AdventureWorks2022].[Production].[Product].[ProductID] as [p].[ProductID]=[AdventureWorks2022].[Sales].[SalesOrderDetail].[ProductID] as [s].[ProductID]))
                                |--Clustered Index Seek(OBJECT:([AdventureWorks2022].[Production].[Product].[PK_Product_ProductID] AS [p]), SEEK:([p].[ProductID]=(782) OR [p].[ProductID]=(783) OR [p].[ProductID]=(784)) ORDERED FORWARD)
                                |--Index Seek(OBJECT:([AdventureWorks2022].[Sales].[SalesOrderDetail].[IX_SalesOrderDetail_PID_CTN_MDate] AS [s]), SEEK:([s].[ProductID] >= (782) AND [s].[ProductID] <= (784)) ORDERED FORWARD)

튜닝 후 :

SELECT p.ProductID, p.Name
      ,s.*
  FROM production.product p
 OUTER APPLY (SELECT TOP 10 s.CarrierTrackingNumber, s.ModifiedDate
                FROM sales.SalesOrderDetail s
               WHERE p.ProductID = s.ProductID
			     AND s.CarrierTrackingNumber is null
			   ORDER BY s.ModifiedDate desc) s
WHERE p.ProductID IN (782,783,784)

메시지 : 

I/O = 15, Time = 31ms

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 31ms.

(30개 행이 영향을 받음)
테이블 'SalesOrderDetail'. 스캔 수 3, 논리적 읽기 9, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.
테이블 'Product'. 스캔 수 3, 논리적 읽기 6, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

(6개 행이 영향을 받음)

 SQL Server 실행 시간: 
 CPU 시간 = 0ms, 경과 시간 = 0ms

완료 시간: 2024-10-02T17:36:13.4382709+09:00

실행계획 :

• 조인을 하면서 TOP N 쿼리 실행

SELECT p.ProductID, p.Name        ,s.*    FROM production.product p   OUTER APPLY (SELECT TOP 10 s.CarrierTrackingNumber, s.ModifiedDate                  FROM sales.SalesOrderDetail s                 WHERE p.ProductID = s.ProductID          AND s.CarrierTrackingNumber is null        ORDER BY s.ModifiedDate desc) s  WHERE p.ProductID IN (782,783,784)
  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[ProductID]))
       |--Clustered Index Seek(OBJECT:([AdventureWorks2022].[Production].[Product].[PK_Product_ProductID] AS [p]), SEEK:([p].[ProductID]=(782) OR [p].[ProductID]=(783) OR [p].[ProductID]=(784)) ORDERED FORWARD)
       |--Top(TOP EXPRESSION:((10)))
            |--Compute Scalar(DEFINE:([Expr1002]=[AdventureWorks2022].[Sales].[SalesOrderDetail].[CarrierTrackingNumber] as [s].[CarrierTrackingNumber], [Expr1003]=[AdventureWorks2022].[Sales].[SalesOrderDetail].[ModifiedDate] as [s].[ModifiedDate]))
                 |--Index Seek(OBJECT:([AdventureWorks2022].[Sales].[SalesOrderDetail].[IX_SalesOrderDetail_PID_CTN_MDate] AS [s]), SEEK:([s].[ProductID]=[AdventureWorks2022].[Production].[Product].[ProductID] as [p].[ProductID] AND [s].[CarrierTrackingNumber]=NULL) ORDERED BACKWARD)

결과 : I/O 횟수가 12회 줄어듦

튜닝 전 : Outer Table에서 Inner Table로 조인 후에 Inner Table의 컬럼을 기준으로 정렬하고 Top N알고리즘을 수행

튜닝 후 : Outer Apply 조인을 사용하여 Outer Table에서 Inner Table로 조인 하면서 Inner Table에서 정렬하여 Top N 알고리즘을 적용하여 I/O가 줄어 듦