snippetsqlMinor
How to avoid using the IN clause in sql query used in a stored procedure
Viewed 0 times
storedthesqlusedqueryavoidprocedureusinghowclause
Problem
Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the User and Member tables.
Kindly suggest the alternative as i don't have a full knowledge in writing SP
CREATE PROCEDURE [dbo].[sp_MemberIdsFromUserIds] @dtUserIds UNIQUETABLE readonly
AS
BEGIN
SELECT userID,
Memberid
FROM Member
INNER JOIN USER
ON UserMemberID = MemberiD
WHERE userID IN (SELECT UniqueId
FROM @dtUserIds)
ENDKindly suggest the alternative as i don't have a full knowledge in writing SP
Solution
Where exists is gnerally faster than IN
SELECT userID,
Memberid
FROM Member
INNER JOIN [USER]
ON UserMemberID = MemberiD
WHERE EXISTS (SELECT *
FROM @dtUserIds a where [user].userId = a.Uniqueid)Code Snippets
SELECT userID,
Memberid
FROM Member
INNER JOIN [USER]
ON UserMemberID = MemberiD
WHERE EXISTS (SELECT *
FROM @dtUserIds a where [user].userId = a.Uniqueid)Context
StackExchange Database Administrators Q#30563, answer score: 5
Revisions (0)
No revisions yet.