SSRS Multi-Value Parameter Performance Issue

Josh,

So you've built a report off a query that uses a multi-value parameter and you've defaulted the parameter to use every option from a dataset.  This works fine for a few values, but when the multi-value parameter contains hundreds, the query is dreadfully slow!

The solution is to add a default value to ignore your parameter in the query...

Let's say you have a query like this:

SELECT
   Id,
   FirstName,
   LastName,
   Age
FROM
   Customer
WHERE
   Id IN (@Ids)

and your "@Ids" parameter is being defaulted from all values of the following query:

SELECT
   GroupName
FROM
   MyGroups

If there are many results returned from this query, the main query might be very slow. Instead you could change the Parameter query to something like this:

SELECT
   ' ALL ' AS GroupName
UNION ALL
SELECT
   GroupName
FROM
   MyGroups

Then the Main Query would look like this:

SELECT
   Name,
   Description,
   GroupName
FROM
   MyTable
WHERE
   @GroupNames = ' ALL ' OR
   GroupName IN (@GroupNames)

Then the parameter can be defaulted to a single value of " ALL " and the second part of the where clause will not be considered since the first is true.