Dlaczego wielokrotne LICZBA jest szybsze niż jedno SUMA z CASE?

Chciałem wiedzieć, które z poniższych dwóch podejść jest szybsze:

1) Trzy COUNT:

 SELECT Approved = (SELECT COUNT(*) FROM dbo.Claims d
                  WHERE d.Status = 'Approved'),
        Valid    = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Valid'),
        Reject   = (SELECT COUNT(*) FROM dbo.Claims d
                    WHERE d.Status = 'Reject')

2) SUMz FROMklauzulą:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c;

Byłem zaskoczony, że różnica jest tak duża. Pierwsze zapytanie z trzema podkwerendami natychmiast zwraca wynik, natomiast drugie SUMpodejście wymaga 18 sekund.

Claimsjest widokiem, który wybiera z tabeli zawierającej ~ 18 milionów wierszy. W kolumnie FK znajduje się indeks ClaimStatustabeli, która zawiera nazwę statusu.

Dlaczego robi to tak wielką różnicę, czy używam, COUNTczy SUM?

Plany wykonawcze:

• COUNT
• SUM

W sumie jest 12 statusów. Te trzy statusy należą do 7% wszystkich rzędów.

To jest rzeczywisty widok, nie jestem pewien, czy jest odpowiedni:

CREATE VIEW [dbo].[Claims]
AS
SELECT 
   mu.Marketunitname AS MarketUnit, 
   c.Countryname     AS Country, 
   gsp.Gspname       AS GSP, 
   gsp.Wcmskeynumber AS GspNumber, 
   sl.Slname         AS SL, 
   sl.Wcmskeynumber  AS SlNumber, 
   m.Modelname       AS Model, 
   m.Salesname       AS [Model-Salesname], 
   s.Claimstatusname AS [Status], 
   d.Work_order      AS [Work Order], 
   d.Ssn_number      AS IMEI, 
   d.Ssn_out, 
   Remarks, 
   d.Claimnumber     AS [Claim-Number], 
   d.Rma_number      AS [RMA-Number], 
   dbo.ToShortDateString(d.Received_Date, 1) AS [Received Date], 
   Iddata, 
   Fisl, 
   Fimodel, 
   Ficlaimstatus 
FROM Tabdata AS d 
   INNER JOIN Locsl AS sl 
           ON d.Fisl = sl.Idsl 
   INNER JOIN Locgsp AS gsp 
           ON sl.Figsp = gsp.Idgsp 
   INNER JOIN Loccountry AS c 
           ON gsp.Ficountry = c.Idcountry 
   INNER JOIN Locmarketunit AS mu 
           ON c.Fimarketunit = mu.Idmarketunit 
   INNER JOIN Modmodel AS m 
           ON d.Fimodel = m.Idmodel 
   INNER JOIN Dimclaimstatus AS s 
           ON d.Ficlaimstatus = s.Idclaimstatus 
   INNER JOIN Tdefproducttype 
           ON d.Fiproducttype = Tdefproducttype.Idproducttype 
   LEFT OUTER JOIN Tdefservicelevel 
                ON d.Fimaxservicelevel = Tdefservicelevel.Idservicelevel 
   LEFT OUTER JOIN Tdefactioncode AS ac 
                ON d.Fimaxactioncode = ac.Idactioncode 

COUNT(*)Wersja jest w stanie po prostu dążyć do indeksu masz na kolumnie statusu raz dla każdego statusu zaznaczania, natomiast SUM(...)potrzeby Wersja do poszukiwania indeksu dwanaście razy (całkowita liczba unikalnych typów status).

Wyraźne wyszukiwanie indeksu trzy razy będzie szybsze niż wyszukiwanie go 12 razy.

Pierwszy plan wymaga przyznania pamięci w wysokości 238 MB, podczas gdy drugi plan wymaga przyznania pamięci w wysokości 650 MB. Może się zdarzyć, że większy przydział pamięci nie może zostać natychmiast wypełniony, co powoduje, że zapytanie jest znacznie wolniejsze.

Zmień drugie zapytanie na:

SELECT  Approved = SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END),
        Valid    = SUM(CASE WHEN Status = 'Valid'    THEN 1 ELSE 0 END),
        Reject   = SUM(CASE WHEN Status = 'Reject'   THEN 1 ELSE 0 END)
FROM dbo.Claims c
WHERE c.Status = 'Approved'
    OR c.Status = 'Valid'
    OR c.Status = 'Reject';

Umożliwi to optymalizatorowi kwerendy wyeliminowanie 75% poszukiwanych indeksów i powinno skutkować zarówno niższym wymaganym przyznaniem pamięci, niższymi wymaganiami we / wy i szybszym czasem do uzyskania wyniku.

SUM(CASE WHEN ...)Konstrukt zasadniczo zapobiega optymizatora z przesuwanie Statusorzeczniki dół do indeksu poszukiwania część planu.