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?


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]
   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.Claimnumber     AS [Claim-Number], 
   d.Rma_number      AS [RMA-Number], 
   dbo.ToShortDateString(d.Received_Date, 1) AS [Received Date], 
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 
  • It looks like both links point to the COUNT version of the plan. Can you edit the like to the SUM version to point to the correct plan? – Geoff Patterson Nov 3 '15 at 14:59
  • What is the ratio of rows with those three statii compared to rows with other statii? – Max Vernon Nov 3 '15 at 15:01
  • 1
    @MaxVernon: yes, of course, i've seen too many zeros, you're right. Let me delete my comments. Yes, there are 16.7 million rows in other status. Most are Authorized. – Rango Nov 3 '15 at 15:19
  • 2
    I would estimate the second plan is suffering from having to scan the entire table 12 times (that is what is shows). This is likely coming from not being able to push down the predicates into the scan. What is performance like if you add WHERE c.Status = 'Approved' or c.Status = 'Valid' or c.status = 'Reject' to the SUM variant. – Max Vernon Nov 3 '15 at 15:27
  • @MaxVernon: there are twelve statuses in total. It's not really an issue for me, but i was very surpised that the optimizer can't handle this. I should really work on my execution-plan analyze skills. Make it an answer. What is your assumption, why is SQL-Server not able to scan only three statuses? – Rango Nov 3 '15 at 15:30

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.

  • Nice catch with the memory. I've noticed that all of my 32GB are currently in use (only 300 MB free). Edit However, i've freed up some memory. The result is the same – Rango Nov 3 '15 at 15:41
  • You may want to look at the max server memory option - it should be configured to the correct value for your system. You may want to look at this question and the answers for details on how to do that. – Max Vernon Nov 3 '15 at 15:59
  • 1
    Unfortunately this server is not only used for the database but also for a SSAS cube and some tools(including intranet web-app). But i have already assigned 12GB as maximum. – Rango Nov 3 '15 at 16:14

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.