We recently ran into an interesting issue related to SQL Server and Microsoft Reporting Services (RS). It is a SQL issue but is most likely to occur when using RS.
A client was complaining about RS reports taking way longer than they should to run. One report in particular asks for a work order number and then displays the data for only that work order. It makes a couple relatively straight-forward SQL calls and returns about 5 rows. It was taking 3-4 minutes to run. We tried the report on a development virtual and it was taking 30 seconds or so.
We tracked it down to the way RS calls SQL with parameterized queries.
The query in question in our report looked like this:
SELECT MANUFACTUREORDER_I, Quantity FROM vwRouteSeqInProcess WHERE MANUFACTUREORDER_I = @WorkOrder
When we run the query above in SQL Server Management Studio (SSMS) specifying a value in place of @WorkOrder it runs in less than a second. However, when the RS report renders it sends the following command to SQL Server:
exec sp_executesql N'SELECT MANUFACTUREORDER_I, Quantity FROM vwRouteSeqInProcess WHERE MANUFACTUREORDER_I = @WorkOrder',N'@WorkOrder nvarchar(13)',@WorkOrder=N'WO000017855-3'
This works. However, it takes quite a bit longer. We tried running that exact command in SSMS and it took 17 seconds. Compare that to under a second for the first query above. Quite a difference!
The reason for the difference is that RS treats string parameters as NVARCHAR when creating the parameterized query. However, in our case this field is a CHAR in the source table. There are a lot of different factors that may affect how much performance impact this may have.
In our situation we didn’t want to change the source table so we worked around the issue by wrapping the select statement in a stored procedure and using this in the RS Report. The report now renders within a second or two.
Here is a link to the relevant Microsoft article: