SSRS Query Performance Issues with Parameters

I recall seeing “fast queries in SSMS but slow in SSRS” issues reported a few times in the public discussion list but I’ve always dismissed them as unrelated to SSRS. Alas, I happened to see one today when a report would take a very long time (7 min) to execute. As usual, the first stop was to take a look at the SSRS Execution Log which showed that all the time was spent in data retrieval. However, executing the report query in SSMS would take a few seconds. In this case, the report had a complex free-text T-SQL SELECT statement that referenced a single report parameter several times in the query.

The workaround we found at the end was to declare a variable at the beginning of the query that referenced the parameter once, as follows:

DECLARE @invoice_number as int

SET      @invoice_number = @inv_parameter

Then, we referenced the variable in the report query instead of the query parameter. This solved the mysterious query performance issue which appears to be a bug with parameter passing in the SQL Server dataset extension. In this case, the customer had SQL Server 2008. The issue might be fixed in SQL Server 2008 R2, I haven’t checked.