I am being redundant - optimize SP

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

SQL Server 2005 Forums

Transact-SQL (2005)

I am being redundant - optimize SP

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-06-29 : 17:30:49

I am not sure if is posible to visualize what is going on here but if you can,

I have this SP, and i am thinking my where clause is not well put, I get my results but the where clause looks like could be better express

SELECT
mu.UserID,
UserName,
EmailAddress,
State,
isnull(dm.MemberID,'-1') AS MemberID,
isnull(Name,'') AS CustomName,
isnull(dm.Title,'') AS Title,
isnull(dm.ImageID, '') AS ImageID,
isnull(ShowName,'0') AS ShowName,
isnull(ShowTitle,'0') AS ShowTitle,
isnull(ShowLocation,'0') AS ShowLocation,
isnull(ShowPicture,'0') AS ShowPicture,
isnull(ShowTotalPosts,'0') AS ShowTotalPosts,
isnull(ShowEmailLink,'0') AS ShowEmailLink,
isnull(Notify,'0') AS Notify,
ImagePath,
(Select Count(messageID)
FROM Discussion_message dm1
WHERE dm1.UserID = mu.UserID AND Active = 1) As AuthorNumberOfPosts,
MessageID,
ParentMessageID,
Topic,
PostDate,
message

FROM Membership_User mu
join Membership_UserAddress mua ON mu.UserID = mua.UserID
left join Discussion_Member dm ON mu.UserID = dm.UserID
left join Discussion_Member_Options dmo ON dm.MemberID = dmo.MemberID
left join Discussion_Image di ON dm.ImageID = di.ImageID
join Discussion_message dmsg ON mu.UserID = dmsg.UserID AND dmsg.Active = 1
WHERE
MessageID = @ThreadID OR ParentMessageID = @threadID AND mu.UserID in ( SELECT distinct UserID FROM Discussion_message WHERE MessageID = @threadID OR ParentMessageID = @threadID AND Active = 1)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-29 : 18:14:46

The combination of OR and AND in the WHERE condition is something you should look carefully into. Use proper parantheses.
WHERE A OR B AND C has to be properly paranthesised as WHERE (A OR B) AND C or WHERE A OR (B AND C) etc. Also I dont see why you need the additional mu.userid in (...). The table is already in your JOIN's and the Active=1 is also specified.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources