Optimize queries with temporary tables

from the Artful SQL Server & Access Tips List


Applications often present a way to allow a user 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, we could rewrite the query to optimize it.)

A better approach 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.

But how do we build the temporary table to hold the values? There are several ways, but the one I like 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)

AS

BEGIN

 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

 RETURN

END



You would execute this function, 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.


Return to the Artful SQL Server & Access Tips page