如何优化下面的查询?

Question

我正在使用下面的查询在SSRS中创建一个报告，但是它需要大约10分钟才能给我结果。

我试图添加索引到视图，但似乎我没有这样做的权限。

是否有其他方法来优化查询?

(仅供参考，此查询将表和视图连接在一起。我不确定这是否导致速度变慢。

/* I'm creating the temp table here, because i think it would help run faster, but it does not */
SELECT
QM.*
INTO #QM
FROM ODS.dbo.QNXT_MEMBER QM
DROP TABLE IF EXISTS #CVG
SELECT
CVG.*
INTO #CVG
FROM JIVA_DWH.dbo.mbr_cvg CVG
DROP TABLE IF EXISTS #1;
SELECT G.ext_cvg_id MemberSourceId,
A.MBR_IDN,
I.ENC_IDN,
I.INTRACN_IDN,
A.ACTIVITY,
A.ACTIVITY_TYPE,
A.UPDATED_DATE,
A.ACTIVITY_STATUS,
A.SCHEDULED_DATE,
I.INTERACTION_DATE,
I.INTERACTION_OUTCOME,
I.INTERACTION_STATUS,
I.MODIFIED_USER,
M.STATUS_CHANGE_DATE,
M.EPISODE_STATUS,
MP.ALTERNATE_ID,
[ROW_NUM] = ROW_NUMBER() OVER (PARTITION BY A.ENC_IDN ORDER BY I.INTERACTION_DATE DESC)
INTO #1
FROM JIVA_DWH.dbo.kv_V_MODEL_MBR_ENC_ACTIVITY A /*this is a view*/
JOIN JIVA_DWH.dbo.kv_V_MODEL_EPISODES M /*this is a view*/
ON M.ENC_IDN = A.ENC_IDN
JOIN JIVA_DWH.dbo.kv_V_MODEL_INTERACTIONS I /*this is a view*/
ON I.ENC_IDN = M.ENC_IDN
JOIN #CVG G /*this is a table*/
ON G.mbr_idn = A.MBR_IDN
LEFT JOIN #QM MP /*this is a table*/
ON G.ext_cvg_id = MP.MEMBER_SOURCE_ID COLLATE DATABASE_DEFAULT
WHERE A.ACTIVITY IN ( 'Verbal consent to be received', 'Incoming Call', 'Initial outreach Call', 'Contact Member' )
AND M.EPISODE_TYPE_CD = 'ECM'
AND I.INTERACTION_DATE
BETWEEN @StartDate AND @EndDate
AND CONVERT(DATE, [M].[EPISODE_START_DATE_UTC] + GETDATE() - GETUTCDATE())
BETWEEN @StartDate AND @EndDate;  /*I declear this variable on the top*/

我还尝试创建一个临时表并存储"jiva_dwh .dbo. kv_v_model_mbr_enc_activity"，但它需要6分钟才能加载。所以我非常怀疑这是因为视图本身。

我应该做些什么来优化查询?

Answer 1

当尝试优化查询时，我建议使用SQL Server Management Studio中的工具。

在实际数据库上运行查询时，激活"Include actual Execution plan"选项。

这样做有两个好处:

1. 查看查询的哪些方面使用了最多的时间/资源。这可以帮助您检查在哪里寻找优化潜力。
2. 该工具还为您提供了建议额外的数据库索引的选项，这可能会有很大帮助(特别是如果报告经常使用)

参考资料/演出表

请注意，我推荐的是一个过程，而不是这样一个过程的结果，因为结果也取决于表格中的数据量和其他难以在问答中发布的因素。

Answer 2

您正在使用的视图可能是为特定原因而设计的。那里可能比你需要的要多。您可以尝试阅读您的3个视图的定义，并只使用您需要的:

DECLARE @StartDate date
DECLARE @EndDate date
SET @StartDate = getdate()
SET @EndDate = DATEADD(year, 1, getdate())
DROP TABLE IF EXISTS #1;
WITH
my_kv_V_MODEL_MBR_ENC_ACTIVITY AS (
<simplified code from kv_V_MODEL_MBR_ENC_ACTIVITY view>
WHERE <tbl>.ACTIVITY IN ( 'Verbal consent to be received', 'Incoming Call', 'Initial outreach Call', 'Contact Member' )
),
my_kv_V_MODEL_EPISODES AS (
<simplified code from kv_V_MODEL_EPISODES view>
WHERE <tbl>.EPISODE_TYPE_CD = 'ECM'
AND CONVERT(DATE, [M].[EPISODE_START_DATE_UTC] + GETDATE() - GETUTCDATE())
BETWEEN @StartDate AND @EndDate
),
my_kv_V_MODEL_INTERACTIONS AS (
<simplified code from kv_V_MODEL_INTERACTIONS view>
WHERE <tbl>.INTERACTION_DATE
BETWEEN @StartDate AND @EndDate
)
SELECT G.ext_cvg_id MemberSourceId,
A.MBR_IDN,
I.ENC_IDN,
I.INTRACN_IDN,
A.ACTIVITY,
A.ACTIVITY_TYPE,
A.UPDATED_DATE,
A.ACTIVITY_STATUS,
A.SCHEDULED_DATE,
I.INTERACTION_DATE,
I.INTERACTION_OUTCOME,
I.INTERACTION_STATUS,
I.MODIFIED_USER,
M.STATUS_CHANGE_DATE,
M.EPISODE_STATUS,
MP.ALTERNATE_ID,
[ROW_NUM] = ROW_NUMBER() OVER (PARTITION BY A.ENC_IDN ORDER BY I.INTERACTION_DATE DESC)
INTO #1
FROM my_kv_V_MODEL_MBR_ENC_ACTIVITY A
JOIN my_kv_V_MODEL_EPISODES M ON M.ENC_IDN = A.ENC_IDN
JOIN my_kv_V_MODEL_INTERACTIONS I ON I.ENC_IDN = M.ENC_IDN
JOIN JIVA_DWH.dbo.mbr_cvg G ON G.mbr_idn = A.MBR_IDN
LEFT JOIN ODS.dbo.QNXT_MEMBER MP ON G.ext_cvg_id = MP.MEMBER_SOURCE_ID COLLATE DATABASE_DEFAULT

另外，在检查这3个视图时，您可能会发现它们具有一些共性。也许您要求数据库服务器多次执行不必要的相同步骤。最好避免使用视图，只使用源表编写查询。