資料庫第九周翻譯

清單3：簡單的交叉連線示例當我執行清單3中的程式碼時，我得到了報告2中的輸出
Report 2：執行清單3時的結果正如您可以看到的，通過檢視清單3中的程式碼，我生成了許多行，其中包含類似於我的Producttable中的資料的資料。通過使用ROW_Number函式，我能夠在每一行上生成唯一的ID列。此外，我還使用SalesItem表中的ID列來建立唯一的ProductName和成本列值。生成的行數等於Producttable中的行數乘以SalesItem表中的行數。到目前為止，本節中的示例只對兩個表執行了交叉連線。可以使用交叉連線運算子跨多個表執行交叉連線操作。清單4中的示例在三個表中建立了一個笛卡爾產品。
清單4：使用交叉連線操作符建立三個表的笛卡爾積執行清單4的輸出有兩個不同的交叉_連線操作。從此程式碼建立的笛卡爾產品將產生一個結果集，其總行數將等於sys.table中的行數乘以sys.objects中的行數乘以sys.sysuserts中的行數。當交叉連線像內部連線一樣執行時在上一節中，我提到當您使用交叉連線運算子時，它將生成一個笛卡爾乘積。這不是一直都是這樣的。當您使用限制交叉連線操作中涉及的表的聯接的WHERE子句時，SQLServer不會建立笛卡爾產品。相反，它的功能類似於普通的聯接操作。要演示這種行為，請檢視清單5中的程式碼。
清單5：兩個等效的SELECT語句。清單5中的程式碼包含兩個SELECT語句。第一個SELECT語句使用交叉連線操作符，然後使用WHERE子句定義如何連線交叉連線操作中涉及的兩個表。第二個SELECT語句使用帶ON子句的普通內部聯接運算子連線兩個表。SQLServer的查詢優化器非常聰明，可以知道清單5中的第一個SELECT語句可以重寫為內部聯接。當交叉連線操作與WHERE子句一起使用時，優化器知道可以重寫查詢。WHERE子句在交叉連線所涉及的兩個表之間提供連線謂詞。因此，SQLServer引擎為清單5中的兩個SELECT語句生成相同的執行計劃。當您不提供WHERE約束時，SQLServer不知道如何連線涉及交叉連線操作的兩個表，因此它在與交叉連線操作關聯的兩個集合之間建立一個笛卡兒積。使用交叉連線查詢未售出的產品前面幾節中的示例幫助您理解交叉連線操作符以及如何使用它。使用交叉連線操作符的一個功能是使用它幫助在一個表中查詢另一個表中沒有匹配記錄的項。例如，假設我想報告每個ProductName的總數量和總銷售額，在ProductName的每個日期，我的任何一個產品專案被出售。由於在我的示例中，每個ProductName並不是每天都有銷售，所以我的報告要求意味著對於那些在某一天沒有售出的產品，我需要顯示0的數量和0美元的總銷售額。在這裡，交叉連線操作符與左外部連線操作一起將幫助我識別那些在給定的一天內沒有售出的物品。滿足這些報告要求的程式碼可以在清單6中找到：
清單6：查詢未使用交叉連線銷售的產品讓我給你介紹一下這段程式碼。我建立一個子查詢，它選擇所有不同的SalesDate值。這個子查詢提供了所有銷售日期。然後，我將它與我的ProductTable連線起來。這允許我在每個SalesDate和每個Productrow之間建立一個笛卡兒產品。從交叉連線返回的集合將有我在最終結果集中所需的所有值，除了每個售出產品的Qty和TotalSalesAmt之和。為了獲得這些摘要值，我對SalesItem表執行一個左外部聯接，將它與我用交叉聯接操作建立的笛卡爾積連線起來。我基於Productid和SalesDate列執行了這個連線。通過使用左外部聯接，我的笛卡爾產品中的每一行都將被返回，如果Productid和SalesDate有一個匹配的SalesDate記錄，那麼Qty和TotalesAmt值將與適當的行相關聯。這個查詢所做的最後一件事是使用GROUPBY子句來總結基於SalesDate和ProductName的Qty和TotalesA掛載。績效考慮產生笛卡爾乘積的交叉連線運算元有一些效能方面需要考慮。因為SQL引擎需要將一組中的每一行與另一集中的每一行連線起來，所以結果集可能相當大。如果我做一個交叉連線，一個表有1，000，000行，另一個表有100，000行，那麼我的結果集將有1，000，000，000行，或100，000，000行。這是一個很大的結果集，建立它需要大量時間。交叉連線操作符可以是一個很好的解決方案，可以在兩個集合的所有可能組合中識別一個結果集，就像每個月所有客戶的所有銷售一樣，即使有幾個月有些客戶沒有銷售。當使用交叉連線運算子時，如果要優化效能，則應儘量減少交叉連線集的大小。例如，假設我有一個表，其中包含過去兩個月的銷售資料。如果我想要生成一個顯示一個月內沒有銷售的客戶的報告，那麼識別一個月中的天數的方法可能會極大地改變我的查詢的效能。為了演示這一點，讓我首先為1000名客戶建立一套為期兩個月的銷售記錄。我將使用清單7中的程式碼來完成這個任務。

清單7：建立用於效能測試的示例資料的TSQL清單7中的程式碼為1，000個不同的客戶建立了2個月的資料。此程式碼沒有為每七個客戶新增任何銷售資料。此程式碼生成1，000條CUST表記錄和52，338條銷售表記錄。為了演示如何使用交叉連線操作符，取決於在交叉連線輸入集中使用的集合的大小，讓我執行清單8和清單9中的程式碼。對於每個測試，我將記錄返回結果所需的時間。
清單8：交叉連線所有銷售記錄
清單9：針對銷售日期的不同列表交叉連線在清單8中，交叉連線操作符將1，000條Cust記錄與52，338條銷售記錄連線起來，生成52，338，000行的記錄集，然後用於確定一個月內零銷售額的客戶。在清單9中，我將選擇條件從Sales表更改為只返回一組不同的SalesDate值。這個不同的集合只產生61個不同的SalesDate值，因此清單9中的交叉連線操作的結果只生成61，000條記錄。通過減少交叉連線操作的結果集，清單9中的查詢在1秒內執行，而清單8中的程式碼在我的機器上執行時間為19秒。造成這種效能差異的主要原因是SQL Server需要為每個查詢執行的不同操作處理大量記錄。如果您檢視這兩個清單的執行計劃，您會發現計劃略有不同。但是，如果您檢視從巢狀迴圈(內連線)操作生成的估計記錄數量，在圖形計劃的右側，您將看到清單8估計了52，338，000條記錄，而清單9中的相同操作僅估計了61，000條記錄。清單8的查詢計劃從交叉連線巢狀迴圈操作生成的大型記錄集然後傳遞到幾個附加操作。因為清單8中的所有這些操作都必須針對5200萬條記錄工作。清單8比清單9慢得多。如您所見，在交叉連線操作中使用的記錄數量可能會極大地影響查詢執行的時間長度。因此，如果您可以編寫查詢以最小化交叉連線操作中涉及的記錄數量，則查詢的執行效率將大大提高。結語交叉連線運算子在兩個記錄集之間產生一個笛卡爾積。此操作符有助於識別一個表中沒有匹配記錄的項。應注意儘量減少與交叉連線操作符一起使用的記錄集的大小。通過確保交叉連線的結果集儘可能小，您將確保程式碼執行得越快。問答在本節中，您可以通過回答以下問題來回顧如何使用交叉聯接運算子來理解。問題1：交叉聯接運算子根據ON子句中指定的列，通過匹配兩個記錄集來建立結果集。(對還是錯)？千真萬確假的問題2：當表A和表B包含重複行時，哪一個公式可以用來標識從兩個表A和B之間的無約束交叉連線返回的行數？表A中的行數乘以表B中的行數表A中的行數乘以表B中的唯一行數表A中的唯一行數乘以表B中的行數表A中唯一行數乘以表B中唯一行數問題3：哪種方法提供了減少交叉連線操作產生的笛卡爾產品的最佳機會？確保連線的兩個集合儘可能多行。確保連線的兩個集合儘可能少行。確保交叉連線操作左邊的設定儘可能少行。確保交叉連線操作右側的設定儘可能少行。答案：問題1：正確的答案是b。交叉連線運算子不使用ON子句來執行交叉連線操作。它將一個表中的每一行連線到另一個表中的每一行。當交叉連線連線兩個集合時，它建立了一個笛卡爾積。問題2：正確的答案是A、b、c和d，因為如果表A或B中有重複行，則在為交叉聯接操作建立笛卡爾積時，每個重複行都是聯接。問題3：正確的答案是b。通過減少交叉連線操作中涉及的兩個集合的大小，使由交叉連線操作建立的最終集的大小最小化。c和d還有助於減小交叉連線操作建立的最終集的大小，但並不像確保涉及到交叉連線操作的兩個集合的行數最少一樣優化。本文是高階T-SQL樓梯的一部分。註冊我們的RSS提要，並得到通知，一旦我們釋出一個新的層次在樓梯上！