sql server - How to optimize T-SQL Query? -

sql server - How to optimize T-SQL Query? -

i trying optimize next query increment performance particularly joins. appreciate suggestions , help. lot. thinking utilize cte instead of subqueries

query:

select year(dtb.tbdate) yearr, `convert(varchar(12),dtb.tbdate,110) dater, (case when dtb.l_grp_no= 7 company_b else company_a end ) portfolio, (case when dtb.past_days between 5 , 30 '5-30' when dtb.past_days between 31 , 60 then'31-60' when dtb.past_days between 61 , 90 '61-90' when dtb.past_days >= 91 '91+' when dtb.past_days <5 'current' else 'dis_po' end) 'qdel', case when lsc.sstatusc not null , dtb.tbdate> ls.eff_date lsc.sstatusc else 'xx'end 'lstatus', count(dtb.refaccno) lcount, sum (dtb.p_bal) lbal sln.[dbo].[table_dtb] dtb --left outer bring together sln.dbo.acct_l la on dtb.accrefno = la.accrefno left outer bring together (select stat_acct_l.* sln.dbo.stat_acct_l inner bring together (select refaccno, max(row_id) maxrow_id sln.dbo.stat_acct_l grouping refaccno) maxstatus on stat_acct_l.refaccno = maxstatus.refaccno , stat_acct_l.row_id = maxstatus.maxrow_id) ls on ls.refaccno = dtb.refaccno left outer bring together dw.dbo.accst_c lsc on lsc.stat_c_id= ls.status_code_no dtb.l_grp_no in (7,4,8,15)and dtb.tbdate > '2010-06-31' , dtb.p_bal+dtb.l_c_bal >0 grouping year(dtb.tbdate), convert(varchar(12),dtb.tbdate,110), case when dtb.past_days between 5 , 30 '5-30' when dtb.past_days between 31 , 60 then'31-60' when dtb.past_days between 61 , 90 '61-90' when dtb.past_days >= 91 '91+' when dtb.past_days <5 'current' else 'dis_po' end case when dtb.l_grp_no= 7 company_b else company_a end, case when lsc.sstatusc not null , dtb.tbdate> ls.eff_date yearthen lsc.sstatusc else 'xx' end order year(dtb.tbdate), convert(varchar(12),dtb.tbdate,110), case when dtb.l_grp_no= 7 company_belse company_a end

i noticed couple of things. first, can optimize case constructs if first take @ data. order cases ones homecoming true looked @ first. may have done that, it's hard tell.

next, move more filtering clause bring together clause. example, this:

left outer bring together dw.dbo.accst_c lsc on lsc.stat_c_id= ls.status_code_no dtb.l_grp_no in (7,4,8,15)and dtb.tbdate > '2010-06-31' , dtb.p_bal+dtb.l_c_bal >0

might run faster this:

left outer bring together dw.dbo.accst_c lsc on lsc.stat_c_id= ls.status_code_no , dtb.l_grp_no in (7,4,8,15)and dtb.tbdate > '2010-06-31' , dtb.p_bal+dtb.l_c_bal >0

also, don't select field don't need. need every column here?

left outer bring together (select stat_acct_l.* sln.dbo.stat_acct_l

maybe these help.

sql sql-server optimization