SSRS Parameterized Query Slow
We had a situation at a client where a few SSRS reports running through SharePoint were painfully slow (over 18 minutes), or worse, timing out after 30 minutes. These reports both had SSRS Parameters in the “WHERE” clause.
A simplified query to understand the syntax would be:
Select Column1, Count(*) as [Counts]
From TableName
Group By Column1
Where TextField = @TextParameterFromSSRS
If the queries were run in Management Studio, the results returned in 2 seconds and 6 seconds respectively. Some suggestions found on various blogs suggested things “to try” such as convert to using Stored Procedures, “because that would compile the code and make it more efficient”, or use index hints, or to add the OPTION (RECOMPILE) statement to the query.
Data Type Mismatch Might be the Problem
Then I came across a comment from Dan Guzman (an MVP on Microsoft SQL Server forums) that helped determine where to look for the answer: “ ‘Parameter sniffing’ applies to any parameterized query, not just stored procedures. But before you go down that path, ensure that the data type of @TextParameterFromSSRS exactly matches that of the underlying TextField column. Otherwise an implicit conversion may be needed, which can mitigate usefulness of indexes on the column.” (Field names changed to match the above example). A follow up comment stated “I also checked the data types. TextField is CHAR(2) and @ TextParameterFromSSRS is TEXT. So I suppose that shouldn’t be a problem…” But that is exactly the problem. Since the data types are not the same, the query optimizer does not know that it can use an index to filter the result set. To correct this, a data type conversion needs to be done.
One way to do this would be to do a conversion in the WHERE clause:
Select Column1, Count(*) as [Counts]
From TableName
Group By Column1
Where TextField = CAST(@TextParameterFromSSRS as CHAR(2))
It is not obvious here why a conversion is done, and in a complex query, this could go unnoticed by whoever has to maintain the report in the future. Another option, and one that I prefer, is to define a local variable within the SQL statement, and set the value of that variable to be equal to the SSRS parameter. Modify the Where clause to use the local variable:
Declare @TextVariable CHAR(2);
Set @TextVariable = @TextParameterFromSSRS;
–Set @TextVariable = ‘US’; /* used for testing */
Select Column1, Count(*) as [Counts]
From TableName
Group By Column1
Where TextField = @TextVariable
In this method, it is easy to see that a local variable is added, and that it gets its value from the parameter that is passed to the query. If an implicit conversion is needed, it is done at the beginning of the query, and the correct data type is used in the Where clause which allows any indexes to be used if appropriate. Note: I added the commented line to be able to test the query in Management Studio. Just comment or uncomment the appropriate line for testing or for use in SSRS.
The client asked “Do I have to do this for every report with Parameters?” The short answer, of course, is “No”, but since the painful response time may not happen until after the report is moved to production, or until after some time has passed, and since this is a simple technique to correct this issue, a better answer might be “Not required, but recommended.”
The original problem I had to look into was dealing with Date parameters. To close the loop with a query involving date ranges, we went with the following technique:
Declare @FromDate datetime,
@ToDate datetime;
Set @FromDate = @FromDateSSRSParm;
Set @ToDate = @ToDateSSRSParm;
–Set @FromDate = ‘2013-11-28’;
–Set @ToDate = ‘2013-12-25’;
SELECT
{List of columns}
FROM TableName
Where DateKey BETWEEN @FromDate and @ToDate
GROUP BY
{List of Columns}
This challenge can also occur in passing parameters to Stored Procedures, and the same corrective techniques apply in that situation as well.