SQL Server Performance

performance help

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by habesha, Nov 7, 2006.

  1. habesha New Member

    the showPlan_text of my procedure gives a result of<br /><br /><br /><br /> | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS] AS [SEO]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SVE].[SVE_ID]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[STU].[STU_NUMBER], [STU].[STU_ID]) WITH PREFETCH)<br /> | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDIES].[STU_ID_PK] AS [STU]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[STU].[STU_RECORD_STATUS_FLAG]='Active'))<br /> | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'COMPONENTPRODUCT'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_COMPONENT_PRO_ID_LIST_Vch]), NULL) AND like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'COMBINATIONPRODUCT'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_COMBINATION_PRO_ID_LIST_Vch]), NULL)) AND like([VOYAGER].[VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN](Convert([SVE].[SVE_ID]), 'CLASS'), [VOYAGER].[VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN]([@p_In_TCL_ID_List_Vch]), NULL)))<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK] AS [SVE]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SVE].[SVE_ID]=[Voyager].[VOYAGER_AP_GET_LATESTSTUDYVERSIONID_GIVEN_STUDY_NUMBER_FUN](Convert([STU].[STU_NUMBER]))), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />([STU].[STU_ID]=[SVE].[SVE_STU_ID] AND [SVE].[SVE_RECORD_STATUS_FLAG]='Active') AND [SVE].[SVE_DISPLAY_FOR_ALL_FLAG]=isnull([@p_In_DISPLAY_FLAG_Vch], Convert([SVE].[SVE_DISPLAY_FOR_ALL_FLAG]))) ORDERED FORWARD)<br /> | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS].[SEO_SVE_ID] AS [SEO]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SEO].[SEO_SVE_ID]=[SVE].[SVE_ID]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[MILESTONES].[MIL_ID_PK] AS [MIL]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[MIL].[MIL_ID]=[STU].[STU_CURRENT_MILESTONE_MIL_ID]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[MIL].[MIL_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)<br /><br /><br /><br />any one can suggest what to do with it to tune my proc, it is taking over a minute to run. I want to bring it down to &lt; 10 sec
  2. Roji. P. Thomas New Member

  3. satya Moderator

  4. habesha New Member

    ALTER PROCEDURE [Voyager].[VOYAGER_AP_STUDIES_BYPRODUCTS_DS_PRO]
    (
    @p_In_APP_ID_Int INT
    ,@p_In_USER_ID_Int INT
    ,@p_In_COMPONENT_PRO_ID_LIST_Vch VARCHAR(4000) = NULL
    ,@p_In_COMBINATION_PRO_ID_LIST_Vch VARCHAR(4000) = NULL
    ,@p_In_TCL_ID_List_Vch VARCHAR(4000) = NULL
    )
    AS
    BEGIN

    DECLARE @p_In_DISPLAY_FLAG_Vch VARCHAR(10)

    IF APPARCH.APPARCH_AP_GET_ROLEEXISTSGIVENUSERID_FUN(@p_In_USER_ID_Int) = 'No'
    BEGIN
    SET @p_In_DISPLAY_FLAG_Vch = 'Yes'
    END

    SET NOCOUNT ON

    SELECT DISTINCT
    DOCUMENTID = CASE
    WHEN STU.STU_LEGACY_PROTOCOL_NUMBER_TEXT IS NULL
    THEN CAST(STU.STU_NUMBER AS VARCHAR(15))
    ELSE CAST(STU.STU_NUMBER AS VARCHAR(22)) + ' (' + STU.STU_LEGACY_PROTOCOL_NUMBER_TEXT + ')'
    END
    ,STU.STU_ID
    ,SVE.SVE_ID
    ,STU.STU_NUMBER
    ,SVE.SVE_VERSION_NUMBER
    --,VOYAGER.VOYAGER_AP_GET_STUDY_LOA_GIVEN_STU_ID_FUN(STU.STU_ID) AS LOA_LIST,
    ,VOYAGER.VOYAGER_AP_GET_STUDY_VERSION_FUN(STU.STU_ID,SVE.SVE_ID,SVE.SVE_VERSION_NUMBER) AS SVE_VERSION_DATE
    ,SVE.SVE_TITLE_TEXT
    ,STU.STU_CURRENT_STUDY_STATUS_TYPE
    ,STU.STU_CURRENT_STUDY_STATUS_DATE
    ,SVE.SVE_SHORT_TITLE_TEXT
    ,SVE.SVE_TARGET_ACCRUAL_NUMBER
    ,SVE.SVE_PHASE_TYPE
    ,MIL.MIL_MILESTONE_NAME AS CURRENT_MILESTONE
    ,STU.STU_CURRENT_MILESTONE_DATE AS CURRENT_MILESTONE_DATE
    ,VOYAGER.VOYAGER_AP_GET_CURRENT_ACCRUAL_GIVEN_STU_ID_FUN(STU.STU_ID,STU_ACCRUAL_DATA_COLLECTION_LEVEL_TYPE) AS CURRENT_ACCRUAL
    ,CONVERT(VARCHAR(10),VOYAGER.VOYAGER_AP_GET_MIN_DATE_GIVEN_STU_ID_FUN(STU.STU_ID),101) AS PARTICIPANT_ENROLLMENT_DATE
    ,VOYAGER.VOYAGER_AP_GET_ACCRUAL_DATE_FUN(stu.STU_NUMBER) AS ACCRUAL_AS_OF_DATE
    ,VOYAGER.VOYAGER_AP_GET_STUDY_TEAM_FUN(STU.STU_ID) AS STUDY_TEAM
    ,VOYAGER.VOYAGER_AP_GET_STUDY_PARTICIPATING_SITES_FUN(STU.STU_ID) AS REGISTERED_PARTICIPATING_SITES
    ,Voyager.VOYAGER_AP_GET_IND_NUMBERS_GIVEN_STU_ID_FUN(STU.STU_ID) AS IND_NUMBER
    ,Voyager.VOYAGER_AP_GET_ORGANIZATIONS_GIVEN_STU_ID_FUN(STU.STU_ID, STU.STU_NETWORK_FLAG) AS NETWORK
    ,Voyager.VOYAGER_AP_GET_STUDY_TEAM_CHAIRS_FUN(STU.STU_ID) AS [STUDY CHAIR]
    ,VOYAGER.VOYAGER_AP_GET_STUDY_TEAM_MEMBERS_BY_ROLE_FUN(STU.STU_ID, 'Medical Officer, Medical Monitor, Clinical Trials Representative') AS [MEDICAL OFFICER]
    ,Voyager.VOYAGER_AP_GET_PERSON_NAME_FUN(STU.STU_ID, 'Program Officer') AS [Program Officer]
    ,Voyager.VOYAGER_AP_IS_REGISTERED_SITE_FUN(STU.STU_ID) AS [REGISTEREDSITE]
    ,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
    THEN VOYAGER.VOYAGER_AP_GET_PRODUCT_NAME_GIVEN_PRODUCT_ID_FUN(SEO.SEO_PRO_ID)
    ELSE VOYAGER.VOYAGER_AP_GET_THERAPYCLASS_NAME_GIVEN_THERAPYCLASS_ID_FUN(SEO.SEO_TCL_ID) END ) AS PRODUCTNAME
    ,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
    THEN VOYAGER.VOYAGER_AP_GET_PRODUCTMANUFACTURERS_FUN(SEO.SEO_ID) END) AS PRODUCTMANUFACTURERS
    ,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
    THEN VOYAGER.VOYAGER_AP_GET_CONTRACTMANUFACTURERS_FUN(SEO.SEO_ID) END) AS CONTRACTMANUFACTURERS
    ,(CASE SEO.SEO_PRODUCT_CLASS_FLAG WHEN 'Product'
    THEN VOYAGER.VOYAGER_AP_GET_PRODUCTSUPPLIERS_FUN(SEO.SEO_ID) END) AS PRODUCTSUPPLIERS
    ,SEO.SEO_STUDY_USE_TYPE
    ,Voyager.VOYAGER_AP_IS_REGISTERED_SITE_FUN(STU.STU_ID) AS [REGISTEREDSITE]
    FROM STUDIES STU
    LEFT OUTER JOIN MILESTONES MIL
    ON MIL.MIL_ID = STU.STU_CURRENT_MILESTONE_MIL_ID
    AND MIL.MIL_RECORD_STATUS_FLAG = 'Active'
    INNER JOIN STUDY_VERSIONS SVE
    ON STU.STU_ID = SVE.SVE_STU_ID
    INNER JOIN STUDY_VERSION_PRODUCTS SEO
    ON SEO.SEO_SVE_ID= SVE.SVE_ID
    WHERE (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'COMPONENTPRODUCT') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_COMPONENT_PRO_ID_LIST_Vch))
    AND (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'COMBINATIONPRODUCT') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_COMBINATION_PRO_ID_LIST_Vch))
    AND (VOYAGER.VOYAGER_AP_GET_PRODUCTIDS_FOR_STUDYVERSION_FUN(SVE.SVE_ID,'CLASS') LIKE VOYAGER.VOYAGER_AP_GET_LIKECLAUSESTRING_FOR_REPORT_FUN(@p_In_TCL_ID_List_Vch))
    AND SVE.SVE_DISPLAY_FOR_ALL_FLAG = ISNULL(@p_In_DISPLAY_FLAG_Vch, SVE_DISPLAY_FOR_ALL_FLAG)
    AND SVE.SVE_ID = Voyager.VOYAGER_AP_GET_LATESTSTUDYVERSIONID_GIVEN_STUDY_NUMBER_FUN(STU.STU_NUMBER)
    AND SVE.SVE_RECORD_STATUS_FLAG = 'Active'
    AND STU.STU_RECORD_STATUS_FLAG = 'Active'
    AND SEO.SEO_RECORD_STATUS_FLAG='Active'
    --ORDER BY NETWORK,STU_CURRENT_STUDY_STATUS_TYPE,DOCUMENTID,PRODUCTNAME

    SET NOCOUNT OFF
    END
  5. joechang New Member

    since this is SQL 2005, you should save the plan as XML (.sqlplan file extension),
    then open the plan file with a text editor, paste the contents into a dedicated reply, ie, no other text

    also, it is better to have the actual plan than the estimated plan, ie, run the query with plan
  6. habesha New Member

    I work on sql 2005 management studio.
    The database is sql 2000.
    I was tring to run show_xml on and execute it, it fails,
    I know show_xml is a new feature on sql 2005. can't I use it on sql 2000 database while connecting the sql 2000 database from sql server 2005 management studio?
  7. gurucb New Member

    How many rows this is returning <br /><br />|--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDIES].[STU_ID_PK] AS [STU]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[STU].[STU_RECORD_STATUS_FLAG]='Active'))<br /><br />Can we remove this bookmark lookup and add a composite / covered index<br />| |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[DaidsesDev1_01].[Voyager].[STUDY_VERSION_PRODUCTS] AS [SEO]))<br /><br />Are stats updated for the database

Share This Page