SQL Server Performance Forum – Threads Archive

Query tuning recommendation

I have a query below, and I am looking towards tuning it. I have recommended developers do certain things like avoid bookmark lookup which is in the execution plan. I would you all to look at the query and recommed your thoughts on this pl. Query: SELECT AVY.I_CLM, ‘Activity’ AS ITM_TYP, (SELECT RTRIM(X_DSC_CD) FROM dbo.REF_CD WHERE C_TYP_CD = ‘C_TYP_DOC_PMY’ AND X_VAL_CD = REF_DOC_TYP_CMB.C_TYP_DOC_PMY ) AS X_PMY_CD, (SELECT RTRIM(X_DSC_CD) FROM dbo.REF_CD WHERE C_TYP_CD = ‘C_TYP_DOC_SEC’ AND X_VAL_CD = REF_DOC_TYP_CMB.C_TYP_DOC_SEC ) AS X_SEC_CD, convert(VARCHAR(10),AVY.D_NTF_AVY,101) AS REC_DTE, (SELECT RTRIM(X_DSC_CD) FROM dbo.REF_CD WHERE C_TYP_CD=’C_STA_ITM_WKL’ AND X_VAL_CD = AVY.C_STA_ITM_WKL) AS ITM_STA, AVY.X_SBJ AS ITM_STE, AVY.I_AVY AS DB_KEY, AVY_WKL_ITM.I_CSE_PDR, AVY_WKL_ITM.I_PDR_WKL, ” AS I_DOC, RTRIM(REF_DOC_TYP_CMB.C_TYP_DOC_PMY) AS C_TYP_DOC_PMY, RTRIM(REF_DOC_TYP_CMB.C_TYP_DOC_SEC) AS C_TYP_DOC_SEC FROMdbo.AVY LEFT JOIN dbo.AVY_WKL_ITM ON AVY.I_AVY = AVY_WKL_ITM.I_AVY JOIN dbo.REF_AVY_NAM_DOC_TYP ON AVY.I_TYP_DOC_NAM_AVY = REF_AVY_NAM_DOC_TYP.I_TYP_DOC_NAM_AVY JOIN dbo.REF_DOC_TYP_CMB ON REF_AVY_NAM_DOC_TYP.I_CMB_TYP_DOC = REF_DOC_TYP_CMB.I_CMB_TYP_DOC WHEREI_CLM = @I_CLM Execution Plan: SELECT AVY.I_CLM, ‘Activity’ AS ITM_TYP, (SELECT RTRIM(X_DSC_CD) FROM dbo.REF_CD WHERE C_TYP_CD = ‘C_TYP_DOC_PMY’ AND X_VAL_CD = REF_DOC_TYP_CMB.C_TYP_DOC_PMY ) AS X_PMY_CD, (SELECT RTRIM(X_DSC_CD) F |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1011]=[Expr1011], [Expr1015]=[Expr1015], [Expr1017]=Convert([AVY].[D_NTF_AVY]), [Expr1020]=[Expr1020], [Expr1023]=rtrim(Convert([REF_DOC_TYP_CMB].[C_TYP_DOC_PMY])), [Expr1024]=rtrim(Convert([REF_DOC_TYP_CMB].[C_TYP_DOC_SEC] |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC])) |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[C_STA_ITM_WKL])) | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[I_AVY]) WITH PREFETCH) | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[I_TYP_DOC_NAM_AVY])) | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[AVY])) | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[AVY].[IX_NU_AVY]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[I_CLM]=822534) ORDERED FORWARD) | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[REF_AVY_NAM_DOC_TYP].[PK_REF_AVY_NAM_DOC_TYP]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_AVY_NAM_DOC_TYP].[I_TYP_DOC_NAM_AVY]=[AVY].[I_TYP_DOC_NAM_AVY]) ORDERED FORWARD) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[AVY_WKL_ITM].[IX_FK_AVY_WKL_ITM]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY_WKL_ITM].[I_AVY]=[AVY].[I_AVY]) ORDERED FORWARD) | |–Hash Match(Cache, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[C_STA_ITM_WKL]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[AVY].[C_STA_ITM_WKL]=[AVY].[C_STA_ITM_WKL])) | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1020]=rtrim([REF_CD].[X_DSC_CD]))) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[REF_CD].[PK_REF_CD]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_CD].[C_TYP_CD]=’C_STA_ITM_WKL’ AND [REF_CD].[X_VAL_CD]=[AVY].[C_STA_ITM_WKL]) ORDERED FORWARD) |–Hash Match(Cache, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC]=[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC])) |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_DOC_TYP_CMB].[C_TYP_DOC_SEC])) |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_DOC_TYP_CMB].[C_TYP_DOC_PMY])) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[REF_DOC_TYP_CMB].[PK_REF_DOC_TYP_CMB]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_DOC_TYP_C MB].[I_CMB_TYP_DOC]=[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC]) ORDERED FORWARD) | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1011]=rtrim([REF_CD].[X_DSC_CD]))) | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[REF_CD].[PK_REF_CD]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_CD].[C_TYP_CD]=’C_TYP_DOC_PMY’ AND [REF_CD].[X_VAL_CD]=[REF_DOC_TYP_CMB].[C_TYP_DOC_PMY]) ORDERED FORWARD) |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1015]=rtrim([REF_CD].[X_DSC_CD]))) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[NAVILINKPFIX].[dbo].[REF_CD].[PK_REF_CD]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[REF_CD].[C_TYP_CD]=’C_TYP_DOC_SEC’ AND [REF_CD].[X_VAL_CD]=[REF_DOC_TYP_CMB].[C_TYP_DOC_SEC]) ORDERED FORWARD) Thanks in advance!

]]>