MSSQL Cross 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 생성

--drop index sales.SalesOrderDetail.IX_SalesOrderDetail_ProductID_UnitPrice ;

create index IX_SalesOrderDetail_ProductID_UnitPrice on sales.SalesOrderDetail(ProductID, UnitPrice);

---

튜닝 전 : 

SELECT ProductID, Name, UnitPrice
FROM 
(
	SELECT p.ProductID, p.Name, s.UnitPrice
		  ,ROW_NUMBER() OVER(PARTITION BY s.ProductID ORDER BY s.UnitPrice DESC) rn 
	 FROM production.product p
	INNER JOIN sales.SalesOrderDetail s
	   ON p.ProductID = s.ProductID 
    WHERE p.ProductID >= 990
) a
WHERE a.rn <= 1
--OPTION(LOOP JOIN)
GO

---

메시지 :

I/O = 46, Time = 5ms

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

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

(7개 행이 영향을 받음)

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

완료 시간: 2024-10-02T13:19:38.1999627+09:00

 

---

실행계획 :

SELECT ProductID, Name, UnitPrice  FROM   (   SELECT p.ProductID, p.Name, s.UnitPrice      ,ROW_NUMBER() OVER(PARTITION BY s.ProductID ORDER BY s.UnitPrice DESC) rn     FROM production.product p   INNER JOIN sales.SalesOrderDetail s      ON p.ProductID = s.ProductID       WHERE p.ProductID >= 990  ) a  WHERE a.rn <= 1
  |--Filter(WHERE:([Expr1002]<=(1)))
       |--Sequence Project(DEFINE:([Expr1002]=row_number))
            |--Segment
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[ProductID]))
                      |--Clustered Index Seek(OBJECT:([AdventureWorks2022].[Production].[Product].[PK_Product_ProductID] AS [p]), SEEK:([p].[ProductID] >= (990)) ORDERED FORWARD)
                      |--Index Seek(OBJECT:([AdventureWorks2022].[Sales].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID_UnitPrice] AS [s]), SEEK:([s].[ProductID]=[AdventureWorks2022].[Production].[Product].[ProductID] as [p].[ProductID]),  WHERE:([AdventureWorks2022].[Sales].[SalesOrderDetail].[ProductID] as [s].[ProductID]>=(990)) ORDERED BACKWARD)

 

 

 

---

튜닝 후 :

SELECT p.ProductID, p.Name, s.UnitPrice
 FROM production.product p
CROSS APPLY (SELECT TOP 1 s.ProductID, s.UnitPrice 
               FROM sales.SalesOrderDetail s 
	      WHERE p.ProductID = s.ProductID			    
	      ORDER BY s.ProductID, s.UnitPrice DESC) s
WHERE p.ProductID >= 990
GO

 

---

메시지 : 

I/O = 34, Time = 14ms

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

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

(5개 행이 영향을 받음)

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

완료 시간: 2024-10-02T13:19:53.5720701+09:00

---

실행계획 :

SELECT p.ProductID, p.Name, s.UnitPrice   FROM production.product p  CROSS APPLY (SELECT TOP 1 s.ProductID, s.UnitPrice                  FROM sales.SalesOrderDetail s              WHERE p.ProductID = s.ProductID              ORDER BY s.ProductID, s.UnitPrice DESC) s  WHERE p.ProductID >= 990
  |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[ProductID]))
       |--Clustered Index Seek(OBJECT:([AdventureWorks2022].[Production].[Product].[PK_Product_ProductID] AS [p]), SEEK:([p].[ProductID] >= (990)) ORDERED FORWARD)
       |--Top(TOP EXPRESSION:((1)))
            |--Index Seek(OBJECT:([AdventureWorks2022].[Sales].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID_UnitPrice] AS [s]), SEEK:([s].[ProductID]=[AdventureWorks2022].[Production].[Product].[ProductID] as [p].[ProductID]) ORDERED BACKWARD)

 

---

 

결과 : I/O 횟수가 12회 준것으로 보아 극단적인 성능향상은 없었으나. INNER JOIN에서 OUTER 테이블이 아닌 INNER 테이블의 컬럼으로 정렬하여 TOP N 쿼리 출력 시 조인 후에 정렬을 시행한다.

CORSS APPLY or OUTER APPLY는 이처럼 Inner 테이블이 정렬의 기준이 되어 row_number의 성능이 느릴때 사용할 수 있는 방법으로 조인하면서 TOP N 알고리즘을 수행한다. 

단, 선행되어야 할점은 정렬의 기준이 되는 인덱스가 먼저 있어야 한다. 또한, 선행테이블의 건수가 많을 시 조인에 사용되는 worktable의 용량이 커지는 것에 주의해서 사용해야 한다.

참고로, 오라클에서는 Rownum을 활용하여 정렬 기준 아닌 테이블을 서브쿼리로 변환하여(Exists문으로 변경) 실행 시 TOP N 쿼리가 적용되나 MSSQL에서는 적용되지 않는다.