Reporting Services Report Optimized for the 'Select All' option of a Multi-Select Parameter

A SQL Server Reporting Services (SSRS) report was behaving dramatically slow when the user selected all parameters in a multi-select parameter list of 30-40 locations.  The report performed within 5-10 seconds when 1 location was selected.  Selecting all locations would eventually time out, exceeding what I believe is a 5 minute connection time out by default in a Reporting Services in SharePoint integrated mode. 

Below are the steps used to improve this performance to an acceptable level..

Removed the Where clause that was filtering by the location parameter

Changed it to a DataSet filter.

When passed in the query, SQL Server was trying to incorporate it in the query plan.  Because the institution is a string value that itself needs to be parsed, this made for a complex loop through the passed in institutions.  This might be optimized in the future by 'closing' the view (there are some requirements on the structure of the view to do so)

The query plan for the query filtered compared to the unfiltered query were similar.  Both actually rely on table scans because there is some text parsing work at play to get at key attributes.  The unfiltered query actually reports more expensive operations in the form of the hash match and is reported to be slightly larger in cost. 

In practice, though the unfiltered returns 100-200 rows in a second, the filtered query returns in between 1-5 minutes depending on how many locations are filtered for.

Data Set Filters are conceptually filtered 'client-side'.  In the case of Reporting Services, the 'client-side' is the Report Server.  It has to go out to the database server, pull 'all' data matching the query, then process and only display the ones that match the filter.  So more data is transferred between the two processes… and more work is done in the reporting server process.  Usually that is not ideal

In this situation, it was faster to get all rows, unfiltered, out of the database, make them available for reporting, and have the report decide which to display.