优化SQL Server 2012查询
我有一个查询运行了12个小时。优化SQL Server 2012查询
查询并留下联接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小时。但是当我运行查询1个月时,它运行8分钟。所以我想运行每个月的查询并追加到一个文件中。这应该使这个查询在2-3小时内运行。
我可以使用union
并复制代码24次,但这似乎并不是最好的方式。有没有更好的方法来做到这一点?
更新:我希望能够每天运行此查询来更新最新月份的数字。
通过你贴我想你已经丢失的连接谓词,并产生一吨的中间行的执行计划的外观:
这里稍微清理了同样的查询( CTE而不是相关的子查询,并用DATEPART(QUARTER)取代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)
谢谢,我应该找什么? – Moosa 2014-12-05 19:53:45
@Moosa任何加入其中条件可以选择更多的记录比预期的,如:crm.referrals_au = ck.au_code采用121M和30K和生产360B - 如果这是高可能是与匹配au_code几个不同的记录被交加入。要消除它们,您需要为连接提供其他条件。 – gordy 2014-12-05 20:06:43
对于上下文,AU是商店。在您给出的AU示例中,crm表在AU级别具有AU的销售,AU表具有AU名称。因此crm具有AU代码多重时间,并且联接是交叉联接。你是这个意思吗?即如果一个AU在CRM中有100条记录,那么它会进行100次连接,对吧?那导致了糟糕的表现?如果是这样,我将如何重写该连接? – Moosa 2014-12-05 20:28:20
我认为这会产生相同的输出,它应该会更快。此外,您需要确保用于JOIN或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
感谢但分裂了e1和e2连接不起作用,因为这是一个员工表,我需要进行自我连接以获取每个员工的经理姓名。我需要鼓捣这个来测试......由于连接的分裂,在select和group by子句中还有别名命名错误。 – Moosa 2014-12-05 19:31:47
@莫萨基于原始的SQL,我没有看到员工的自我加入。对不起,如果我错过任何别名,我想我得到了他们所有的重构。 – UnhandledExcepSean 2014-12-05 20:06:29
请发布您的执行计划。 – 2014-12-05 18:26:10
我可以在每个月的月份= 1和年份= 2013年工会全部选择....月份= 2和年份= 2013年工会所有...'等。那是你执行计划的意思吗? – Moosa 2014-12-05 18:32:12
不,不是。谷歌“SQL Server执行计划” – 2014-12-05 18:34:49