SSRS using SP with option(recompile) still taking forever to load report embedded in asp.net ReportViewer

Question

I have a SSRS report.

When I open the report using SSRS browser page at http://localhost/reportserver the report works fine and loading data. Data is just 4 rows.

But when I embed the report in report Viewer, it takes forever to load means never loads just progress status showing.

I have added option(recompile) but still no result.

Following is my SP

 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select s.Name,s.Id as STUDENT_ID,a.AttendanceStatus,a.AttendanceDate,

  case when (select count(*) from
  Attendance where AttendanceStatus='Present')>
                           (select count(*) from Attendance where 
     AttendanceStatus='Absent') then 'Regular' else 'Not Regular' end as IsRegular
  from Student s 
  inner join Attendance a on s.Id=a.Student_ID
  where s.Id=@Student_Id

  option(recompile)

END

Following is my C# code that I used to get report in reportViewer

    ReportViewer1.ServerReport.ReportServerUrl = new System.Uri("http://localhost/ReportServer");
        ReportViewer1.ServerReport.ReportPath = "/Report1";
        ReportViewer1.ProcessingMode = ProcessingMode.Remote;


        Microsoft.Reporting.WebForms.ReportParameter[] Param = new Microsoft.Reporting.WebForms.ReportParameter[1];
        Param[0] = new Microsoft.Reporting.WebForms.ReportParameter("Student_Id","1");



        ReportViewer1.ShowParameterPrompts = false;
        ReportViewer1.ServerReport.SetParameters(Param);

In absence of other details (execution plan and index information), I would ask you to try fix for parameter sniffing. Can you try declaring @Student_Id_loc and assign it with @Student_Id and use @Student_Id_loc in your query. Remove option(recompile) — AB_87
also you can look into executionlog3 table in reporting server and check where does it take time. You have TimeDataRetrieval, TimeProcessing and TimeRendering columns which will tell you what needs fixing. — AB_87
These timings are for the execution of your report from ReportViewer in your application? If yes, then looks like report executed fine. May be some other issue within your application. — AB_87
@AB_87 sorry I deleted the comment. There is no row in log for the report means report is not hitting. My report is on my desktop and I am giving reportpath as "/Report1.rdl" my reporting server is in native mode. any idea how I can fix this? — user786
I may be wrong but I don't think you can run rdl report like that from your desktop. As you mentioned you can already deployed it to your local server so you can execute it from localhost server. May be with code something similar to below post. You can also provide your c# code in your question and I am sure experts will be able to help. stackoverflow.com/questions/11465530/… — AB_87

Pouya Kamyar Pouya Kamyar · Accepted Answer · 2017-10-08T10:26:13

I don't know any logic behind it but i had the same problem, declare a new variable in your SP and put the value of "@Student_Id" in it at first, then use this new variable in your queries

hope it works for you too

SSRS using SP with option(recompile) still taking forever to load report embedded in asp.net ReportViewer

1 Answers