Why are multiple COUNT faster than one SUM with CASE?

Question

I wanted to know which of the following two approaches is faster:

1) Three 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) SUM with FROM-clause:

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;

I was suprised that the difference is so large. The first query with three subqueries returns the result immediately whereas the second SUM approach needs 18 seconds.

Claims is a view which selects from a table containing ~18 million rows. There is an index on the FK-Column to the ClaimStatus table which contains the status-name.

Why does it make such a great difference whether i use COUNT or SUM?

Execution-plans:

• COUNT
• SUM

There are 12 statuses in total. Those three statuses belong to 7% of all rows.

---

This is the actual view, i'm not sure if it's relevant:

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 

Answer 1

The COUNT(*) version is able to simply seek into the index you have on the status column once for each status you are selecting, whereas the SUM(...) version needs to seek the index twelve times (the total number of unique status types).

Clearly seeking an index three times is going to be faster than seeking it 12 times.

The first plan requires a memory grant of 238MB, whereas the second plan requires a memory grant of 650MB. It may be that the larger memory grant could not be immediately filled, making the query that much slower.

Alter the second query to be:

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';

This will allow the query optimizer to eliminate 75% of the index seeks, and should result in both a lower required memory grant, lower I/O requirements, and faster time-to-result.

The SUM(CASE WHEN ...) construct essentially prevents the query optimizer from pushing the Status predicates down into the index seek portion of the plan.