Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Mar 7, 2005

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)
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 
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.

Parthasarathy Mandayam
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date