Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

performance problem

Status
Not open for further replies.

washaw

Programmer
Joined
Feb 13, 2008
Messages
48

I have a view, The view has 5 simple select statements joined by union all statement,

each select statement in the view runs under 1 sec except one which takes > 1 sec to run

but when this view is joined in procedure, the procedures tend to run very slow,

what could be the cause

Thanks
 
look at your execution plans. When they are in joins are indexes being used? Are you indexing the views or the tables?

"NOTHING is more important in a database than integrity." ESquared
 
here is the execution plan

|--Compute Scalar(DEFINE:([Expr1172]=If ([VOYAGER].[VOYAGER_AP_GET_STUDY_PRODUCTS_FLAG_FUN](Convert([A].[SVE_ID]), 'Study Agents')='Yes') then 'Yes' else [VOYAGER].[VOYAGER_AP_GET_STUDY_PRODUCTS_FLAG_FUN](Convert([A].[SVE_ID]), 'Study Vaccines')))
|--Sort(DISTINCT ORDER BY:([A].[SVE_ID] ASC, [A].[SVE_VERSION_NUMBER] ASC, [A].[SVE_TITLE_TEXT] ASC, [A].[SVE_SHORT_TITLE_TEXT] ASC, [Expr1119] ASC, [AE_REPORTING_MANUALS].[ARM_AE_MANUAL_NAME] ASC))
|--Filter(WHERE:(([ses].[SES_RECORD_STATUS_FLAG]='Active' AND [ses].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int])) AND [ses].[SES_CURRENT_REGISTRATION_STATUS_TYPE]<>'Disapproved'))
|--Bookmark Lookup(BOOKMARK:([Bmk1041]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES] AS [ses]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([STUDY_VERSIONS].[SVE_ID]) WITH PREFETCH)
|--Hash Match(Inner Join, HASH:([A].[SVE_ID])=([STUDY_VERSIONS].[SVE_ID]), RESIDUAL:([A].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID]))
| |--Nested Loops(Inner Join)
| | |--Nested Loops(Inner Join, WHERE:([usr].[USE_PER_ID]=[Union1169]))
| | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Apparch].[USERS].[USE_ID_PK] AS [usr]), SEEK:([usr].[USE_ID]=Convert([@p_In_USER_ID_Int])), WHERE:([usr].[USE_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | |--Concatenation
| | | |--Table Scan(OBJECT:([Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE:(((([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=Convert([@p_In_OTY_ID_Int])) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]<>NULL) AND [PERSON_ROLES].[PRL_STU_ID]<>NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Nested Loops(Inner Join)
| | | | |--Top(1)
| | | | | |--Filter(WHERE:([STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1128]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([STUDY_VERSIONS].[SVE_ID]))
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK:([STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | | |--Filter(WHERE:(([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1126]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK:([STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | | |--Table Scan(OBJECT:([Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE:(([PERSON_ROLES].[PRL_SITE_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [PERSON_ROLES].[PRL_STU_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Nested Loops(Inner Join)
| | | | |--Top(1)
| | | | | |--Filter(WHERE:([STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1143]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([STUDY_VERSIONS].[SVE_ID]))
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK:([STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | | |--Filter(WHERE:(([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1141]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK:([STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | | |--Table Scan(OBJECT:([Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE:((([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_NETWORK_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Hash Match(Inner Join, HASH:([ORGANIZATION_TYPES].[OTY_ID])=([PERSON_ROLES].[PRL_NETWORK_OTY_ID]), RESIDUAL:([ORGANIZATION_TYPES].[OTY_ID]=[PERSON_ROLES].[PRL_NETWORK_OTY_ID]))
| | | | |--Sort(DISTINCT ORDER BY:([ORGANIZATION_TYPES].[OTY_ID] ASC, [ORGANIZATIONS].[ORG_NAME] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]))
| | | | | | | | |--Hash Match(Inner Join, HASH:([PARTY_PAIRS].[PPA_ID])=([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]), RESIDUAL:([PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]))
| | | | | | | | | |--Filter(WHERE:([PARTY_PAIRS].[PPA_OTY_ESTABLISHED_TO_ID]=Convert([@p_In_OTY_ID_Int]) AND [PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1026]), OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS]) WITH PREFETCH)
| | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_COMBINATIONS].[PPC_ID]))
| | | | | | | | | | |--Hash Match(Inner Join, HASH:([PARTY_COMBINATION_ROLES].[PCR_ID])=([PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]), RESIDUAL:([PARTY_COMBINATION_ROLES].[PCR_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]))
| | | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_COMBINATION_ROLES].[PCR_ID_PK]), WHERE:([PARTY_COMBINATION_ROLES].[PCR_RECORD_STATUS_FLAG]='Active' AND [PARTY_COMBINATION_ROLES].[PCR_ROLE_TEXT]='Clinical Research Site'))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIR_COMBINATIONS].[PPC_ID_PK]), WHERE:([PARTY_PAIR_COMBINATIONS].[PPC_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS].[NIX_PPA_PPC_ID]), SEEK:([PARTY_PAIRS].[PPA_PPC_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_ID]) ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIR_RELATIONSHIPS].[PPR_ID_PK]), WHERE:([PARTY_PAIR_RELATIONSHIPS].[PPR_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[CHAINS].[CHA_ID_PK]), SEEK:([CHAINS].[CHA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]), WHERE:([CHAINS].[CHA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS].[PPA_ID_PK]), SEEK:([PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]), WHERE:([PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active' AND [ORGANIZATION_TYPES].[OTY_TYPE]='Network') ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | |--Table Scan(OBJECT:([Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE:((([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_STU_ID]<>NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Hash Match(Inner Join, HASH:([ORGANIZATION_TYPES].[OTY_ID])=([PERSON_ROLES].[PRL_NETWORK_OTY_ID]), RESIDUAL:([ORGANIZATION_TYPES].[OTY_ID]=[PERSON_ROLES].[PRL_NETWORK_OTY_ID]))
| | | |--Sort(DISTINCT ORDER BY:([ORGANIZATION_TYPES].[OTY_ID] ASC, [ORGANIZATIONS].[ORG_NAME] ASC))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]))
| | | | | | | |--Hash Match(Inner Join, HASH:([PARTY_PAIRS].[PPA_ID])=([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]), RESIDUAL:([PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]))
| | | | | | | | |--Filter(WHERE:([PARTY_PAIRS].[PPA_OTY_ESTABLISHED_TO_ID]=Convert([@p_In_OTY_ID_Int]) AND [PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS]) WITH PREFETCH)
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([PARTY_PAIR_COMBINATIONS].[PPC_ID]))
| | | | | | | | | |--Hash Match(Inner Join, HASH:([PARTY_COMBINATION_ROLES].[PCR_ID])=([PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]), RESIDUAL:([PARTY_COMBINATION_ROLES].[PCR_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]))
| | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_COMBINATION_ROLES].[PCR_ID_PK]), WHERE:([PARTY_COMBINATION_ROLES].[PCR_RECORD_STATUS_FLAG]='Active' AND [PARTY_COMBINATION_ROLES].[PCR_ROLE_TEXT]='Clinical Research Site'))
| | | | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIR_COMBINATIONS].[PPC_ID_PK]), WHERE:([PARTY_PAIR_COMBINATIONS].[PPC_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS].[NIX_PPA_PPC_ID]), SEEK:([PARTY_PAIRS].[PPA_PPC_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_ID]) ORDERED FORWARD)
| | | | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIR_RELATIONSHIPS].[PPR_ID_PK]), WHERE:([PARTY_PAIR_RELATIONSHIPS].[PPR_RECORD_STATUS_FLAG]='Active'))
| | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[CHAINS].[CHA_ID_PK]), SEEK:([CHAINS].[CHA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]), WHERE:([CHAINS].[CHA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[PARTY_PAIRS].[PPA_ID_PK]), SEEK:([PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]), WHERE:([PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active' AND [ORGANIZATION_TYPES].[OTY_TYPE]='Network') ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | |--Nested Loops(Inner Join)
| | | |--Top(1)
| | | | |--Filter(WHERE:([STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1163]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([STUDY_VERSIONS].[SVE_ID]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK:([ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE:([ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT:([Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK:([ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE:([ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK:([STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Filter(WHERE:(([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1161]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK:([STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | |--Table Scan(OBJECT:([Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE:(([PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL AND [PERSON_ROLES].[PRL_STU_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | |--Filter(WHERE:((([A].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [A].[SVE_CHANGE_TYPE]=NULL) AND [A].[SVE_CHANGE_NUMBER]=NULL) AND [A].[SVE_RECORD_STATUS_FLAG]='Active'))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1040]), OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS] AS [A]))
| | |--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID] AS [A]), SEEK:([A].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| |--Filter(WHERE:((([@p_In_AE_REPORT_TYPE_Int]=0 AND ([Expr1119]='EAE' OR [Expr1119]='SAE')) OR ([@p_In_AE_REPORT_TYPE_Int]=1 AND [Expr1119]='EAE')) OR [@p_In_AE_REPORT_TYPE_Int]<>1 AND [@p_In_AE_REPORT_TYPE_Int]<>0))
| |--Compute Scalar(DEFINE:([STUDY_VERSIONS].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID], [Expr1119]=If like(upper([DOMAIN_VALUES].[DVA_VALUE_TERM]), '%EAE%', NULL) then [DOMAIN_VALUES].[DVA_VALUE_TERM] else If [Expr1183] then 'EAE' else [DOMAIN_VALUES].[DVA_VALUE_TERM]))
| |--Nested Loops(Left Semi Join, WHERE:(like(upper([DOMAIN_VALUES].[DVA_VALUE_TERM]), '%EAE%', NULL))OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE:([Expr1183] = [PROBE VALUE]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | |--Hash Match(Right Outer Join, HASH:([AE_REPORTING_MANUALS].[ARM_ID])=([STUDY_VERSIONS].[SVE_ARM_ID]), RESIDUAL:([STUDY_VERSIONS].[SVE_ARM_ID]=[AE_REPORTING_MANUALS].[ARM_ID]))
| | | |--Table Scan(OBJECT:([Daidsesxml].[Voyager].[AE_REPORTING_MANUALS]))
| | | |--Hash Match(Inner Join, HASH:([DOMAIN_VALUES].[DVA_VALUE_CODE])=([STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]), RESIDUAL:([STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]=[DOMAIN_VALUES].[DVA_VALUE_CODE]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1045]), OBJECT:([Daidsesxml].[Apparch].[DOMAIN_VALUES]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([DOMAIN_NAMES].[DNA_ID]))
| | | | |--Filter(WHERE:([DOMAIN_NAMES].[DNA_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK:([Bmk1047]), OBJECT:([Daidsesxml].[Apparch].[DOMAIN_NAMES]))
| | | | | |--Index Scan(OBJECT:([Daidsesxml].[Apparch].[DOMAIN_NAMES].[DNA_AVE_ID_NAME_UK]), WHERE:([DOMAIN_NAMES].[DNA_NAME]='Protocol AE Reporting Methods'))
| | | | |--Index Seek(OBJECT:([Daidsesxml].[Apparch].[DOMAIN_VALUES].[DVA_DNAID_VALUETERM_UK]), SEEK:([DOMAIN_VALUES].[DVA_DNA_ID]=[DOMAIN_NAMES].[DNA_ID]) ORDERED FORWARD)
| | | |--Filter(WHERE:(((([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098])) OR [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1102]) OR [Expr1186]=NULL))
| | | |--Nested Loops(Left Semi Join, WHERE:((([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098])) OR [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1102])OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE:([Expr1186] = [PROBE VALUE]))
| | | |--Nested Loops(Inner Join, WHERE:(([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098]))OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | | | |--Nested Loops(Inner Join, WHERE:([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved')OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | | | | |--Nested Loops(Left Semi Join, WHERE:([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved')OUTER REFERENCES:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE:([Expr1185] = [PROBE VALUE]))
| | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]), WHERE:([STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Row Count Spool
| | | | | | |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | | | |--Hash Match(Cache, HASH:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL:([STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | | | | |--Stream Aggregate(DEFINE:([Expr1098]=MAX([STUDY_VERSIONS].[SVE_CHANGE_NUMBER])))
| | | | | |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | | |--Hash Match(Cache, HASH:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL:([STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | | | |--Stream Aggregate(DEFINE:([Expr1102]=MAX([STUDY_VERSIONS].[SVE_CHANGE_NUMBER])))
| | | | |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | |--Row Count Spool
| | | |--Filter(WHERE:([STUDY_VERSIONS].[SVE_CHANGE_TYPE]<>NULL))
| | | |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | |--Hash Match(Cache, HASH:([STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL:([STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | |--Top(1)
| | |--Compute Scalar(DEFINE:([STUDY_VERSIONS].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID]))
| | |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CHANGE_TYPE]=NULL))
| | |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| |--Row Count Spool
| |--Nested Loops(Inner Join, WHERE:([STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]=[DOMAIN_VALUES].[DVA_VALUE_CODE]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1114]), OBJECT:([Daidsesxml].[Apparch].[DOMAIN_VALUES]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([DOMAIN_NAMES].[DNA_ID]))
| | |--Filter(WHERE:([DOMAIN_NAMES].[DNA_RECORD_STATUS_FLAG]='Active'))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1116]), OBJECT:([Daidsesxml].[Apparch].[DOMAIN_NAMES]))
| | | |--Index Scan(OBJECT:([Daidsesxml].[Apparch].[DOMAIN_NAMES].[DNA_AVE_ID_NAME_UK]), WHERE:([DOMAIN_NAMES].[DNA_NAME]='Protocol AE Reporting Methods'))
| | |--Index Seek(OBJECT:([Daidsesxml].[Apparch].[DOMAIN_VALUES].[DVA_DNAID_VALUETERM_UK]), SEEK:([DOMAIN_VALUES].[DVA_DNA_ID]=[DOMAIN_NAMES].[DNA_ID]), WHERE:(like([DOMAIN_VALUES].[DVA_VALUE_TERM], '%EAE%', NULL)) ORDERED FORWARD)
| |--Index Spool(SEEK:([STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active' AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| |--Clustered Index Scan(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
|--Index Seek(OBJECT:([Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID] AS [ses]), SEEK:([ses].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
 
I don't have time to review all that just now, but I do see tablescans on PersonRoles. If this is a large table or view this could be slowing things down. Also are those indexes it is useing onthe underlying tables or the views? Views can be faster if they have their own indexes.

"NOTHING is more important in a database than integrity." ESquared
 
Table scan most likely the main cause.

A few bookmarks lookups. Not sure how much those affect performance.

Also a SORT in the middle. If the number of records is significant, then this will also affect performance.

Some hash match also.

I also see a few convert functions of variables. Maybe you should do the convert outside main SQL, and use converted variables instead. Not really important this one I think.


Can you post the original SQL statement? it may also help a bit to suggest any change to it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top