How do I optimize the following query?

Question

I’m using the following query to create a report in SSRS, but it’s taking about 10 minutes to give me the result.

I was trying to add index to the view, but it seems like I don’t have the permission to do so.

Is there another way to optimize the query?

(FYI, this query is joining table and view together. I’m not sure if this causing the slowness.)

/* 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*/

I also tried create a temporary table and store "JIVA_DWH.dbo.kv_V_MODEL_MBR_ENC_ACTIVITY", but it took 6 minutes to load. So I’m highly suspicious it’s because of the view itself.

What should I do to optimize the query?

Answer 1

The views you are using were probably designed for a specific reason. There may be more there than you need. You might try reading the definitions of your 3 views and using only what you need:

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

Also, upon inspecting the 3 views, you may discover that they share some commonality. Perhaps you are asking the database server to unnecessarily perform the same steps several times. It may be better to avoid using the views and write your query using only the source tables.