Suppose we want to pass an array of values as a parameter to an SP in which it is used as values in IN clause.
Which one is better in case of performance :
1- Passing values as comma separated string
ie
Select * FROM myTable WHERE Id IN (@myConcatenatedValues)
OR
2- Passing values as a table variable consist of a column that holds this values as rows
ie
Select * FROM myTable WHERE Id IN (Select Id from @myVariableTable)
Thanks in advance
I did a quick test run of this using the below 3 queries:
DECLARE @UsersInString VARCHAR(255) = '1, 2, 3, 4, 5, 6, 7, 8, 9, 10'
/* Prone to SQL Injection, do not use like this! Sanitise your inputs */
EXEC (' SELECT * FROM [dbo].[User] WHERE UserID IN ( ' + @UsersInString + ' ) ')
GO
and:
DECLARE @UsersInTable TABLE (
UserID INT
)
INSERT INTO @UsersInTable
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
SELECT *
FROM [dbo].[User] U
JOIN @UsersInTable UT ON UT.UserID = U.UserID
and:
DECLARE @UsersInTable TABLE (
UserID INT
)
INSERT INTO @UsersInTable
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
SELECT *
FROM [dbo].[User] U
WHERE U.UserID IN (
SELECT UserID
FROM @UsersInTable
)
The actual execution plan returned indicates that the top query is faster in my case; the query used a Clustered Index Seek only.
The middle query was marginally slower as along with the Clustered Index Seek it also used a Table Scan on the table variable.
The bottom query was even slower as along with the Clustered Index Seek and Table Scan, it also performed a Sort on the table variable; one would assume in an attempt to optimise the IN () query against the sorted Clustered Index.
However it is largely dependent on the indexing of the column being queried; along with how long it would take to build the string or table variable.
A fairly important side note: Passing the values as a comma separated string could be risky; you would have to dynamically construct the IN () statement using string concatenation; which could leave you open to SQL Injection.
declare @severities table (SeverityId uniqueidentifier unique clustered)