SQL runs slow in SSRS, but fast in SSMS

Question

I have this query:

Select 
    '<ALL>' as name, 
    '<ALL>' as pid, 
    '<ALL>' as type
union all
Select distinct 
    instructor.name as name, 
    instructor.Pid as pid, 
    instructor_type as type  
From sisinfo.dbo.SISCRSI instructor
inner join section_info as section 
    on section.sctn_id_code = instructor.sctn_id_code
Where section.sctn_term_code in (@Terms) 
    and section.subj_code in (@Subject)
order by name

When I run it in SSMS, it completes in pretty close to zero time. It also runs fast in the SSRS query designer when I feed it some values. But when I preview the report, the query takes at least two minutes to run. I'm not sure what is going on here, I've never had this kind of a problem with SSRS before.

Have you turned on SQL Profiler while you preview the report? Or make a copy of your report and replace all layout items with a signle static item to eliminate processing and rendering as culprits. There are three components to a report: data retrieval, processing, and rendering. Iif you deploy your report out to a report server, you can view these individually in the execution log of the Reporting Services database for that server. — Kyle Hale, May 30 '14 at 20:57
`in (@Terms)` and `in (@Subject)` won't actually work correctly for multi valued parameters is that your actual query? — Martin Smith, May 30 '14 at 22:21
You might have an issue with [parameter sniffing](http://stackoverflow.com/questions/16924144/why-does-the-2nd-t-sql-query-run-much-faster-than-the-first-when-called-by-repor/16929881#16929881) — Chris Latta, May 30 '14 at 23:09
I think parameter sniffing is only ripe for stored procedures. If you are not using a stored procedure then your query is executed as inline sql. I would delete all *.data files from your project or force something in the ide to fetch new data. — Ross Bush, Jun 01 '14 at 01:54
@lrb For a long time I thought parameter sniffing only affected stored procedures as well until I discovered it affecting a straight SQL query in SSRS, which I solved by using local parameters as per the answer in the link in my comment above. When something looks so much like parameter sniffing problems as this does and the workaround is easy to test, you can't dismiss parameter sniffing as the cause until you have actually tested to make sure it isn't. — Chris Latta, Jun 02 '14 at 01:06
@ChrisLätta: I have two multivalue parameters in this SQL. How would I go about storing those in an internal variable? — Michael Robinson, Jun 02 '14 at 13:19
@MichaelRobinson Let's get rid of the parameters altogether to see if that helps. I'll add the technique as an answer as it won't fit into a comment. — Chris Latta, Jun 03 '14 at 01:31

score 8 · Answer 1 · answered Jun 03 '14 at 02:01

As discussed in the comments, let's get rid of the parameters to see if your query is getting affected by parameter sniffing.

To do this we build the SQL statement from scratch. Most things in SSRS can be expressions including the SQL query so you can build it as a string. With the parameters, we'll convert them to a comma delimited list using JOIN.

So for your SQL statement, go into the Dataset Properties dialogue (not the query editor) and press the fx expression editor button to edit the query expression as text and make it be an expression as per below:

="Select '<ALL>' as name, '<ALL>' as pid, '<ALL>' as type "
&"union all "
&"Select distinct instructor.name as name, instructor.Pid as pid, instructor_type as type " 
&"From sisinfo.dbo.SISCRSI instructor "
&"inner join section_info as section on section.sctn_id_code = instructor.sctn_id_code "
&"Where section.sctn_term_code in (" & Join(Parameters!Terms.Value, ",") & ") "
&"and section.subj_code in (" & Join(Parameters!Subject.Value, ",") & ") "
&"order by name "

What we have done here is turn the SQL expression into a string where we supply the multivalue parameters as strings rather than parameters, thereby eliminating parameter sniffing as a cause of a slow running query.

If your query is slow after this, you know it isn't parameter sniffing that is the problem but at least you will have dismissed it as a cause.

score 3 · Answer 2 · answered Oct 07 '14 at 11:40

There's a couple of options I tried when I came across the same thing.

First was to change the parameters for variables in the stored procedure, and then use those new variables in your query.

Declare @TermsNew DataType = @Terms
Declare @SubjectNew DataType = @Subject
Select '<ALL>' as name, '<ALL>' as pid, '<ALL>' as type
union all
Select distinct instructor.name as name, instructor.Pid as pid, instructor_type as type  
From sisinfo.dbo.SISCRSI instructor
inner join section_info as section on section.sctn_id_code = instructor.sctn_id_code
Where section.sctn_term_code in (@TermsNew) and section.subj_code in (@SubjectNew)
order by name

Another option that has already been suggested here is to add

Option(Recompile);

at the end of your query to recompile the execution plan.

The thing that worked for me though having tried the above... If you're pulling big result sets make sure that in the tablix properties that it is not set to "Keep results on one page if possible". I turned that off and my report went from 70 seconds to 7.

Thanks, this worked for me: (1) putting the report query into a stored procedure, and (2) making the stored procedure use local variables, setting them to the passed parameters. Also note that in order for Reporting Services to execute the stored procedure, I had to change the datasource to use read-write credentials, instead of read-only credentials. — Doug_Ivison, Oct 21 '15 at 23:19

score 1 · Answer 3 · answered Jun 03 '14 at 12:10

Actually, it turns out that this query was not the one bogging down, it was another one that was running at the same time (I think SSRS can run multiple queries at once). I thought it was the posted one as it was populating a field. Thanks for all the suggestions, I will probably wind up using them in the future.

score 0 · Answer 4 · answered Jun 03 '14 at 03:27

0

This sounds like parameter sniffing to me - I see this all the time when the SELECT is at all complex. I would add the OPTION (RECOMPILE) hint to the end of your SQL.

answered Jun 03 '14 at 03:27

Mike Honey

14,523
1
24
40

SQL runs slow in SSRS, but fast in SSMS

4 Answers4

Linked