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:
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
ELSE 0
(or make itELSE NULL
) for the count to work properly.COUNT(CASE...
. It needs also 18 seconds. I have noticed that a scalar valued function in the viewToShortDateString
also causes 3 of the 18 seconds.