After my colleague brought some performance issues to my attention, we thought further investigation was required. After discovering Multi-Select Parameters for SSRS reports, we set out to add this to as many reports as possible, as it really is a cool feature.

We always use stored procedures to extract data for reports as a development standard, which brings a complication to Multi Select Parameters. When more than one option is selected in the parameter drop down the values are concatenated into one string, delimited with a comma. If you are using "SELECT" statements in your report to extract data this works well, as you can simply use an "IN" statement in your "WHERE" clause as it will get concatenated into the correct format. If you use a stored procedure however the concatenated string will be considered a single value, and no records will be

returned.

After some searching we found some sample code for a SQL function that takes the concatenated string and splits it into a table, allowing a "JOIN" statement onto the values in the concatenated string. This works very well from a coding perspective, but unfortunately has major performance penalties. We tested this method compared to using a direct "IN" statement and the difference was very big. The "IN" approach completed execution in just over 10 seconds, where the computed table executed over 6 minutes. A heads-up on the "IN" statement, string selections do not have the enclosing single quotation marks and you will need something like:

IN (''' + RTRIM(REPLACE(CONVERT(NVARCHAR(4000), @MultiSelectParameter), ',', ''',''')) + ''')...

I then tried inserting the computed table into a "#TEMP" table before joining it to the main table; this put us to about 3 minutes of execution. Much better but not good enough if compared to 10 seconds. This puts us back to where we started. (We worked off a single table, with a fairly large number of rows, if joins are already being used to other tables the impact of the computed table might be less prominent)

This leaves one more option: the controversial dynamic SQL code.

We tried to avoid using sp_executesql (or its various derivations) as far as possible, but in this situation it seems to be the best option. Two reasons why we feel that dynamics SQL statements are acceptable here are:

• The interface to the stored procedure does not allow for SQL injection text as only selections from a defined list is allowed
• Recompiling of the statement has a negligible impact on the system as the report is run infrequently

Apart from the technical reasons for allowing dynamic SQL statements, the overall duration improvement really makes this a good option.

One limitation on the dynamics SQL method to consider is the maximum length of your SQL string. In our case the user could choose from a small range of options, each two characters long. This allowed us to use NVARCHAR(4000) for the SQL string, if you could possibly have a large number of options, or possibly lengthy options you will have to revert to a NTEXT data type, making the dynamics SQL a lot more complex and impractical.

So what we have ended up with is a stored procedure that builds a dynamic SQL "SELECT" statement and executes it in the best performing way we can find!

Happiness!