Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
headbuzz
Starting Member
11 Posts |
Posted - 2013-07-31 : 11:52:15
|
[code]Declare @MemberID varchar(25)SET @MemberID='1234567'Declare @SubscriberID varchar(25)SET @SubscriberID = (SELECT TM.SubscriberID FROM fccore.dbo.tCMMembers TM WHERE TM.MemberID = @MemberID)IF @SubscriberID=@MemberIDBEGINSELECT TM.MemRecID, TM.MemberID, TM.FirstName, TM.LastName, TM.DOB, TM.SubscriberID, TE.MemberTypeID,fccore.dbo.AppSubsRelationships.RelationshipFROM fccore.dbo.tCMMembers TM with(NOLOCK)inner JOIN fccore.dbo.tCMMembElig TE with(NOLOCK) ON TM.MemberID = TE.MemberIDINNER JOIN fccore.dbo.AppSubsRelationships with(NOLOCK) ON fccore.dbo.AppSubsRelationships.RelationshipID = TE.MemberTypeIDWHERE TM.SubscriberID=@SubscriberIDENDELSEBEGINSELECT TM.MemRecID, TM.MemberID, TM.FirstName, TM.LastName, TM.DOB, TM.SubscriberID, TE.MemberTypeID,fccore.dbo.AppSubsRelationships.RelationshipFROM fccore.dbo.tCMMembers TM with(NOLOCK)inner JOIN fccore.dbo.tCMMembElig TE with(NOLOCK) ON TM.MemberID = TE.MemberIDINNER JOIN fccore.dbo.AppSubsRelationships with(NOLOCK) ON fccore.dbo.AppSubsRelationships.RelationshipID = TE.MemberTypeIDWHERE TM.MemberID=@MemberIDEND[/code] |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-31 : 12:17:23
|
What indexes do you have? |
|
|
headbuzz
Starting Member
11 Posts |
Posted - 2013-07-31 : 12:25:14
|
quote: Originally posted by Lamprey What indexes do you have?
tcmmembers: FirstName,LastName,DOB,SubscriberID, MemberID,MemrecIDtCMMembElig : MemberIdAppSubsRelationships : RelationshipID |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-31 : 15:38:42
|
How many rows are in each table? Given your queries above; how many rows are returned, on average, for a given Subscriber or Member ID? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-31 : 15:39:27
|
quote: Originally posted by headbuzz
quote: Originally posted by Lamprey What indexes do you have?
tcmmembers: FirstName,LastName,DOB,SubscriberID, MemberID,MemrecID
Is that one index or an index on each column? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 02:31:23
|
Which version you're using? If on 2008 or above, you'll have index suggestion specified by query engine when you turn actual execution plan on.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|