using SUM with a range, nested sql, runs slow

Question

I'm running a query to tally up some numbers and it seems to be running super slow. This is on an MSSQL server. It takes about 22 seconds to run the query with only 34 records returned. The problem is that I'm running multiple SUM's at the same time and the execute time adds up. I've simplified the sql statement here to the barebones of what i need. How do i run this faster?

SELECT 

  SUM(case when (claims.dateon >= '20161110' AND claims.dateon < '20161117') AND entries.errorCode NOT IN('DP','RB','WP','PE','OV') then entries.refundDue else 0.0 end) as rate1

FROM auditors 

INNER JOIN claims 
   ON claims.auditorID = auditors.auditorID 
  AND claims.status='closed' 
--AND (claims.dateon >= '20161020' AND claims.dateon < '20161117') 
INNER JOIN entries 
   ON claims.rID = entries.rid 
WHERE claims.status = 'closed' 
  AND (claims.dateon >= '20161020' AND claims.dateon < '20161117') 

GROUP BY auditors.auditorID

Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. — Juan Carlos Oropeza, Nov 17 '16 at 19:41
I'm not too experienced with SQL so EXPLAIN ANALYZE, this is the first i'm hearing of it. Sorry about the slow, but 22 seconds. I figure with the amount returned it should be <1 sec. — Damien, Nov 17 '16 at 19:43
check [here](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) also a description of your tables and how many rows have, and what indexes you are created — Juan Carlos Oropeza, Nov 17 '16 at 19:45
about 72,000 rows. No indexes created, not sure how or what to use indexes for — Damien, Nov 17 '16 at 19:48

score 0 · Accepted Answer · answered Nov 17 '16 at 19:44

I would write this query more like this:

SELECT auditor_id,
       sum(case when c.dateon >= '20161110' AND c.dateon < '20161117' AND
                     e.errorCode NOT IN ('DP', 'RB', 'WP', 'PE', 'OV')
                then e.refundDue else 0.0
           end) as rate1
FROM auditors a INNER JOIN
     claims c
     ON c.auditorID = a.auditorID AND c.status ='closed' INNER JOIN
     entries e
     ON c.rID = e.rid
WHERE c.status = 'closed' AND (c.dateon >= '20161020' AND c.dateon < '20161117') 
GROUP BY a.auditorID;

Then I would notice that the join to auditors is not necessary:

SELECT auditor_id,
       sum(case when c.dateon >= '20161110' AND c.dateon < '20161117' AND
                     e.errorCode NOT IN ('DP', 'RB', 'WP', 'PE', 'OV')
                then e.refundDue else 0.0
           end) as rate1
FROM claims c INNER JOIN
     entries e
     ON c.rID = e.rid
WHERE c.status = 'closed' AND
      (c.dateon >= '20161020' AND c.dateon < '20161117') 
GROUP BY c.auditorID;

Then I would suggest indexes: claims(status, dateon, rId, auditorId) and entries(rid, error_code, refunddue).

Sorry, but I broke down the SQL statement to the necessary components. Auditors is necessary. Not sure what you mean by indexes — Damien, Nov 17 '16 at 19:45
`Not sure what you mean by indexes` that doesnt sound good. And index is like the index book a way to jump faster to where you need to go. Reduce query time by a lot. Check [**here**](http://www.w3schools.com/sql/sql_create_index.asp) — Juan Carlos Oropeza, Nov 17 '16 at 19:47

using SUM with a range, nested sql, runs slow

1 Answers1