MSSQL Outer Apply & TOP N 적용 예제

 

개요

---

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가 줄어 듦