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.

Answer 1

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.