devxlogo

Building Temporary Tables to Optimize Queries

Building Temporary Tables to Optimize Queries

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.

devx-admin

Share the Post: