March 4, 2014 at 12:14 am
Hi ALl,
Hi Have a table with non clustered index on primary key
index_name index_description index_keys
PK_ResearchUserAlias_tblnonclustered, unique, primary key located on PRIMARYuserObjectId, researchContributorId
query :
select ep.*
,rc.brokerageFirmFlag
,rc.independentFirmFlag
,rc.quantitativeFirmFlag
,cast(isnull(my.ExcludeFlag,0) as bit) as ExcludeFlag
,cast(isnull(my.myFlag,0) as bit) as myFlag
,cast(case when ep.researchContentViewTypeId = 1 then 0 else 1 end as bit) as partialAccessFlag
,cast(case when ep.researchProductSalesModelId in (2,3,4) then 1 else 0 end as bit) as afterMarketFlag
,cast(case when rua.researchContributorId is null or ep.researchProductSalesModelId in (2,3,4) then 0 else rua.userIsBound end as bit) as boundFlag
,cast(isnull(rc.supportsLinkbacks,0) as bit) as supportsLinkbackFlag
,cast(isnull(rc.isBindingContributor,0) as bit) as bindingContributorFlag
,@useCapCache as capCacheFlag
from #entitledProducts ep
inner join ResearchContributor_tbl rc (nolock)
on ep.researchContributorId = rc.researchContributorId
left outer join ResearchUserAlias_tbl rua WITH(INDEX(PK_ResearchUserAlias_tbl))
on rua.researchContributorId = ep.researchContributorId
and rua.userObjectId = ep.userObjectId
left outer join @my my
on my.UserObjectId = ep.userObjectId
and my.researchContributorId = ep.researchContributorId
execution plan :
March 4, 2014 at 12:38 am
Please post the actual execution plan of the problematic query as a .sqlplan file attachment.
March 4, 2014 at 1:52 am
pls find the original execution plan
March 4, 2014 at 1:54 am
Remove the index hint from the query whilst you are still testing and developing. Index hints are used when you've exhausted other, simpler, possibilities.
Add rua.userIsBound to the index as an INCLUDE column.
Then post the actual execution plan as a .sqlplan attachment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 4, 2014 at 3:14 am
ChrisM@Work (3/4/2014)
Remove the index hint from the query whilst you are still testing and developing. Index hints are used when you've exhausted other, simpler, possibilities.Add rua.userIsBound to the index as an INCLUDE column.
Then post the actual execution plan as a .sqlplan attachment.
+1,
"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw π
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply