18

I have a query which runs much faster with select top 100 and much slower without top 100. The number of returned records is 0. Could you explain the difference in query plans or share links where such difference explained?

The query without top text:

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

The query plan for the above (without top):

https://pastebin.com/cbtJpxFf

enter image description here

The IO and TIME statistics (without 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.

The used indexes (without 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

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

The query plan (with TOP):

https://pastebin.com/0dyu6QZd

enter image description here


The query IO and TIME stats (with 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.

The used indexes (with 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

Will deeply appreciate any help on the topic!

15

SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower.

For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2.

For in-depth technical details, plus an example of where the TOP 100 algorithm is actually slower, read Paul White's Sorting, Row Goals, and the TOP 100 Problem.

The bottom line: in your case, if you know that no rows will be returned, well...don't run the query, eh? The fastest query is the one you never make. However, if you need to do an existence check, just do IF EXISTS(stick query here), and then SQL Server will do an even different execution plan.

  • Thanks, I will definitely read it. I have also noticed that rows estimates are incorrect in both scenarios. What might it be connected with? The statistics if fine - I have updated it with fullscan option on both clustered indexes. – George K Sep 22 '17 at 10:54
  • Also - is it suitable in my case as I do not have any ORDER, so I believe there no SORT is going to be in my plan? – George K Sep 22 '17 at 12:09
  • It will be very interesting to see if the execution plan is the same for both TOP 100 and TOP 101. If it is possible please share. Thanks. – Artashes Khachatryan Sep 23 '17 at 19:38
  • @GeorgeK I can see that you are using Dynamics AX. Watch out for trace flag [blogs.msdn.microsoft.com/axinthefield/… which can lead to a lot of estimate issues. If trace flag 4136 is disabled check if the "Partition" Column is the first in your indexes. Partition has usually very few distinct values. – Hans Vader Oct 2 '17 at 12:26
9

Looking at the two plans, you have a key lookup on both with dramatically different % costs. If you hover the mouse over the objects you will see the number of executions.

The key lookup is a lookup back to the clustered index as the index used in the index seek (top right) isn't covering all columns (select * so the clustered index must be used).

Top 100 is able to get the 100 rows needed in less reads from the index and then perform the lookup 100 times rather than for every row in the table. Also explains the increase in number of pages read when NOT doing the 'top'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.