Adriaan,

I'm rather surprised at the blow-out to ten minutes for that basic shred-the-tokenised-string-to-a-table approach.  I've done this several times as a way of dealing with SSRS multi-select, and found it fine for performance (although really it's a bit crude and hack-y).

Was there anything obvious in the  query plan (of the porly performing query) to indicate why this was such a bad thing to do? 

Thanks for the post Adriaan. It would be great if you could paste some sample code here!

Nick

no need for dynamic SQL, just use something like this:

WHERE charindex(',' + field1 + ',',',' + @param1 + ',') > 0

... where @param1 is a string of comma-delimited values.

We faced this problem a while back at our company as well.  And one of our DBAs came up with the following solution:

WHERE (@CommaDelimtedList + ',' LIKE '%'+ TableColumnToCompareTo + ',%' OR @CommaDelimtedList = '')

Just thought I'd share with everyone how we handle this situation.

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