Sql-Server

在這種情況下，表變數如何提高查詢的性能？

  • May 23, 2018

對於這種特定情況，我將在下面嘗試解釋，使用表變數比不使用表變數執行得更好。

我想知道為什麼，如果可能的話，擺脫 table 變數。

這是使用表變數的查詢：

USE [BISource_UAT]
GO

set statistics io on
SET STATISTICS TIME ON

   SET NOCOUNT ON;

   DECLARE @OrderStartDate DATETIME = '15-feb-2015'
   DECLARE @OrderEndDate DATETIME = '28-feb-2016'
   DECLARE @tmp TABLE
   (
   strBxOrderNo VARCHAR(20)
   ,sintReturnId INT
   )  

   INSERT INTO @tmp
   SELECT  strBxOrderNo
           ,sintReturnId
   FROM    TABLEBACKUPS.dbo.tblBReturnHistory rh
   WHERE   rh.sintReturnStatusId in ( 3 )
   AND     rh.dtmAdded >= @OrderStartDate
   AND     rh.dtmAdded < @OrderEndDate

   SELECT 
            op.lngPaymentID
           ,op.strBxOrderNo
           ,op.sintPaymentTypeID
           ,op.strCurrencyCode
           ,op.strBCCurrencyCode
           ,op.decPaymentAmount
           ,op.decBCPaymentAmount
           ,ap.strAccountCode
           ,o.sintMarketID
           ,o.sintOrderChannelID
           ,o.sintOrderTypeID
           ,CASE   WHEN opgv.lngpaymentID IS NULL THEN NULL
                    -- Not a Voucher = Null
               WHEN gvp.strIssuedBxOrderNo IS NULL THEN 0 ELSE 1 
             END AS [IsPromoVoucher] -- Is a Voucher - check type
           ,o.sdtmOrdCreated

   FROM    @tmp rh

           INNER JOIN TABLEBACKUPS.dbo.tblBReturn r 
                   ON r.sintReturnId = rh.sintReturnId 
                  AND r.strBxOrderNo = rh.strBxOrderNo

           INNER JOIN bocss2.dbo.tblBOrder o 
                   ON o.strBxOrderNo = r.strBxOrderNo

           INNER JOIN Bocss2.dbo.tblBOrderPayment op 
                   ON op.strBxOrderNo = o.strBxOrderNo

           INNER JOIN TABLEBACKUPS.dbo.tblBOrderItemReturn AS oir 
                   ON r.sintReturnId = oir.sintReturnID 
                  AND r.strBxOrderNo = oir.strBxOrderNo

           INNER JOIN Bocss2.dbo.tblBOrderItem AS i 
                   ON i.strBxOrderNo = oir.strBxOrderNo 
                  AND i.sintOrderSeqNo = oir.sintOrderSeqNo

           INNER JOIN TABLEBACKUPS.dbo.tblBAccountParticipant ap 
                  ON o.lngAccountParticipantID = ap.lngParticipantID

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBOrderPaymentGiftVoucher opgv 
                        ON op.lngPaymentID = opgv.lngPaymentID

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucher gv 
                        ON opgv.strVoucherNumber = gv.strVoucherNumber

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucherPromotion gvp 
                        ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

   WHERE   oir.decReturnFinalAmount > 0
   AND     o.sdtmOrdCreated >= @OrderStartDate

這會產生以下統計資訊：

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 78 ms, elapsed time = 86 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
Table '#BF0B2154'. Scan count 0, logical reads 1957, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturnHistory'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 16 ms,  elapsed time = 9 ms.
Table 'tblBGiftVoucherPromotion'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucher'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPaymentGiftVoucher'. Scan count 0, logical reads 452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItem'. Scan count 0, logical reads 904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPayment'. Scan count 186, logical reads 1649, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBAccountParticipant'. Scan count 0, logical reads 7112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrder'. Scan count 3557, logical reads 14267, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItemReturn'. Scan count 1951, logical reads 5865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturn'. Scan count 0, logical reads 3902, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#BF0B2154'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 125 ms,  elapsed time = 138 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

使用showplan_text 我想顯示查詢計劃：

查詢的第一部分 - 填充表變數 在此處輸入圖像描述

查詢的第二部分：使用表變數並加入其他表： 在此處輸入圖像描述

這是使用表變數的查詢的 XML 計劃。

現在這是不使用表變數的相同查詢：

USE [BISource_UAT]
GO

set statistics io on
SET STATISTICS TIME ON

   SET NOCOUNT ON;

   DECLARE @OrderStartDate DATETIME = '15-feb-2015'
   DECLARE @OrderEndDate DATETIME = '28-feb-2016'

   SELECT 
            op.lngPaymentID
           ,op.strBxOrderNo
           ,op.sintPaymentTypeID
           ,op.strCurrencyCode
           ,op.strBCCurrencyCode
           ,op.decPaymentAmount
           ,op.decBCPaymentAmount
           ,ap.strAccountCode
           ,o.sintMarketID
           ,o.sintOrderChannelID
           ,o.sintOrderTypeID
           ,CASE   WHEN opgv.lngpaymentID IS NULL 
              THEN NULL -- Not a Voucher = Null
               WHEN gvp.strIssuedBxOrderNo IS NULL 
               THEN 0 ELSE 1 END AS [IsPromoVoucher] 
               -- Is a Voucher - check type
           ,o.sdtmOrdCreated

   FROM    TABLEBACKUPS.dbo.tblBReturnHistory rh

           INNER JOIN TABLEBACKUPS.dbo.tblBReturn r 
                   ON r.sintReturnId = rh.sintReturnId 
                  AND r.strBxOrderNo = rh.strBxOrderNo

           INNER JOIN bocss2.dbo.tblBOrder o 
                   ON o.strBxOrderNo = r.strBxOrderNo
                  AND o.sdtmOrdCreated >= @OrderStartDate

           INNER JOIN Bocss2.dbo.tblBOrderPayment op 
                   ON op.strBxOrderNo = o.strBxOrderNo

           INNER JOIN TABLEBACKUPS.dbo.tblBOrderItemReturn AS oir 
                   ON r.sintReturnId = oir.sintReturnID 
                  AND r.strBxOrderNo = oir.strBxOrderNo
                  AND oir.decReturnFinalAmount > 0

           INNER JOIN Bocss2.dbo.tblBOrderItem AS i 
                   ON i.strBxOrderNo = oir.strBxOrderNo 
                  AND i.sintOrderSeqNo = oir.sintOrderSeqNo

           INNER JOIN TABLEBACKUPS.dbo.tblBAccountParticipant ap 
                  ON o.lngAccountParticipantID = ap.lngParticipantID

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBOrderPaymentGiftVoucher opgv 
                        ON op.lngPaymentID = opgv.lngPaymentID

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucher gv 
                        ON opgv.strVoucherNumber = gv.strVoucherNumber

           LEFT OUTER JOIN TABLEBACKUPS.dbo.tblBGiftVoucherPromotion gvp 
                        ON gvp.strIssuedBxOrderNo = gv.strIssuedBxOrderNo

   WHERE   rh.sintReturnStatusId in ( 3 )
   AND     rh.dtmAdded >= @OrderStartDate
   AND     rh.dtmAdded < @OrderEndDate

查看統計數據時，我們得到了以下資訊：

SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucher'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBAccountParticipant'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturn'. Scan count 1, logical reads 170, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItemReturn'. Scan count 0, logical reads 35849, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPayment'. Scan count 9408, logical reads 87643, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderItem'. Scan count 1950, logical reads 8336, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrder'. Scan count 1951, logical reads 7835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBReturnHistory'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBOrderPaymentGiftVoucher'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBGiftVoucherPromotion'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 625 ms,  elapsed time = 612 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

現在，關於文本格式的執行計劃：

設置參數

在此處輸入圖像描述

現在是重要的部分，執行節目： 在此處輸入圖像描述

這是不使用表變數的查詢的 XML 計劃。

但是為什麼使用表變數我得到更少的讀取，更少的 I/O，並且執行（不清除記憶體）總是更快？

我可以提供任何創建表腳本，或任何其他必要的東西來更好地理解這種情況。

在這裡發表任何評論，我會回复。

這是一個類似的問題：

為什麼在這種特定情況下使用表變數的速度是 #temp 表的兩倍多？

在CHECKPOINT之後執行查詢時；DBCC 刪除緩衝區；結果是：

使用表變數查詢

使用表變數查詢

沒有表變數的查詢

沒有表變數的查詢

這裡的主要因素是：

  • 優化器不會試圖找到最佳計劃；它的目標是快速找到一個合理的計劃
  • 它假定查詢將使用冷記憶體執行
  • 使用的成本模型有利於順序 I/O 而不是隨機 I/O
  • 假設重複搜尋索引是隨機分佈的

表變數的基數估計為 1 行（除非發生語句級重新編譯，或者跟踪標誌 2453處於活動狀態）。這種低估計導致成本非常低的計劃，具有基於嵌套循環的導航策略。該計劃對於相對較低的行數可能會繼續有效，尤其是在不需要從持久儲存中讀取所需數據的情況下。

通過更準確的基數估計，優化器傾向於使用散列連接和一些掃描的計劃。考慮到上面列出的假設，這似乎比導航策略便宜；特別是關於冷記憶體，以及與許多查找相比順序掃描的成本相對較低（假設大部分是隨機 I/O 模式）。

如果需要的數據不在記憶體中，則表變數計劃可能比替代計劃慢 -或者它可能不在。成本模型就是這樣——一個模型——使用的確切數字可能不代表您的硬體和配置，並且所做的假設在特定情況下可能無效。

所有這些警告尤其適用於低成本查詢（兩者都是），因為小的成本變化會產生非常不同的計劃形狀。事實上，這兩個計劃都是成功的，因為它們能夠快速有效地產生結果。

引用自：https://dba.stackexchange.com/questions/134987

相關問答

Sql-Server

隱式轉換不影響性能

  • February 14, 2022
檢視問答
Sql-Server

由於阻塞，無法檢索正在執行的查詢的查詢計劃

  • November 12, 2020
檢視問答
Sql-Server

SQL Server 2014：自聯接基數估計不一致的任何解釋？

  • August 24, 2020
檢視問答
Sql-Server

什麼可能導致睡眠會話有一個打開的事務？

  • July 15, 2020
檢視問答
Sql-Server

為什麼我的 EXISTS 查詢執行索引掃描而不是索引查找？

  • May 12, 2020
檢視問答
Sql-Server

執行查詢但不向客戶端返回結果

  • April 27, 2020
檢視問答