SQL Server Performance

Query tuning recommendation

Discussion in 'T-SQL Performance Tuning for Developers' started by msrviking, Jan 27, 2005.

  1. msrviking New Member

    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.<br /><br />Query:<br />SELECT AVY.I_CLM,<br /> 'Activity' AS ITM_TYP, <br /><br /> (SELECT RTRIM(X_DSC_CD)<br /> FROM dbo.REF_CD <br />WHERE C_TYP_CD = 'C_TYP_DOC_PMY' <br /> AND X_VAL_CD = REF_DOC_TYP_CMB.C_TYP_DOC_PMY ) AS X_PMY_CD, <br /><br /> (SELECT RTRIM(X_DSC_CD)<br />FROM dbo.REF_CD <br />WHERE C_TYP_CD = 'C_TYP_DOC_SEC' <br /> AND X_VAL_CD = REF_DOC_TYP_CMB.C_TYP_DOC_SEC ) AS X_SEC_CD, <br /><br />convert(VARCHAR(10),AVY.D_NTF_AVY,101) AS REC_DTE, <br /><br />(SELECT RTRIM(X_DSC_CD) <br /> FROM dbo.REF_CD<br /> WHERE C_TYP_CD='C_STA_ITM_WKL' <br /> AND X_VAL_CD = AVY.C_STA_ITM_WKL) AS ITM_STA, <br /><br />AVY.X_SBJ AS ITM_STE, <br />AVY.I_AVY AS DB_KEY, <br />AVY_WKL_ITM.I_CSE_PDR, <br />AVY_WKL_ITM.I_PDR_WKL, <br />'' AS I_DOC,<br /><br />RTRIM(REF_DOC_TYP_CMB.C_TYP_DOC_PMY) AS C_TYP_DOC_PMY,<br />RTRIM(REF_DOC_TYP_CMB.C_TYP_DOC_SEC) AS C_TYP_DOC_SEC<br /><br />FROMdbo.AVY<br /><br />LEFT JOIN dbo.AVY_WKL_ITM <br />ON AVY.I_AVY = AVY_WKL_ITM.I_AVY<br /><br />JOIN dbo.REF_AVY_NAM_DOC_TYP <br />ON AVY.I_TYP_DOC_NAM_AVY = REF_AVY_NAM_DOC_TYP.I_TYP_DOC_NAM_AVY<br /><br />JOIN dbo.REF_DOC_TYP_CMB <br />ON REF_AVY_NAM_DOC_TYP.I_CMB_TYP_DOC = REF_DOC_TYP_CMB.I_CMB_TYP_DOC<br /><br /><br />WHEREI_CLM = @I_CLM<br /><br /><br />Execution Plan:<br />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<br /> |--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]<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[AVY].[C_STA_ITM_WKL]))<br /> | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[AVY].[I_AVY]) WITH PREFETCH)<br /> | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[AVY].[I_TYP_DOC_NAM_AVY]))<br /> | | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[NAVILINKPFIX].[dbo].[AVY]))<br /> | | | | |--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)<br /> | | | |--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)<br /> | | |--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)<br /> | |--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]))<br /> | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1020]=rtrim([REF_CD].[X_DSC_CD])))<br /> | |--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)<br /> |--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]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[REF_DOC_TYP_CMB].[C_TYP_DOC_SEC]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[REF_DOC_TYP_CMB].[C_TYP_DOC_PMY]))<br /> | |--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_CMB].[I_CMB_TYP_DOC]=[REF_AVY_NAM_DOC_TYP].[I_CMB_TYP_DOC]) ORDERED FORWARD)<br /> | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1011]=rtrim([REF_CD].[X_DSC_CD])))<br /> | |--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)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1015]=rtrim([REF_CD].[X_DSC_CD])))<br /> |--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)<br /><br /><br />Thanks in advance!

Share This Page