我编写了一个涉及 INNER JOIN
, LEFT OUTER JOIN
, UNION
和 SUBQUERY
的SQL查询,这需要很长时间才能执行 . 我的SQL技能不是那么好,如果有人可以帮助我,我将如何优化以下查询以使其更快,我将不胜感激 .
SELECT [l].[ACCOUNT_ID]
,[l].[CONTACT_ID]
,[l].[JOB_ROLE]
,[l].[JOB_FUNCTION]
,[l].[STATE]
,[l].[COUNTRY]
,[acc].[EMPLOYEE_COUNT] AS [EmpSize]
,[r].[CALL_DATE]
, CONVERT(varchar(20), [r].[DURATION]) AS [COMMENTS]
,[r].[LOCATIONS]
FROM [dbo].[tbl_V_S_CONTACT] AS [l]
INNER JOIN [dbo].[CALL_DETAILS_RECORDS_CLEAN] AS [r] ON [l].[CONTACT_ID] = [r].[CON_PER_ID]
LEFT OUTER JOIN [dbo].[tbl_V_S_ACCOUNT] AS [acc] ON [l].[ACCOUNT_ID] = [acc].[ACCT_ID]
WHERE [l].[CONTACT_ID] IN (SELECT [b].[CON_PER_ID]
FROM [dbo].[S_SRC] AS [a]
INNER JOIN [dbo].[S_CAMP_CON] AS [b] ON [a].[ROW_ID] = [b].[SRC_ID]
WHERE [a].[STATUS_CD] = 'Launched'
AND [a].[PROG_END_DT] > CONVERT(varchar, GETDATE(), 101))
AND [l].[COUNTRY] IN ('United States', 'Canada')
AND [r].[CALL_DATE] >= '01-01-2017' AND [r].[CALL_DATE] <= '2018-03-20'
UNION
SELECT [l].[ACCOUNT_ID]
,[l].[CONTACT_ID]
,[l].[JOB_ROLE]
,[l].[JOB_FUNCTION]
,[l].[STATE]
,[l].[COUNTRY]
,[acc].[EMPLOYEE_COUNT] AS [EmpSize]
,[act].[TODO_PLAN_START_DT] AS [CALL_DATE]
,[act].[COMMENTS]
,'Philadelphia' AS [LOCATIONS]
FROM [dbo.tbl_V_S_CONTACT] AS [l]
INNER JOIN [SiebProd].[dbo].[S_EVT_ACT] AS [act] ON [l].[CONTACT_ID] = [act].[PR_CON_ID]
LEFT OUTER JOIN [dbo].[tbl_V_S_ACCOUNT] AS [acc] ON [l].[ACCOUNT_ID] = [acc].[ACCT_ID]
WHERE [l].[CONTACT_ID] IN (SELECT [b].[CON_PER_ID]
FROM [dbo].[S_SRC] AS [a]
INNER JOIN [dbo].[S_CAMP_CON] AS [b] ON [a].[ROW_ID] = [b].[SRC_ID]
WHERE [a].[STATUS_CD] = 'Launched'
AND [a].[PROG_END_DT] > CONVERT(varchar, GETDATE(), 101))
AND [l].[COUNTRY] IN ('United States', 'Canada')
AND [act].[CEM_PLAN_START_DT] <= CONVERT(varchar, GETDATE(), 101)
AND [act].[TODO_CD] = 'Call Disposition'
AND [act].[COMMENTS] IS NOT NULL
AND [act.REF_NUM] IS NOT NULL
AND [X_CALL_DISPOSITION] IS NOT NULL
AND [act].[X_DISPOSITION_FLG] = 'Y'
1 回答
有许多步骤可以改进您的查询 .
首先纠正一个问题,
我的代码未经测试,但肯定会提高性能 . 理解这个想法并修复错误(如果有的话)