sql server - using SUM with a range, nested sql, runs slow -
i'm running query tally numbers , seems running super slow. on mssql server. takes 22 seconds run query 34 records returned. problem i'm running multiple sum's @ same time , execute time adds up. i've simplified sql statement here barebones of need. how run faster?
select sum(case when (claims.dateon >= '20161110' , claims.dateon < '20161117') , entries.errorcode not in('dp','rb','wp','pe','ov') entries.refunddue else 0.0 end) rate1 auditors inner join claims on claims.auditorid = auditors.auditorid , claims.status='closed' --and (claims.dateon >= '20161020' , claims.dateon < '20161117') inner join entries on claims.rid = entries.rid claims.status = 'closed' , (claims.dateon >= '20161020' , claims.dateon < '20161117') group auditors.auditorid
i write query more this:
select auditor_id, sum(case when c.dateon >= '20161110' , c.dateon < '20161117' , e.errorcode not in ('dp', 'rb', 'wp', 'pe', 'ov') e.refunddue else 0.0 end) rate1 auditors inner join claims c on c.auditorid = a.auditorid , c.status ='closed' inner join entries e on c.rid = e.rid c.status = 'closed' , (c.dateon >= '20161020' , c.dateon < '20161117') group a.auditorid;
then notice join auditors not necessary:
select auditor_id, sum(case when c.dateon >= '20161110' , c.dateon < '20161117' , e.errorcode not in ('dp', 'rb', 'wp', 'pe', 'ov') e.refunddue else 0.0 end) rate1 claims c inner join entries e on c.rid = e.rid c.status = 'closed' , (c.dateon >= '20161020' , c.dateon < '20161117') group c.auditorid;
then suggest indexes: claims(status, dateon, rid, auditorid)
, entries(rid, error_code, refunddue)
.
Comments
Post a Comment