none
How can I use Indexed Temp tables to optimize performance?

    Question

  • Instead of joining two CTEs together, I am now going to attempt to join two indexed temp tables. 

    The first temp table is a number of encounters that returns 147 rows in 2 seconds. The second temp table is progress notes for all those encounters returning 136 rows in 18 seconds. Joining the indexed views comes back at 3 1/2 minutes.

    What can I do to optimize performance?

    Code is below. Thanks in advance!

    if object_id('tempdb..#arpb') is not null begin drop table #arpb end;
    if object_id('tempdb..#progress_notes') is not null begin drop table #progress_notes end;
    
    
    SELECT DISTINCT
    ARPB.PAT_ENC_CSN_ID, ARPB.SERVICE_DATE, ARPB.BILLING_PROV_ID,  ARPB.DEPARTMENT_ID,
    SER.PROV_NAME, DEP.DEPARTMENT_NAME,  E.APPT_TIME,  ZC_APPT.NAME AS APPT_STATUS
    INTO #arpb
    FROM ARPB_TRANSACTIONS ARPB 
    LEFT OUTER JOIN CLARITY_SER AS SER ON SER.PROV_ID = ARPB.BILLING_PROV_ID
    LEFT OUTER JOIN CLARITY_DEP AS DEP ON DEP.DEPARTMENT_ID = ARPB.DEPARTMENT_ID
    LEFT OUTER JOIN PAT_ENC AS E ON E.PAT_ENC_CSN_ID = ARPB.PAT_ENC_CSN_ID
    LEFT OUTER JOIN ZC_APPT_STATUS AS ZC_APPT ON ZC_APPT.APPT_STATUS_C  = E.APPT_STATUS_C
    WHERE ARPB.DEPARTMENT_ID = xxxx
    AND ARPB.TX_TYPE_C = 1
    AND ARPB.VOID_DATE IS NULL
    AND ARPB.BILLING_PROV_ID = xxxx
    AND ARPB.SERVICE_DATE BETWEEN
    'xxxx' AND 'xxxxx'
    
    create clustered index idx_temp_arpb on #arpb(PAT_ENC_CSN_ID)
    
    SELECT DISTINCT
    ARPB.PAT_ENC_CSN_ID,
    ZCNT.NAME AS NOTE_TYPE, PR.NAME AS PURPOSE, HNO_INFO.NOTE_ID,
    EMP.NAME AS EMP_NAME, STS.NAME AS NOTE_STATUS
    INTO #progress_notes
    FROM ARPB_TRANSACTIONS ARPB 
    LEFT OUTER JOIN ENC_NOTE_INFO AS ENC_NOTE_INFO ON ARPB.PAT_ENC_CSN_ID = ENC_NOTE_INFO.PAT_ENC_CSN_ID 
    LEFT OUTER JOIN HNO_INFO AS HNO_INFO ON ENC_NOTE_INFO.ENCOUNTER_NOTE_ID = HNO_INFO.NOTE_ID
    LEFT OUTER JOIN ZC_NOTE_TYPE AS ZCNT ON ZCNT.NOTE_TYPE_C = ENC_NOTE_INFO.NOTE_TYPE_C
    LEFT OUTER JOIN ZC_NOTE_PURPOSE AS PR ON PR.NOTE_PURPOSE_C = HNO_INFO.NOTE_PURPOSE_C
    LEFT OUTER JOIN CLARITY_EMP AS EMP ON EMP.EPIC_EMP_ID = HNO_INFO.CURRENT_AUTHOR_ID
    LEFT OUTER JOIN ZC_NOTE_STATUS AS STS ON STS.NOTE_STATUS_C = ENC_NOTE_INFO.NOTE_STATUS_C
    WHERE ARPB.DEPARTMENT_ID = xxxx
    AND ARPB.TX_TYPE_C = 1
    AND ARPB.VOID_DATE IS NULL
    AND ARPB.BILLING_PROV_ID = xxxx
    AND ZCNT.NAME = 'xxxx'
    AND ARPB.SERVICE_DATE BETWEEN
    'xxxx' AND 'xxxx'
    AND PR.NAME = 'xxxxx'
    
    create index idx_temp_pn on #progress_notes(PAT_ENC_CSN_ID)
    
    SELECT 
    #arpb.PAT_ENC_CSN_ID,
    #arpb.APPT_TIME,
    #arpb.SERVICE_DATE,
    #arpb.BILLING_PROV_ID,
    #arpb.PROV_NAME,
    #arpb.DEPARTMENT_ID,
    #arpb.DEPARTMENT_NAME,
    #progress_notes.EMP_NAME as NoteEmp,
     CASE #progress_notes.NOTE_ID
             WHEN null THEN 'No Progress Note'
             ELSE #progress_notes.NOTE_ID
          END as NoteId,
    #progress_notes.NOTE_STATUS,
    #progress_notes.NOTE_TYPE,
    #progress_notes.PURPOSE
    
    FROM #ARPB 
    LEFT  JOIN #PROGRESS_NOTES ON #ARPB.PAT_ENC_CSN_ID = #PROGRESS_NOTES.PAT_ENC_CSN_ID


    To err is human, to REALLY foul things up requires a computer

    Thursday, April 10, 2014 9:11 PM

All replies

  • Something is not right here. What is the type of that column in both temp tables?

    I would assume that joining 147 and 136 rows should be done in less than 1 sec. even without indexes.

    How did you measure the time?

    BTW, I suggest to use aliases in your last query - it will be easier to read. Also use COALESCE function instead of CASE for the Note_ID (BTW, is Note_ID a character column - otherwise you supposed to get an error).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 9:36 PM
  • It may not  be the CTE or un indexed temp table causing the performance issues , a missing JOIN column as well.

    Is PR.NAME and ZCNT.NAME columns are expected to have same value?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 11, 2014 12:51 AM