sql server - Help improving SQL join -

i have stored procedure runs update gaming points user balances. it's insert 5 subqueries. have isolated 1 of subqueries query slows entire batch down. without it, stored procedure run in under 2 seconds. it, take as 8 seconds. 8 seconds isn't end of world, sake of scalability, need have complete faster. here isolated subquery:

(select isnull(sum(a.transamount) + sum(case when a.betresult = 1 (a.betwinamount + (a.transamount * -1)) end), 0)             user_t             left outer join user_td b on a.tid = b.tid             left outer join lines_bl c on b.lid = c.lid             left outer join lines_bm d on c.bmid = d.bmid             left outer join event_m e on d.eid = e.eid             left outer join event_kb f on a.transreason = f.bid             left outer join event_m g on f.bid = g.eid         a.userid = u.userid , (a.issettled = 1)         ,          (         (a.transreason = 1 , (datediff(dd, convert(datetime, e.edate, 101), convert(datetime, @enddate, 101)) = @daysago)) or          (a.transreason >= 3000 , (datediff(dd, convert(datetime, g.edate, 101), convert(datetime, @enddate, 101)) = @daysago)                 ,  [dbo].[event_ceafkbid](a.transreason) = 1) or         (a.transreason between 3 , 150 , (datediff(dd, convert(datetime, a.transdt, 101), convert(datetime, @enddate, 101)) = @daysago))         ) 

what have done further isolate: when run select * on joins (without clauses), performance in - > 100000 rows in under second. add in clauses, believe great slow down 'or' clause and/or function needs evaluated.

as understand it, function inside clause evaluates each row - opposed somehow caching definition of function , evaluating way. have indexes on tables, wondering if of them not correct.

without knowing full database structure, sure it's difficult pin down problem is, pointed in direction begin further isolate.

i suspect biggest performance hits correlated subquery (whatever table behind u.userid) , embedded function call dbo.event_ceafkbid. of course depends upon how big tables (how many rows being read). datetime conversions won’t , generate strong “bad design” smell, don’t think they’d impact performance much.

those left outer joins ugly, optimizer has check them row – if “a” big, all joins on all rows have performed, if there’s no data there. if can replaced inner joins, so, i’m guessing not because of “table e or table g” logic. lesses, sure looks you’ve got 3 separate queries moshed one; if broke out three, unioned together, it’d frankenstein query below. i’ve no idea if run faster or not (heck, can’t debug query , make sure panetheses balance), if you’ve got sparse data relative logic should run pretty fast. (i took out date conversions make code more legible, you’d have plug them in.)

select isnull(sum(total), 0) finaltotal ( select     sum(a.transamount + case when a.betresult = 1 a.betwinamount - a.transamount else 0 end) total  user_t              inner join user_td b on a.tid = b.tid              inner join lines_bl c on b.lid = c.lid              inner join lines_bm d on c.bmid = d.bmid              inner join event_m e on d.eid = e.eid               a.userid = u.userid   , a.issettled = 1   , a.transreason = 1    , (datediff(dd, e.edate, @enddate) = @daysago))   union select     sum(a.transamount + case when a.betresult = 1 a.betwinamount - a.transamount else 0 end) total  user_t             inner join event_kb f on a.transreason = f.bid              inner join event_m g on f.bid = g.eid          a.userid = u.userid   , a.issettled = 1   , a.transreason >= 3000    , (datediff(dd, g.edate, @enddate) = @daysago)                    , [dbo].[event_ceafkbid](a.transreason) = 1  union select     sum(a.transamount + case when a.betresult = 1 a.betwinamount - a.transamount else 0 end) total  user_t              a.userid = u.userid   , a.issettled = 1   , a.transreason between 3 , 150    , datediff(dd, a.transdt, @enddate) = @daysago) ) threewayunion