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:

query plan

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

Popular posts from this blog

python - mat is not a numerical tuple : openCV error -

-
i have write down code showing error ma not getting it: please help: showing mat not numerical tuple: import cv import cv2 capture = cv2.videocapture("j.3gp") while(1): _, frame1 = capture.read() grayimage1 = cv2.cvtcolor(frame1, cv2.color_bgr2gray) _, frame2 = capture.read() grayimage2 = cv2.cvtcolor(frame2, cv2.color_bgr2gray) differenceimage = cv2.absdiff(grayimage1,grayimage2) thresholdimage = cv2.threshold(differenceimage,25,255,cv2.thresh_binary) cv2.imshow("difference image", differenceimage) cv2.imshow("threshold image", thresholdimage) cv2.imshow("image", frame1) k = cv2.waitkey(30) & 0xff error arising : ----------------------------------------------------------------------------------------- traceback (most recent call last): file "desk.py", line 15, in <module> cv2.imshow("threshold image", thresholdimage) typeerror: mat not numerical tuple ...
Read more

c# - MSAA finds controls UI Automation doesn't -

-
i'm working on automating windows application. i'm using teststack white framework. i've hit problem. program has 'window' object cannot see inside of. white shows no controls inside of it. inspect.exe shows no controls inside of either when running in ui automation mode. if switch inspect msaa see controls inside fine. there anyway me use msaa c# handle on these controls? if can identify msaa functions need, can use p/invoke call them c#. here's example article doing msaa: http://www.codeproject.com/articles/38906/ui-automation-using-microsoft-active-accessibility also, pinvoke.net can used identify iaccessible (msaa) functions: http://www.pinvoke.net/search.aspx?search=iaccessible&namespace=[all] here's answer along lines: msaa com-based? finally, alternative p/invoke, might able use tlbimp.exe create wrapper assembly oleacc.dll, , access msaa functions through it. i'm not sure if works msaa, it's worth try. as e...
Read more

wordpress - .htaccess: RewriteRule: bad flag delimiters -

-
pulling hair out on one. have following .htaccess file custom redirects in addition wordpress's default settings. when tested on local wamp server worked fine after moving production i'm not getting rewriterule: bad flag delimiters error in server log , after while site goes down internal server error any appreciated. in advance! <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^rockford_weddings___welcome\.html$ /wedding/ [r=301,l] rewriterule ^blog/?$ /journal/ [r=301,l] rewriterule ^blog/(.*)$ /$1 [r=301,l] rewriterule ^portraitinvestment/?$ /portraitinvestment\.pdf [r=301,l] rewriterule ^weddinginvestment/?$ /weddinginvestment\.pdf [r=301,l] rewriterule ^holidaycards2014/?$ /holidaycards2014\.pdf [r=301,l] </ifmodule> # begin wordpress <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^index\.php$ - [l] rewritecond %{request_filename} !-f rewritecond %{request_filename} !-d rewriterule . /index.php [l] </...
Read more