优化Server 2012查询

Question

我有一个12小时的查询。

该查询确实在5个表上留下了联接，并根据一组月度指标进行了报告。以下是查询：

SELECT DATEPART(YYYY,Referral_dt) AS RefYear, DATEPART(MM,Referral_dt) AS RefMonth, 
        CASE 
            WHEN CAST(Referral_dt as date) BETWEEN '1/1/2013' AND '4/14/2013' THEN 'Q1' 
            WHEN CAST(Referral_dt as date) BETWEEN '4/15/2013' AND '7/14/2013' THEN 'Q2'
            WHEN CAST(Referral_dt as date) BETWEEN '7/15/2013' AND '9/30/2013' THEN 'Q3' 
            WHEN CAST(Referral_dt as date) BETWEEN '10/1/2013' AND '12/31/2013' THEN 'Q4'
            WHEN CAST(Referral_dt as date) BETWEEN '1/1/2014' AND '4/14/2014' THEN 'Q1' 
            WHEN CAST(Referral_dt as date) BETWEEN '4/15/2014' AND '7/14/2014' THEN 'Q2'
            WHEN CAST(Referral_dt as date) BETWEEN '7/15/2014' AND '9/30/2014' THEN 'Q3' 
            WHEN CAST(Referral_dt as date) BETWEEN '10/1/2014' AND '12/31/2014' THEN 'Q4'
        ELSE 'X' END as RefQtr, 
        crm.salesstatuscode, crm.Referral_State, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname, 
        sr.region as Sales_Region,sr.market as Sales_Market, sr.saleschannel as SalesChannel, Bk.SuperRegion_Name as Bank_SuperRegion, 
        Bk.Region_Name as Bank_Region ,Bk.Division_Name as Bank_Division,
        sum(case when crm.referral_state = 'Won' then 1 else 0 end) as referrals_won, sum(sv.projected_profit) as prj_profit,
        sum(case when mp.proposal_Date is null then 0 else 1 end) as proposals_created, sum(ac.signed_annual_volume) as total_signed_volume,  
        sum(case when ac.Acct_Act_Date is null then 0 else 1 end) as activated_accounts, COUNT(*)  as referral_count
        into moagg1
FROM kaiserver.dbKAI.dbo.Referrals_CRM CRM 
     LEFT JOIN (SELECT p.merchant_id, CAST(p.proposal_create_dt as date) as Proposal_Date, m.mcc_desc, m.mcc_industry 
                from kaiserver.[dbKAI].[dbo].[proposals] p 
                left join (SELECT mcc, mcc_desc, mcc_industry from kaiserver.[dbKAI].[dbo].[merchantcategorycode]) m
                 on p.mcc = m.mcc where 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 as date) as Acct_Act_Date, signed_annual_volume, average_tkt
                from kaiserver.[dbKAI].[dbo].[Account]
                where current_ind=1 and 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 as manager_name,
            e1.region,e1.market, e1.saleschannel
             FROM   [fdserver].fdms.[dbo].[tbl_reps] e1 LEFT JOIN [fdserver].fdms.[dbo].[tbl_reps] e2 
                    ON e1.salesmanager = e2.repid
             WHERE e1.market not like ('%TEST%') and e1.payrollname is not null and e1.region is not null and e1.market is 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]
             FROM kaiserver.[dbKAI].[dbo].[BankAU_Hierarchy]
             WHERE [Reporting_Interval_Id] = '201410') BK 
    ON CRM.referral_au = BK.AU_code
    LEFT JOIN (select merchantnumber, projected_profit from kaiserver.[dbKAI].[dbo].[SoldVolumeDetail]) sv
    ON crm.account_no = sv.merchantnumber
    WHERE DATEPART(YYYY, Referral_dt) in ('2013', '2014')
          AND (crm.salesstatuscode <> 'DUPL' or crm.salesstatuscode is null) 
          AND crm.lead_source not in ('Test Lead', 'Bank Lead Placeholder')
    group by 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 as date) BETWEEN '1/1/2013' AND '4/14/2013' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2013' AND '7/14/2013' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2013' AND '9/30/2013' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2013' AND '12/31/2013' THEN 'Q4'
        WHEN CAST(Referral_dt as date) BETWEEN '1/1/2014' AND '4/14/2014' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2014' AND '7/14/2014' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2014' AND '9/30/2014' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2014' AND '12/31/2014' THEN 'Q4'
    ELSE 'X' END

当我像上面一样运行完整的查询时，它会运行12个小时。但是，当我运行一个月的查询时，它将在8分钟内运行。因此，我希望每个月运行查询，并将其附加到一个文件中。这将使这个查询在2-3个小时内运行。

我可以使用union并24次复制代码，但这似乎不是最好的方法。有什么更先进的方法吗？

更新:我希望能够每天运行这个查询来更新最近一个月的数据。

Answer 1

根据您发布的执行计划的外观，我认为您缺少一个连接谓词，并且正在生成大量中间行：

​

​

下面这个查询稍微清理了一下(CTE而不是相关的子查询，用DATEPART(季度)替换了CASE语句)，它可能使判断丢失谓词的位置变得更容易：

WITH
m as (SELECT mcc, mcc_desc, mcc_industry from kaiserver.[dbKAI].[dbo].[merchantcategorycode]),
mp as (SELECT p.merchant_id, CAST(p.proposal_create_dt as date) as Proposal_Date, m.mcc_desc, m.mcc_industry 
    from kaiserver.[dbKAI].[dbo].[proposals] p 
    left join m on p.mcc = m.mcc where datepart(yyyy,proposal_create_dt) in ('2013', '2014')),
ac as (SELECT account_no, CAST(Account_Activate_dt as date) as Acct_Act_Date, signed_annual_volume, average_tkt
    from kaiserver.[dbKAI].[dbo].[Account]
    where current_ind=1 and datepart(yyyy,account_submit_dt) in ('2013', '2014')),
sr as (SELECT e1.repid, e1.repcode, e1.payrollname, e1.salesmanager, e2.payrollname as manager_name, e1.region,e1.market, e1.saleschannel
    FROM [fdserver].fdms.[dbo].[tbl_reps] e1
    LEFT JOIN [fdserver].fdms.[dbo].[tbl_reps] e2 ON e1.salesmanager = e2.repid
    WHERE e1.market not like ('%TEST%') and e1.payrollname is not null and e1.region is not null and e1.market is not null),
bk as (SELECT [AU_name], [AU_Code], [SuperRegion_Name], [Region_Name] ,[Division_Name], [SubDivision_Name], [District_Name], [SubDistrict_Name]
    FROM kaiserver.[dbKAI].[dbo].[BankAU_Hierarchy]
    WHERE [Reporting_Interval_Id] = '201410'),
sv as (select merchantnumber, projected_profit from kaiserver.[dbKAI].[dbo].[SoldVolumeDetail])

SELECT DATEPART(YYYY,Referral_dt) AS RefYear, DATEPART(MM,Referral_dt) AS RefMonth, DATEPART(QUARTER, Referral_dt) as RefQtr,
    crm.salesstatuscode, crm.Referral_State, crm.lead_source, mp.mcc_desc, mp.mcc_industry, sr.manager_name, sr.payrollname,
    sr.region as Sales_Region,sr.market as Sales_Market, sr.saleschannel as SalesChannel, Bk.SuperRegion_Name as Bank_SuperRegion,
    Bk.Region_Name as Bank_Region ,Bk.Division_Name as Bank_Division,
    sum(case when crm.referral_state = 'Won' then 1 else 0 end) as referrals_won, sum(sv.projected_profit) as prj_profit,
    sum(case when mp.proposal_Date is null then 0 else 1 end) as proposals_created, sum(ac.signed_annual_volume) as total_signed_volume,  
    sum(case when ac.Acct_Act_Date is null then 0 else 1 end) as activated_accounts, COUNT(*) as referral_count INTO moagg1
FROM 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

WHERE DATEPART(YYYY, Referral_dt) in ('2013', '2014')
    AND (crm.salesstatuscode <> 'DUPL' or crm.salesstatuscode is null) 
    AND crm.lead_source not in ('Test Lead', 'Bank Lead Placeholder')

group by 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)

Answer 2

我认为这会产生相同的输出，而且应该更快。此外，您需要确保每个用于联接或WHERE子句中的字段都被索引。

SELECT
    DATEPART(YYYY,Referral_dt) AS RefYear, DATEPART(MM,Referral_dt) AS RefMonth, 
    CASE 
        WHEN CAST(Referral_dt as date) BETWEEN '1/1/2013' AND '4/14/2013' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2013' AND '7/14/2013' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2013' AND '9/30/2013' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2013' AND '12/31/2013' THEN 'Q4'
        WHEN CAST(Referral_dt as date) BETWEEN '1/1/2014' AND '4/14/2014' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2014' AND '7/14/2014' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2014' AND '9/30/2014' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2014' AND '12/31/2014' THEN 'Q4'
    ELSE 'X' END as RefQtr, 
    crm.salesstatuscode, crm.Referral_State, crm.lead_source, m.mcc_desc, m.mcc_industry, e2.manager_name, e1.payrollname, 
    e1.region as Sales_Region,e1.market as Sales_Market, e1.saleschannel as SalesChannel, Bk.SuperRegion_Name as Bank_SuperRegion, 
    Bk.Region_Name as Bank_Region ,Bk.Division_Name as Bank_Division,
    sum(case when crm.referral_state = 'Won' then 1 else 0 end) as referrals_won, sum(sv.projected_profit) as prj_profit,
    sum(case when p.proposal_Date is null then 0 else 1 end) as proposals_created, sum(ac.signed_annual_volume) as total_signed_volume,  
    sum(case when ac.Acct_Act_Date is null then 0 else 1 end) as activated_accounts, COUNT(*)  as referral_count
into moagg1
FROM kaiserver.dbKAI.dbo.Referrals_CRM CRM 
LEFT kaiserver.[dbKAI].[dbo].[proposals] p  ON crm.merchant_id = p.merchant_id
    AND datepart(yyyy,p.proposal_create_dt) in ('2013', '2014')
left join kaiserver.[dbKAI].[dbo].[merchantcategorycode] m on p.mcc = m.mcc
LEFT JOIN kaiserver.[dbKAI].[dbo].[Account] ac ON crm.account_no = ac.account_no
    AND ac.current_ind=1
    AND and datepart(yyyy,ac.account_submit_dt) in ('2013', '2014')
LEFT JOIN [fdserver].fdms.[dbo].[tbl_reps] e1 ON e1.repcode=CRM.Sales_Rep_Cd
    AND e1.market not like ('%TEST%')
    and e1.payrollname is not null
    and e1.region is not null
    and e1.market is not null
LEFT JOIN [fdserver].fdms.[dbo].[tbl_reps] e2 ON e1.salesmanager = e2.repid
LEFT JOIN kaiserver.[dbKAI].[dbo].[BankAU_Hierarchy]  BK ON CRM.referral_au = BK.AU_code
    AND [Reporting_Interval_Id] = '201410'
LEFT JOIN kaiserver.[dbKAI].[dbo].[SoldVolumeDetail] sv ON crm.account_no = sv.merchantnumber
WHERE DATEPART(YYYY, CRM.Referral_dt) in ('2013', '2014')
AND ISNULL(crm.salesstatuscode,'') <> 'DUPL'
AND crm.lead_source not in ('Test Lead', 'Bank Lead Placeholder')
group by 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 as date) BETWEEN '1/1/2013' AND '4/14/2013' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2013' AND '7/14/2013' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2013' AND '9/30/2013' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2013' AND '12/31/2013' THEN 'Q4'
        WHEN CAST(Referral_dt as date) BETWEEN '1/1/2014' AND '4/14/2014' THEN 'Q1' 
        WHEN CAST(Referral_dt as date) BETWEEN '4/15/2014' AND '7/14/2014' THEN 'Q2'
        WHEN CAST(Referral_dt as date) BETWEEN '7/15/2014' AND '9/30/2014' THEN 'Q3' 
        WHEN CAST(Referral_dt as date) BETWEEN '10/1/2014' AND '12/31/2014' THEN 'Q4'
    ELSE 'X' END