none
SSRS report displays slow for the first time search

    问题

  • Hi,

    The SSRS report is running slow for the first time when i click "View Report" and for the next search criteria it just takes a sec to view the report. Can anyone help me on why the delay is  happening for the first time ?. Thank You.

    Jenni !!

    2012年3月22日 21:15

答案

  • Hi Jenni - Its hard to say for sure but it is likely related to caching that benefits the 2nd and later executions. SQL Server stores query plans and data pages in in-memory caches, this could affect the perf time of the RS service's queries against the catalog database to load your report. It could also affect the perf of your report's dataset queries when they are executed by SQL Server to return the report data. If you are running in Sharepoint it could have the same effect there. And finally, the report itself may be set up to cache its data in the RS database for future executions.

    There are a couple ways you can isolate these components, depending on what access you have to these components. You can query the ExecutionLog views in the ReportServer database, it will have an entry for each time you run the report. You can review the values in the "TimeDataRetrieval" column for your report, this shows the time spent executing the report's dataset queries. If its longer in the first and shorter in the later executions, its the caching at the report's data source.

    If you can view the Report in Report Manager or Sharepoint, you can see if it is set up for caching.

    I hope this helps, Dean

    2012年3月23日 2:06

全部回复

  • Hi Jenni - Its hard to say for sure but it is likely related to caching that benefits the 2nd and later executions. SQL Server stores query plans and data pages in in-memory caches, this could affect the perf time of the RS service's queries against the catalog database to load your report. It could also affect the perf of your report's dataset queries when they are executed by SQL Server to return the report data. If you are running in Sharepoint it could have the same effect there. And finally, the report itself may be set up to cache its data in the RS database for future executions.

    There are a couple ways you can isolate these components, depending on what access you have to these components. You can query the ExecutionLog views in the ReportServer database, it will have an entry for each time you run the report. You can review the values in the "TimeDataRetrieval" column for your report, this shows the time spent executing the report's dataset queries. If its longer in the first and shorter in the later executions, its the caching at the report's data source.

    If you can view the Report in Report Manager or Sharepoint, you can see if it is set up for caching.

    I hope this helps, Dean

    2012年3月23日 2:06
  • Hello,

    There may be the reason that the next time you execute the query the execution plans are stored in cache. So it takes lesser time. To optimize your query please post it here.

    2012年3月23日 7:11
  • I confirm that RS 2012 is very slow.  Logging into ReportManager is deathly slow as well as the first run of a small/basic report.  No offense, but those saying "have you tried this or that" are obviously not using RS 2012 and just being kind and trying to help.  I'm going back to RS 2008 until this is fixed.
    2012年5月1日 2:37