How can I make this query run faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I make this query run faster?

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=@MemberID
BEGIN
SELECT TM.MemRecID, TM.MemberID, TM.FirstName, TM.LastName, TM.DOB, TM.SubscriberID,
TE.MemberTypeID,fccore.dbo.AppSubsRelationships.Relationship
FROM fccore.dbo.tCMMembers TM with(NOLOCK)
inner JOIN fccore.dbo.tCMMembElig TE with(NOLOCK) ON TM.MemberID = TE.MemberID
INNER JOIN fccore.dbo.AppSubsRelationships with(NOLOCK) ON fccore.dbo.AppSubsRelationships.RelationshipID = TE.MemberTypeID
WHERE TM.SubscriberID=@SubscriberID

END

ELSE
BEGIN
SELECT TM.MemRecID, TM.MemberID, TM.FirstName, TM.LastName, TM.DOB, TM.SubscriberID, TE.MemberTypeID,fccore.dbo.AppSubsRelationships.Relationship
FROM fccore.dbo.tCMMembers TM with(NOLOCK)
inner JOIN fccore.dbo.tCMMembElig TE with(NOLOCK) ON TM.MemberID = TE.MemberID
INNER JOIN fccore.dbo.AppSubsRelationships with(NOLOCK) ON fccore.dbo.AppSubsRelationships.RelationshipID = TE.MemberTypeID
WHERE TM.MemberID=@MemberID
END

[/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,MemrecID

tCMMembElig : MemberId

AppSubsRelationships : 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources