Cross Apply 與 Inner Join 的對抗

05-13

Cross Apply 是 SQL Server 2005 新出的特性，微軟給出的定義原文是這樣：

The MSDN definition is:

"The APPLY operator is similar to the JOIN operator, but the difference is that the right-hand side operator of APPLY can reference columns from the left-hand side".

兩者最大的區別在於 apply 可以引用操作符左邊表的欄位。

我們來詳細了解下它與 inner join 的區別和聯繫。

首先兩者的目的是不一樣的。

cross apply 在保持外部表( outer table 亦稱為 left table ) 所有數據行的基礎上，對每一行數據做運算，此類運算可以是表連接聚合，兩表部分欄位合併或者建立在第二張表特定條件下的篩選（此類運算會減少數據行）等等。將 cross apply 比作是運算符的話，外部表的運算肯定是排第一位的，因此順序比 Join 嚴格。

Join 專註於兩表的鏈接，即部分欄位的合併。這一步之後才能展開做排序，聚合等計算。Inner Join 的隱含條件篩選動作，有可能會減少原表的數據行大小。此時兩表是獨立的，運算順序不需要特別明確。

離開場景談目的，總是行走於迷茫大霧之中一樣，縱然千言萬語，說盡其理，但讀者依然可能聽的是雲里霧裡。

適合 cross apply 的最佳場景：

1 在原表欄位作為表函數參數，以此結果作為第二張連接表的時候

2 在計算排名，並取第二張表 Top N 名次記錄的時候

當然在這種場景下，使用 join 也是可以解決問題的。只是演算法的複雜度變為了O(n^2). 每一行都要做一次聚合排名，每一次要對第二張表做全表（全索引）掃描和聚合。

我們舉例來說明：

假設有三張表，用戶表，訂單表，以及用戶訂單關聯表。

用戶表有10000數據，訂單表有3650000數據，而用戶訂單關聯表是將這總訂單平分給這些用戶（為了方便自動化處理）

CREATE TABLE dbo.DimUser (

UserID INT IDENTITY(1, 1)

,UserName NVARCHAR(200)

)

CREATE TABLE dbo.FctOrderHeader (

OrderID BIGINT IDENTITY(1, 1)

,OrderDate DATETIME

,OrderAmount BIGINT

)

CREATE TABLE dbo.FctOrderUser (

OrderID BIGINT

,UserID INT

)

自動化給這些表賦值，語句就不貼了。有興趣可以留言，我再貼。用到的思想是 tally table, 即生成連續數字和日期的技巧。

我們需要新建一個表函數，用來接收用戶作為參數，返回用戶對應訂單的詳細信息。

CREATE FUNCTION dbo.GetUserOrder (@UserID INT)

RETURNS TABLE

RETURN (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

AND usr.UserID = @UserID

)

最精彩之處在於，我們可以用第一張表裡的欄位作為限制條件，來將第二張表裡的數據篩選出來，並且進行計算，合成到第一張表裡，而 Inner join 的第二張表，即 right table 是獨立的，並不能接收第一張表的任何欄位：

SELECT TOP 10 usr.UserName

,ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM DimUser usr

CROSS APPLY (

SELECT TOP 3 OrderID

,OrderDate

,OrderAmount

FROM dbo.GetUserOrder(usr.UserID)

ORDER BY OrderDate DESC

,OrderAmount DESC

) ord

ORDER BY usr.UserName ASC;

Inner join 也可以實現類似的查詢，但是性能上會差很多，我測出來是 4s:12s 的區別：

WITH BASE_QUERY

AS (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

,usr.UserID

,Row_Number() OVER (

PARTITION BY usr.UserID ORDER BY ord.OrderDate DESC

,ord.OrderAmount DESC

) AS RNK

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

)

SELECT TOP 10 usr.UserName

,ord.OrderID

,ord.OrderDate

,ord.OrderAmount

FROM DimUser usr

INNER JOIN BASE_QUERY ord ON usr.UserID = ord.UserID

WHERE ord.RNK <= 3

ORDER BY usr.UserName

適合 Inner Join 的最佳場景：

1 兩表拼接的時候，主表的數據量遠遠大於副表的數據量，即預存表數據量級很大的情況下，使用 cross apply, 不能有效的利用第二張表來過濾。

而 Inner join 既能高效利用兩表的索引，通過 Join 還能同時縮小條件篩選的命中範圍。

用 cross apply 統計總數，查詢用時特別長，終其根本原因，表函數內部查詢和 Left Table （DimUser）查詢，並沒有受到 Right Table 的時間限制影響：

SELECT count(*) AS rowseffect

FROM DimUser usr

CROSS APPLY (

SELECT TOP 3 OrderID

,OrderDate

,OrderAmount

FROM dbo.GetUserOrder(usr.UserID)

WHERE OrderDate BETWEEN 2015-01-01

AND 2015-02-1

ORDER BY OrderDate DESC

,OrderAmount DESC

) ord;

用 Inner Join 就會快很多，因為 Left Table 的總數，被 Inner Table 時間限制住了

WITH BASE_QUERY

AS (

SELECT ord.OrderID

,ord.OrderDate

,ord.OrderAmount

,usr.UserID

,Row_Number() OVER (

PARTITION BY usr.UserID ORDER BY ord.OrderDate DESC

,ord.OrderAmount DESC

) AS RNK

FROM FctOrderHeader ord

INNER JOIN FctOrderUser usr ON ord.OrderID = usr.OrderID

WHERE ord.OrderDate BETWEEN 2015-01-01

AND 2015-02-1

)

SELECT count(*) AS rowseffect

FROM DimUser usr

INNER JOIN BASE_QUERY ord ON usr.UserID = ord.UserID

WHERE ord.RNK <= 3

--------------------------------

歡迎關注【有關SQL】，入群討論技術

http://weixin.qq.com/r/KTnRyWrEqTfWrS6q92w7 (二維碼自動識別)