HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to avoid using the IN clause in sql query used in a stored procedure

Submitted by: @import:stackexchange-dba··
0
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.

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)
  END


Kindly 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.