‘SELECT TOP’ 表現問題

我有一個查詢，它使用 select 執行得更快，top 100而沒有top 100. 返回的記錄數為0。您能否解釋一下查詢計劃中的差異或分享解釋這種差異的連結？

沒有top文字的查詢：

SELECT --TOP 100
*
FROM InventTrans
    JOIN
    InventDim
    ON InventDim.DATAAREAID = 'dat' AND 
       InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
     InventTrans.ITEMID = '027743' AND 
     InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
     InventDim.ECC_BUSINESSUNITID = 'Казахстан';

---

上述查詢計劃（不帶top）：

https://pastebin.com/cbtJpxFf

在此處輸入圖像描述

IO 和 TIME 統計資訊（不帶top）：

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.

(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 6256 ms,  elapsed time = 13348 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.

---

使用的索引（不帶top）：

1. INVENTTRANS.I_177TRANSIDIDX
  4 KEYS:
- DATAAREAID
- INVENTTRANSID
- INVENTDIMID
- RECID
2. INVENTTRANS.I_177ITEMIDX
  3 KEYS:
  - DATAAREAID
  - ITEMID
  - DATEPHYSICAL 
3. INVENTDIM.I_698DIMIDIDX
  2 KEYS:
  - DATAAREAID
  - INVENTDIMID

---

查詢top：

SELECT TOP 100
*
FROM InventTrans
    JOIN
    InventDim
    ON InventDim.DATAAREAID = 'dat' AND 
       InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND 
     InventTrans.ITEMID = '027743' AND 
     InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND 
     InventDim.ECC_BUSINESSUNITID = 'Казахстан';

---

查詢計劃（帶TOP）：

https://pastebin.com/0dyu6QZd

在此處輸入圖像描述

---

查詢 IO 和 TIME 統計資訊（帶 TOP）：

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.

(0 row(s) affected)
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 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 265 ms,  elapsed time = 257 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.

---

使用的索引（帶TOP）：

1. INVENTTRANS.I_177TRANSIDIDX
    4 KEYS:
    - DATAAREAID
    - INVENTTRANSID
    - INVENTDIMID
    - RECID
2. INVENTTRANS.I_177DIMIDIDX
   3 KEYS:
   - DATAAREAID
   - INVENTDIMID
   - ITEMID
3. INVENTDIM.I_698DIMIDIDX
   2 KEYS:
   - DATAAREAID
   - INVENTDIMID
4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
   3 KEYS
   - DATAAREAID
   - ECC_BUSINESSUNITID
   - INVENTLOCATIONID

將非常感謝有關該主題的任何幫助！

SQL Server 使用不同的排序算法為 TOP 100 建構不同的執行計劃。有時它更快，有時它更慢。

有關它的更簡單範例，請閱讀一行可以更改查詢計劃多少？第 1部分和第 2 部分。

有關深入的技術細節，以及 TOP 100 算法實際上較慢的範例，請閱讀Paul White 的排序、行目標和 TOP 100 問題。

底線：在您的情況下，如果您知道不會返回任何行，那麼…不要執行查詢，是嗎？最快的查詢是您從未做過的查詢。但是，如果您需要進行存在性檢查，只需執行 IF EXISTS（此處粘貼查詢），然後 SQL Server 將執行一個甚至不同的執行計劃。

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