Post reply

Sub report is taking forever to run in Reporting Services.

  • June 25, 2018 at 10:50 am

    #352275

    I have a Main report and inside the main report there are three sub reports. One of the sub report is pulling 53,000 records. The other two sub reports are just the summary reports.

    When I run the main report in ssrs development tool, it runs fine and executes all three reports. It takes just few minutes to run.

    However, when I run main report online in reporting services, the subreport with 53,000 records takes forever to run.

    I checked the parameters. I don't know what is causing this.

    Any suggestion would be greatly appreciated! Thanks

  • June 27, 2018 at 1:57 pm

    #1995514

    Hello.  I'm not surprised that the report runs fast in VS because the data are cached.  Before we start talking about tuning, I'd like to help you think through whether or not sub reports are a good idea.  I've seen lots of performance issues because of them which were improved after moving to a single query and organizing the data properly in a table. Is this an option for you?

    What I would do (especially if I wanted to keep the report as-is) is make sure I knew where the performance issue is.  Is it the SQL Server or the reporting server?  Use Extended Events or Trace to look at the SQL coming in along with the metrics.  Maybe the issue is the query (or queries).  Tune those up if you can.  You may need to look through some execution plans and possibly index recommendations.  To see what the rendering time is for SSRS, look at dbo.ExecutionLog3 in the report server database.  This table will help you see what part is running slow (i.e. SSRS or the SQL Server).

    I'm curious to know where the performance issue is.

    Best wishes on this

  • June 28, 2018 at 11:02 am

    #1995690

    The issue is resolved. I made the interactive size and page size of the subreport same. I also unchecked "Keep together" option. Now the main report with large data subreport along with other mini subreport is executing fine online in Reporting Services.

    Below is the link

    https://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs

  • June 28, 2018 at 11:39 am

    #1995702

    good job.  glad you got it figured out!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply