使用带有范围的SUM，嵌套的sql，运行缓慢

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?

我正在运行一个查询来计算一些数字,它似乎运行速度超慢。这是在MSSQL服务器上。运行查询大约需要22秒,只返回34条记录。问题是我在同一时间运行多个SUM,执行时间加起来。我已经将sql语句简化为我需要的准系统。我如何更快地运行?

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

1 个解决方案

#1

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).

然后我会建议索引:声明(status,dateon,rId,auditorId)和条目(rid,error_code,refunddue)。

#1