Adriaan,

When you create the temp table, was included a index for the join column ?, the performance between a table without index vs an indexes table in this case can be very big.

Example: The customer table have the next structure:

create table customer (

  id         int primary key

, state    int

, LName   varchar(100)

, rname    varchar(100))

and supose that table have an index on the state column

create index IDX_Customer_State on Customer (state)

so, we temp table have the next code

declare @TempJoinTable (IdState    int   primary key) //note the primary key, we can index a @temptable like anyone

the next step is to populate the temp table with this code

-- the @FilterValue contain the filter values with comma

DECLARE @posicion     INT

DECLATE @Value        VARCHAR(100)

WHILE (Len(@FilterValue))

BEGIN

     SET @Posicion = PATINDEX('%,%',@FilterValue)

     IF (@Posicion = 0)

     BEGIN

          SET @Value = @FilterValue

          SET @FilterValue = ''

     END

     ELSE

     BEGIN

          SET @Value = SUBSTRING(@FilterValue,1,@Posicion-1)

          SET @FilterValue = SUBSTRING(@FilerValue,@Posicion+1,LEN(@FilterValue)

     END

     INSERT INTO @TempJoinTable VALUES (@Value)

END

finally, just join the tables and compare the execution time

SELECT *

FROM customer CUS

     INNER JOIN @TempJoinTable TT

         ON CUS.State = TT.IdState