Applications often present a way to allow users to select a number of values. These values are then assembled in an IN() clause that contains a list of values to be matched, as in the following example:
SELECT * FROM TableA WHERE SomeColumn IN( 1, 2, 3...) But as the list of items to match grows longer, it takes SQL more time to perform it. Against a large number of rows, this can be especially problematic, since there’s no way to take advantage of the index. The result is a table scan in which each row is compared to each value in the list.
The values in the IN() clause typically come from a front-end application whose user makes a run-time selection. (If that wasn’t the case, you could rewrite the query to optimize it.)
A better approach in such situations is to build a table from the list of values and join the table to the target table. This enables SQL to take advantage of the index, which significantly increases performance.
The question remains: how do you build the temporary table to hold the values? There are several ways, but this tip uses a user-defined function (UDF) that parses a delimited string and returns a table in which each item in the string becomes a row in the table. The UDF looks like this:
CREATE FUNCTION fn_StringToTable (@String varchar(100))RETURNS @Values TABLE (ID int primary key)ASBEGIN DECLARE @pos int DECLARE @value int WHILE @string > '' BEGIN SET @pos = CHARINDEX(',', @string) IF @pos > 0 BEGIN SET @value = SUBSTRING( @string, 1, @pos - 1) select @string = LTRIM(SUBSTRING( @string, @pos + 1, LEN(@string)-@pos+1)) INSERT @Values SELECT @value END ELSE IF LEN(@string) > 0 BEGIN SET @value = @string INSERT @Values SELECT @value SET @string = '' END ELSE SET @string = '' END RETURNEND Execute this function by passing it a comma-delimited string like this:
SELECT * FROM fn_StringToTable( '100, 200, 333, 444, 555') Now, you can join this table to any other table, view, or table UDF, and get maximum performance with minimal disk reads.
