8

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

Improve this question
7
  • 6
    The query is fine. Probably you are missing some indexes. What indexes do you have? What does the execution plan look like? What is the definition of the table type? How many rows are you passing in in @dtUserIds?
    – Martin Smith
    Commented Dec 17, 2012 at 10:12
  • @MartinSmith: I am having index on userid and memberid. The table will be a DataTable [From ADO.Net]. I will pass 25000 rows in the table. Kindly suggest a best solution if any
    – Saravanan
    Commented Dec 17, 2012 at 10:26
  • 3
    What is the definition of the UNIQUETABLE type? Do you have a primary key on userID? Also you may be getting a bad plan due to cardinality estimates on the TVP. You could try with OPTION (RECOMPILE) to see if you get a plan better suited for 25K rows.
    – Martin Smith
    Commented Dec 17, 2012 at 10:29
  • @MartinSmith: The userId are uniqueidentifier datatypes. I have userId as primary key in usertable and so with the case of memberid. shall i use the option(recompile) in the stored procedure.
    – Saravanan
    Commented Dec 17, 2012 at 14:22
  • Yes add it to the end of the query and see if that improves things. If it does and you are always going to be passing in large numbers of rows you might want to then look at the plan you get and use other query hints to get the same plan without needing to recompile every time (but this will also mean that the plan won't adjust to cardinality changes in the other tables involved)
    – Martin Smith
    Commented Dec 17, 2012 at 14:25

3 Answers 3

Reset to default
5

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)
Improve this answer
3

Try this option, there is a half-chance that SQL Server will behave differently...

CREATE PROCEDURE [dbo].[sp_MemberIdsFromUserIds] @dtUserIds UNIQUETABLE readonly
AS
BEGIN
    SELECT  userID,
        Memberid
    FROM  Member
    INNER JOIN USER
        ON UserMemberID = MemberiD
    INNER JOIN @dtUserIds
        ON UniqueId = userID
END

If this does not help, than please post all 3 tables' structures
(user, memeber and @dtUserIds) - including indexes and keys.

good luck :)

Improve this answer
2
  • This is a simple query. Why would you expect the inner join being faster than an IN? What would be the chance that the execution plan would be different using this writing?
    – Marian
    Commented Dec 17, 2012 at 22:17
  • @Marian I do not EXPECT it to be faster, saravanan asked for a way to avoid using the IN clause, and this is an option which is easy to read as well. There is a chance however, that this WILL be faster under certain circumstances, influenced by table definitions, data distribution, statistics etc. where the difference will show in the execution plan (query plan) e.g. when "number of executions" of the IN clause will be high due to the use of nested loops, and an inner join will use a different method.
    – Roi Gavish
    Commented Dec 26, 2012 at 14:47
-2 
Create proc demo
@A_C varchar(50)
AS
BEGIN
EXEC ('select * from table1 where AC_No in ('+@A_C+')')
END

EXEC demo'321101030249,321101030268'
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.