sql-server – ‘SELECT TOP’性能问题

我有一个查询,选择前100名运行得更快,而前100名则慢得多.返回记录的数量为0.
你能解释一下查询计划的不同之处或分享这些差异所解释的链接吗？

没有热门文字的查询：

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 = 'Казахстан';

上面的查询计划(没有顶部)：

https://pastebin.com/cbtJpxFf

IO和TIME统计信息(无顶部)：

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,read-ahead reads 14299,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.

使用的索引(没有顶部)：

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

顶部的查询：

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

使用的索引(带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构建不同的执行计划.有时它更快,有时它更慢.

有关它的简单示例,请阅读How Much Can One Row Change A Query Plan? Part 1和@L_403_5@.

有关深入的技术细节,以及TOP 100算法实际较慢的示例,请阅读Paul White’s Sorting,Row Goals,and the TOP 100 Problem.

底线：在你的情况下,如果你知道不会返回任何行,那么……不要运行查询,是吗？最快的查询是你从未做过的.但是,如果您需要进行存在检查,只需执行IF EXISTS(在此处查询),然后sql Server将执行甚至不同的执行计划.

sql-server – ‘SELECT TOP’性能问题

解决方法

猜你在找的MsSQL相关文章