SSRS report running very slow whereas the query runs super fast in SSMS

Question

I am running a very basic report in SSRS 2012. I am just retrieving some data from a table and I am using a parameter in the Where clause of the query. The query runs fast (in less than 5 secs) if I hardcode the parameter in the SSRS query but if it's left as a dynamically chosen parameter the query takes over 5 minutes to render. Here's an abstract query similar to what I am using:

Select Col1, Count(*)
From Tbl1
Where Col2 = @Para1
Group By Col1
OPTION (RECOMPILE);

I have already tried using OPTION (RECOMPILE) in the query to address the parameter sniffing issue. I have also checked the data types and Col2 is CHAR(2) and Para1 is TEXT so no conversion should be required at runtime.

Any thoughts what could be causing this?

Answer 1

I had similar problem in a report which uses a query to load data. In my case I had to support development patterns established by architecture team (use direct queries in SSRS DataSets). I could not use filters because the volume of unfiltered data and security requirements (extract only requested data from DB).

So my solution was to attack conversion. I declared table variables, filled these variables with converted selection values coming from SSRS parameter and used these table variables in the WHERE … IN (…) expression. This covered all requirements query only, limited data amount, secure data access and solved NVARCHAR to VARCHAR conversion slowdown.

Solution in the query looks like this (@pSelectedValues is variable mapped to selection parameter in report, it already existed before my changes):

DECLARE @tMySelectedParameterValues TABLE( SelectedValue varchar(255) NULL );

INSERT INTO @tMySelectedParameterValues

SELECT DISTINCT EntityStatusStatusName
FROM      dbo.t_MyReferenceData
WHERE     ValueForParameters IN (@pSelectedValues);

... main selection and from parts ....

WHERE keyValues IN (SELECT SelectedValue FROM   @tMySelectedParameterValues )

Answer 2

I experienced the same issue. I ran the SQL Server Profiler Sniffing queries to SQL Server to determine where the report is slow and found a query running 20 seconds. The query is wrapped in exec sp_executesql. I copied the query to SSMS and found it to run 20 sec. Converting the query to straight SQL made it run 1 sec. AhHah!

Returning to exec sp_executesql sql, there was an IN Clause as expected:

operid IN  (N''XICL002'',N''XICL005'',N''XICL026'',N''XICL028'...

operid is decared varchar(32) while the N prefix forces the parameters to NVARCHAR. Removing the N made the exec sp_executesql sql run in 1 sec.

In my case I was able to modify the report. The IN clause was populated by a multi-select parameter (p_operators). I changed the report query to get the operid list from a view using the parameters that generated the p_operators list.

operid IN (SELECT oper_id  FROM foo WHERE (foo1 IN (@p_foo1)) AND (foo2 IN (@p_foo2)) AND ...

Basically, the conversion of a parameter from NVARCHAR to VARCHAR is killing the performance. Seems like SSRS should provide the option of indicating a parameter's datatype - not just "text". Also some logging of the queries in dev mode would be nice as well.

(I hope I provided enough info to be usefull. Due to corp policies, I can't write too much)

Answer 3

I've had a similar problem, but only when connecting to an Oracle database. When I hardcode the params in the query....works great.....pass a parameter to the query and it would take forever. The weird thing is it would only take forever for certain parameters. In my case it was the rendering time that was taking forever when I checked the logs. (should also mention this was a multi-select param...had no way of getting around that)

Strange solution:

Instead of passing the parameter into the query, I passed it into the dataset as a filter. One would think this would only increase the load on the ssrs server since you're sending more data back across the wire for it to deal with...thus increasing the rendering time, but the difference is night and day. The report runs great now.

Honestly can't explain it.

I've written more reports than I could count against SQL Server data sources, and I've only seen this (and a few other strange things) when connecting to Oracle. But might be worth a try if you're having the same problem, and the parameter you're applying doesn't actually limit the data all that much. (most people in my case were running 'all' anyway.

if you're not familiar with using filters on data sets. Go to this link and look for the heading "Using Filters with Multiple Value Parameters" (applies to non-multi-value params as well) http://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/

Answer 4

Three things:

1. Why do you have to use a Recompile? Unless your plan you specifically want to rethink it's execution everytime you don't need that. Espeically if you are stating your query is simple. SSRS does it's own interpretations overtop of the SQL so adding more things for it to do you are only asking for trouble. If you had to do a recompile you may wish to put that in a proc so all the logic is encapsulated for the SQL engine to understand.

2. Parameters at times can slow down anything is the query with a parameter in SSMS slow too? You mentioned it was fast hard coded but not testing it in SSMS with the parameter. I have seen at times when parameters were slow on SSMS.

3. You can 'cheat' the parameters part of SSRS with a little know how. You can build an expression that runs at runtime in extreme cases where parameters are particularly bad. On your dataset option click the 'fx' for expression and then do this:

   ="Select Col1, Count(*)
   From Tbl1
   Where Col2 = " & Parameters!Para1.Value
   

As long as your type is text, this should yield a legitimate string that is then evaluated at runtime. The one caveat is your fields may not self populate first, so you may wish to do the actual query, have them auto populate, then return and put the function in. Otherwise you would have to set the fields manually on the left pane of the dataset that states 'fields' which is a pain IMHO.