SQL is slower in SSRS but faster 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 at almost zero time. It also runs quickly in the SSRS query designer when I pass it some values. But when I look at the report, it takes at least two minutes to complete the request. I'm not sure what is going on here, I have never had such a problem with SSRS before.

Answer 1

As discussed in the comments, let me get rid of the parameters to see if your request affects the sniffing parameter.

SQL . SSRS , SQL-, . -, JOIN.

, SQL " " ( ) fx, , :

="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 "

, , - SQL , , , snuffing .

, , , .

Answer 2

, , .

, .

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

, ,

Option(Recompile);

, .

, , ... , , tablix, " ". , 70 7.

Answer 3

, , , ( , SSRS ). , , . , , , .

Answer 4

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