Optimizing SQL Server 2012 Query -
i have query runs 12 hours.
the query left joins on 5 tables , reports on bunch of monthly metrics. here query:
select datepart(yyyy,referral_dt) refyear, datepart(mm,referral_dt) refmonth, case when cast(referral_dt date) between '1/1/2013' , '4/14/2013' 'q1' when cast(referral_dt date) between '4/15/2013' , '7/14/2013' 'q2' when cast(referral_dt date) between '7/15/2013' , '9/30/2013' 'q3' when cast(referral_dt date) between '10/1/2013' , '12/31/2013' 'q4' when cast(referral_dt date) between '1/1/2014' , '4/14/2014' 'q1' when cast(referral_dt date) between '4/15/2014' , '7/14/2014' 'q2' when cast(referral_dt date) between '7/15/2014' , '9/30/2014' 'q3' when cast(referral_dt date) between '10/1/2014' , '12/31/2014' 'q4' else 'x' end refqtr, crm.salesstatuscode, crm.referral_state, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname, sr.region sales_region,sr.market sales_market, sr.saleschannel saleschannel, bk.superregion_name bank_superregion, bk.region_name bank_region ,bk.division_name bank_division, sum(case when crm.referral_state = 'won' 1 else 0 end) referrals_won, sum(sv.projected_profit) prj_profit, sum(case when mp.proposal_date null 0 else 1 end) proposals_created, sum(ac.signed_annual_volume) total_signed_volume, sum(case when ac.acct_act_date null 0 else 1 end) activated_accounts, count(*) referral_count moagg1 kaiserver.dbkai.dbo.referrals_crm crm left join (select p.merchant_id, cast(p.proposal_create_dt date) proposal_date, m.mcc_desc, m.mcc_industry kaiserver.[dbkai].[dbo].[proposals] p left join (select mcc, mcc_desc, mcc_industry kaiserver.[dbkai].[dbo].[merchantcategorycode]) m on p.mcc = m.mcc datepart(yyyy,proposal_create_dt) in ('2013', '2014')) mp on crm.merchant_id = mp.merchant_id left join (select account_no, cast(account_activate_dt date) acct_act_date, signed_annual_volume, average_tkt kaiserver.[dbkai].[dbo].[account] current_ind=1 , datepart(yyyy,account_submit_dt) in ('2013', '2014')) ac on crm.account_no = ac.account_no left join (select e1.repid, e1.repcode, e1.payrollname, e1.salesmanager, e2.payrollname manager_name, e1.region,e1.market, e1.saleschannel [fdserver].fdms.[dbo].[tbl_reps] e1 left join [fdserver].fdms.[dbo].[tbl_reps] e2 on e1.salesmanager = e2.repid e1.market not ('%test%') , e1.payrollname not null , e1.region not null , e1.market not null) sr on crm.sales_rep_cd = sr.repcode left join (select [au_name], [au_code] ,[superregion_name], [region_name] ,[division_name], [subdivision_name] ,[district_name] ,[subdistrict_name] kaiserver.[dbkai].[dbo].[bankau_hierarchy] [reporting_interval_id] = '201410') bk on crm.referral_au = bk.au_code left join (select merchantnumber, projected_profit kaiserver.[dbkai].[dbo].[soldvolumedetail]) sv on crm.account_no = sv.merchantnumber datepart(yyyy, referral_dt) in ('2013', '2014') , (crm.salesstatuscode <> 'dupl' or crm.salesstatuscode null) , crm.lead_source not in ('test lead', 'bank lead placeholder') group datepart(yyyy,referral_dt), datepart(mm,referral_dt), crm.referral_state, crm.salesstatuscode, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname, sr.region,sr.market, sr.saleschannel, bk.superregion_name, bk.region_name ,bk.division_name, case when cast(referral_dt date) between '1/1/2013' , '4/14/2013' 'q1' when cast(referral_dt date) between '4/15/2013' , '7/14/2013' 'q2' when cast(referral_dt date) between '7/15/2013' , '9/30/2013' 'q3' when cast(referral_dt date) between '10/1/2013' , '12/31/2013' 'q4' when cast(referral_dt date) between '1/1/2014' , '4/14/2014' 'q1' when cast(referral_dt date) between '4/15/2014' , '7/14/2014' 'q2' when cast(referral_dt date) between '7/15/2014' , '9/30/2014' 'q3' when cast(referral_dt date) between '10/1/2014' , '12/31/2014' 'q4' else 'x' end when run full query above, runs 12 hours. when run query 1 month, runs in 8 minutes. want run query each month , append 1 file. should make query run in 2-3 hours.
i can use union , copy code 24 times doesn't seem best way it. there more programattic way this?
update: want able run query every day update latest month's numbers.
by looks of execution plan posted think have missing join predicate , generating ton of intermediate rows:
here's same query cleaned (ctes instead of correlated subqueries , replaced case statement datepart(quarter)), might make easier tell missing predicate is:
with m (select mcc, mcc_desc, mcc_industry kaiserver.[dbkai].[dbo].[merchantcategorycode]), mp (select p.merchant_id, cast(p.proposal_create_dt date) proposal_date, m.mcc_desc, m.mcc_industry kaiserver.[dbkai].[dbo].[proposals] p left join m on p.mcc = m.mcc datepart(yyyy,proposal_create_dt) in ('2013', '2014')), ac (select account_no, cast(account_activate_dt date) acct_act_date, signed_annual_volume, average_tkt kaiserver.[dbkai].[dbo].[account] current_ind=1 , datepart(yyyy,account_submit_dt) in ('2013', '2014')), sr (select e1.repid, e1.repcode, e1.payrollname, e1.salesmanager, e2.payrollname manager_name, e1.region,e1.market, e1.saleschannel [fdserver].fdms.[dbo].[tbl_reps] e1 left join [fdserver].fdms.[dbo].[tbl_reps] e2 on e1.salesmanager = e2.repid e1.market not ('%test%') , e1.payrollname not null , e1.region not null , e1.market not null), bk (select [au_name], [au_code], [superregion_name], [region_name] ,[division_name], [subdivision_name], [district_name], [subdistrict_name] kaiserver.[dbkai].[dbo].[bankau_hierarchy] [reporting_interval_id] = '201410'), sv (select merchantnumber, projected_profit kaiserver.[dbkai].[dbo].[soldvolumedetail]) select datepart(yyyy,referral_dt) refyear, datepart(mm,referral_dt) refmonth, datepart(quarter, referral_dt) refqtr, crm.salesstatuscode, crm.referral_state, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname, sr.region sales_region,sr.market sales_market, sr.saleschannel saleschannel, bk.superregion_name bank_superregion, bk.region_name bank_region ,bk.division_name bank_division, sum(case when crm.referral_state = 'won' 1 else 0 end) referrals_won, sum(sv.projected_profit) prj_profit, sum(case when mp.proposal_date null 0 else 1 end) proposals_created, sum(ac.signed_annual_volume) total_signed_volume, sum(case when ac.acct_act_date null 0 else 1 end) activated_accounts, count(*) referral_count moagg1 kaiserver.dbkai.dbo.referrals_crm crm left join mp on crm.merchant_id = mp.merchant_id left join ac on crm.account_no = ac.account_no left join sr on crm.sales_rep_cd = sr.repcode left join bk on crm.referral_au = ck.au_code left join sv on crm.account_no = sv.merchantnumber datepart(yyyy, referral_dt) in ('2013', '2014') , (crm.salesstatuscode <> 'dupl' or crm.salesstatuscode null) , crm.lead_source not in ('test lead', 'bank lead placeholder') group datepart(yyyy,referral_dt), datepart(mm,referral_dt), crm.referral_state, crm.salesstatuscode, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname, sr.region,sr.market, sr.saleschannel, bk.superregion_name, bk.region_name ,bk.division_name, datepart(quarter, referral_dt)
Comments
Post a Comment