Pytanie o wydajność „WYBIERZ TOP”

Mam zapytanie, które działa znacznie szybciej z select top 100i znacznie wolniej bez top 100. Liczba zwróconych rekordów wynosi 0. Czy możesz wyjaśnić różnicę w planach zapytań lub udostępnić linki tam, gdzie taka różnica jest wyjaśniona?

Zapytanie bez toptekstu:

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

Plan zapytań dla powyższych (bez top):

https://pastebin.com/cbtJpxFf

Statystyki IO i TIME (bez 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.

Zastosowane indeksy (bez 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

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

Plan zapytań (z TOP):

https://pastebin.com/0dyu6QZd

Zapytania statystyki IO i TIME (z 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.

Zastosowane indeksy (z 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

Z pewnością doceni każdą pomoc na ten temat!

SQL Server buduje różne plany wykonania dla TOP 100, używając innego algorytmu sortowania. Czasami jest szybszy, a czasem wolniejszy.

Aby zapoznać się z prostszymi przykładami, przeczytaj, jak wiele wiersz może zmienić plan zapytań? Część 1 i część 2 .

Aby uzyskać szczegółowe informacje techniczne, a także przykład, gdzie algorytm TOP 100 jest rzeczywiście wolniejszy, przeczytaj : Sortowanie Paula White'a, Cele rzędu i Problem TOP 100 .

Podsumowując: w twoim przypadku, jeśli wiesz, że nie zostaną zwrócone żadne wiersze, cóż ... nie uruchamiaj zapytania, co? Najszybsze zapytanie jest tym, którego nigdy nie wykonałeś. Jeśli jednak musisz przeprowadzić kontrolę istnienia, po prostu wykonaj JEŻELI ISTNIEJE (zapytanie tutaj), a następnie SQL Server wykona jeszcze inny plan wykonania.

Patrząc na te dwa plany, masz kluczowy przegląd obu przy radykalnie różnych% kosztach. Jeśli najedziesz myszką na obiekty, zobaczysz liczbę wykonań.

Wyszukiwanie klucza jest przeglądem wstecz do indeksu klastrowanego, ponieważ indeks używany w wyszukiwaniu indeksu (prawy górny róg) nie obejmuje wszystkich kolumn (wybierz *, więc należy użyć indeksu klastrowanego).

Top 100 jest w stanie uzyskać 100 wierszy potrzebnych w mniejszej liczbie odczytów z indeksu, a następnie wykonać wyszukiwanie 100 razy, a nie dla każdego wiersza w tabeli. Wyjaśnia także wzrost liczby stron czytanych, gdy NIE robi się „góry”.