Performance hit using ' where id IN [table] ' with only 1 row in [table]?

Question

I have a stored procedure that takes a comma-delimited string of IDs. I split them and put them into a temporary table and pull out records from another table using where id IN [table]

Is it ok to use this same procedure when only one id is passed in for the param? I could write a second stored procedure that would do exactly the samething but instead do where id = @id.

I have MANY stored procedures where multiple IDs or just one could be passed in. Do I try to reuse the existing procedures or write new ones? Is the performance hit significant?

Answer 1

You might like to try a JOIN instead of WHERE id IN - although I think you will get the same query plan.

So I assume you are doing

SELECT COl1, Col2, ... FROM MyTable WHERE id IN (SELECT id FROM @MyTempTable)

in which case the equivalent JOIN syntax would be

SELECT COl1, Col2, ... FROM MyTable AS T1 JOIN @MyTempTable AS T2 ON T2.id = T1.id

and in the second case whether there is 1, or many rows, it will be very effective provided [id] is indexed (I am assuming its the PK on your table, and using a Clustered Index).

(Beware that if you have DUP IDs in @MyTempTable you will wind up getting dups from MyTable as well :( )

For best performance it would be worth explicitly declaring [id] as the PK on your temporary table (but given it only holds a few rows it probably won't make much odds)

DECLARE @TempTable TABLE
(
    id int NOT NULL,
    PRIMARY KEY
    (
        id
    )
)

Answer 2

You could use the same procedure, but use a conditional statement to determine whether use use the IN clause.

There is a performance hit with IN; the execution plan should detail this for you.

Answer 3

As rde6173 says, perform a COUNT on the temporary table to determine which SELECT query to use.

Answer 4

I wouldn't worry about the performance hit of in with only one item until I had observed a performance problem with it. The query optimizer is smart and may very well deal with the one item in, but even if it doesn't, your routines will probably be slowest elsewhere.

I would look at the performance of the string parsing, temp table creation and insertion into the temp table. Making those as fast a possible, will have a bigger effect on overall performance than wether you use in or = for the one item case.

Answer 5

Since you've specified that it's a comma-delimited list, you can do something like this in your sproc:

IF (CHARINDEX(',', @id) = 0)
BEGIN
    -- the @id parameter contains a single value
    SELECT *
    FROM your_table
    WHERE id = @id  -- maybe need to cast @id if the column isn't a string
END
ELSE
BEGIN
    -- the @id parameter contains a comma-delimited list
    -- only perform the expensive splitting logic at this point
    -- eg, SET @yourTempTable = dbo.SplitCommaDelimitedIDsIntoTable(@id)
    SELECT *
    FROM your_table
    WHERE id IN (SELECT id FROM @yourTempTable)
END

Answer 6

When you create a temporary table (not a table variable), it has statistics. As such, the optimizer will determine the best plan, and the best plan for one ID might be the same as for 10 IDs, but for 50K IDs it may choose a different plan. So, I would not try to optimize it further unless you have performance concerns.