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

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -