How to make query with multi value parameter run faster?

I have the following query that is running very slow:

SELECT 
    DISTINCT a.Role as Role
FROM 
    [Table_A] a 
JOIN 
    [Table_B] b ON (a.Key = b.Key)
WHERE 
    b.Date BETWEEN @StartDate AND @EndDate
    AND ISNULL(a.ID, -1) IN (@People)

The values of the variables @StartDate and @EndDate and @People come from parameters in a SSRS report. The date parameters are just dates. The @People parameter is a multi value parameter.

The problem is that @People contains over 3000 values. So the query has to go through it all using the IN clause. This really, really slows down my query when running it in SSRS.

I wanted to use an exists clause to replace the IN clause but I can't seem to get that to work in this scenario. I'd need to somehow select the values from the @People variable in the EXISTS clause and join it back to the first table, but I don't even know if that is possible.

Perhaps I am going down the wrong direction with this trying to use the EXISTS in this scenario. But I still need to fix the query so it runs faster.

Can anyone help with this?

Solution

ISNULL(a.ID, -1) is going to make the query non-SARGable. You would be better off using (a.ID IN (@People) OR a.ID IS NULL), however, an IN with that many arguments is unlikely to run well.

I'm running on memory here (I don't have SSRS at home) but if i recall SSRS does some "magic" with multivalue parameters and IN that doesn't scale well. Perhaps you would be better trying to use an EXISTS and a splitter (such as DelimitedSplit8k). This specific example relies on @People having less than 8000 characters.

SELECT DISTINCT a.Role
FROM [Table_A] a 
     JOIN [Table_B] b ON a.Key = b.Key
WHERE b.Date BETWEEN @StartDate AND @EndDate
  AND (EXISTS (SELECT 1
               FROM dbo.DelimitedSplit8K(@People,',') DS
               WHERE DS.Item = a.ID)
   OR  a.ID IS NULL);

Considering, however, that ordinal position doesn't matter here, then other splitters are available. For example the XML Splitter.

For completeness, a quickly written XML Splitter Function:

CREATE FUNCTION dbo.XMLSplitter (@DelimitedString varchar(MAX))
RETURNS TABLE AS RETURN

    SELECT n.d.value('.','varchar(MAX)') AS Item
    FROM (VALUES(CONVERT(xml,'<d>'+ REPLACE(@DelimitedString,',','</d><d>') + '</d>'))) V(X)
         CROSS APPLY V.X.nodes('d') n(d);

GO

Added a full example without a function:

SELECT DISTINCT a.Role
FROM [Table_A] a 
        JOIN [Table_B] b ON a.Key = b.Key
WHERE b.Date BETWEEN @StartDate AND @EndDate
    AND (EXISTS (SELECT 1
                 FROM (VALUES(CONVERT(xml,'<d>'+ REPLACE(@DelimitedString,',','</d><d>') + '</d>'))) V(X)
                       CROSS APPLY V.X.nodes('d') n(d)
                 WHERE n.d.value('.','varchar(MAX)') = a.ID)
    OR  a.ID IS NULL);