如何优化SSRS中运行缓慢的多视图关联SQL查询？

问题背景 #

在SSRS中运行以下查询生成报表时耗时约10分钟，尝试创建临时表未提升性能，且发现视图JIVA_DWH.dbo.kv_V_MODEL_MBR_ENC_ACTIVITY加载就耗时6分钟，怀疑视图本身是性能瓶颈。无权限给视图添加索引，需要优化查询逻辑：

/* 原临时表创建逻辑，未起到加速作用 */
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 /* 视图 */
    JOIN JIVA_DWH.dbo.kv_V_MODEL_EPISODES M /* 视图 */
        ON M.ENC_IDN = A.ENC_IDN
    JOIN JIVA_DWH.dbo.kv_V_MODEL_INTERACTIONS I /* 视图 */
        ON I.ENC_IDN = M.ENC_IDN
    JOIN #CVG G /* 表 */
        ON G.mbr_idn = A.MBR_IDN
    LEFT JOIN #QM MP /* 表 */
        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;

优化建议 #

1. 拆解视图，直接基于底层表过滤 #

视图加载慢通常是因为返回全量数据，而你的查询只需要部分结果。先查看视图定义：

EXEC sp_helptext 'JIVA_DWH.dbo.kv_V_MODEL_MBR_ENC_ACTIVITY';
EXEC sp_helptext 'JIVA_DWH.dbo.kv_V_MODEL_EPISODES';
EXEC sp_helptext 'JIVA_DWH.dbo.kv_V_MODEL_INTERACTIONS';

获取视图的底层表和逻辑后，直接针对底层表编写查询并提前应用过滤条件，避免视图先拉取全量数据再过滤。

2. 删除无意义的临时表复制 #

当前将QM和CVG全表复制到临时表完全是冗余操作，会增加IO开销。直接关联原表即可，例如把JOIN #CVG G改为JOIN JIVA_DWH.dbo.mbr_cvg G，LEFT JOIN #QM MP改为LEFT JOIN ODS.dbo.QNXT_MEMBER MP。

3. 优化日期转换逻辑 #

原查询中对EPISODE_START_DATE_UTC的转换会导致无法利用索引，改为提前计算时区偏移，将查询参数转为UTC时间匹配：

-- 提前计算时区偏移
DECLARE @UTCOffset INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE());
DECLARE @StartDateUTC DATETIME = DATEADD(HOUR, -@UTCOffset, @StartDate);
DECLARE @EndDateUTC DATETIME = DATEADD(HOUR, -@UTCOffset, DATEADD(DAY, 1, @EndDate));

-- WHERE子句替换为
AND M.EPISODE_START_DATE_UTC BETWEEN @StartDateUTC AND @EndDateUTC;

4. 提前过滤数据，缩小关联范围 #

用CTE先过滤每个视图/表的数据集，再进行关联，减少中间数据量：

WITH FilteredA AS (
    SELECT MBR_IDN, ENC_IDN, ACTIVITY, ACTIVITY_TYPE, UPDATED_DATE, ACTIVITY_STATUS, SCHEDULED_DATE
    FROM JIVA_DWH.dbo.kv_V_MODEL_MBR_ENC_ACTIVITY
    WHERE ACTIVITY IN ( 'Verbal consent to be received', 'Incoming Call', 'Initial outreach Call', 'Contact Member' )
),
FilteredM AS (
    SELECT ENC_IDN, STATUS_CHANGE_DATE, EPISODE_STATUS, EPISODE_START_DATE_UTC
    FROM JIVA_DWH.dbo.kv_V_MODEL_EPISODES
    WHERE EPISODE_TYPE_CD = 'ECM'
),
FilteredI AS (
    SELECT ENC_IDN, INTRACN_IDN, INTERACTION_DATE, INTERACTION_OUTCOME, INTERACTION_STATUS, MODIFIED_USER
    FROM JIVA_DWH.dbo.kv_V_MODEL_INTERACTIONS
    WHERE 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 FilteredA A
JOIN FilteredM M ON M.ENC_IDN = A.ENC_IDN
JOIN FilteredI 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
WHERE M.EPISODE_START_DATE_UTC BETWEEN @StartDateUTC AND @EndDateUTC;

5. 避免SELECT *，只取需要的字段 #

不管是临时表还是视图查询，都不要用SELECT *，只选取实际需要的字段，减少数据传输和存储开销。

6. 检查执行计划定位瓶颈 #

在SSMS中按Ctrl+M开启执行计划，运行查询后查看：

• 是否存在表扫描/聚集索引扫描：说明底层表缺少合适索引（可向DBA提索引创建建议）
• 是否存在Key Lookup：可建议DBA创建覆盖索引
• 是否存在Hash Match：可能是关联的数据量过大，需进一步缩小数据集

7. 优化COLLATE逻辑 #

COLLATE DATABASE_DEFAULT可能导致索引失效，如果ext_cvg_id和MEMBER_SOURCE_ID排序规则不同，可尝试将其中一方转换为另一方的排序规则，或向DBA申请统一字段排序规则。

内容的提问来源于stack exchange，提问作者NewbeeLoveCoding