Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite
2

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

share|improve this question
5  
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 Dec 17 '12 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 Dec 17 '12 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 Dec 17 '12 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 Dec 17 '12 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 Dec 17 '12 at 14:25
show 2 more comments

2 Answers

up vote 1 down vote

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

share|improve this answer
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 Dec 17 '12 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. – Justicator Dec 26 '12 at 14:47
up vote 1 down vote

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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