I've got a saved method that requires a comma-delimited string of IDs. I split them and set them right into a temporary table and take out records from another table using where id IN [table]
Could it be ok to make use of this same procedure when just one id is passed set for the param? I possibly could write another saved method that would just do the samething but rather do where id = @id.
I've MANY saved methods where multiple IDs or simply you could be passed in. Will I attempt to reuse the present methods or write brand new ones? May be the performance hit significant?
You may want to consider using a JOIN rather than WHERE id IN - although I believe you're going to get exactly the same query plan.
And So I assume you do
Choose COl1, Col2, ... FROM MyTable WHERE id IN (Choose id FROM @MyTempTable)
by which situation the same JOIN syntax could be
Choose COl1, Col2, ... FROM MyTable AS T1 JOIN @MyTempTable AS T2 ON T2.id = T1.id
as well as in the 2nd situation whether there's 1, or many rows, it will likely be extremely effective provided [id] is indexed (I'm presuming it is the PK you are cooking, and taking advantage of a Clustered Index).
(Beware that for those who have DUP IDs in @MyTempTable you'll find yourself getting dups from MyTable too :( )
For the best performance it might be worth clearly proclaiming [id] because the PK in your temporary table (but trained with only holds a couple of rows it most likely will not make much odds)
DECLARE @TempTable TABLE ( id int NOT NULL, PRIMARY KEY ( id ) )
I would not be worried about the performance hit of along with just one item until I'd observed a performance trouble with it. The query optimizer is wise and might easily cope with the main one item in, but even when it does not, your programs will most likely be slowest elsewhere.
I'd consider the performance from the string parsing, temp table creation and insertion in to the temp table. Making individuals as quickly a potential, may have a larger impact on efficiency than wether you utilize in or = for that one item situation.
You could utilize exactly the same procedure, but make use of a conditional statement to find out whether use make use of the IN clause.
There's a performance hit within the execution plan should detail this for you personally.
As rde6173 states, execute a Rely on the temporary table to find out which Choose query to make use of.
Since you've specified it's a comma-delimited list, that you can do something similar to this inside 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
Whenever you produce a temporary table (not really a table variable), it's statistics. As a result, the optimizer determines the very best plan, and also the best arrange for one ID may be the same for 10 IDs, however for 50K IDs it might select a different plan. So, I wouldn't attempt to optimize it further unless of course you've performance concerns.