locked
Report taking a long time to render RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, I have a single query with no WHERE clause or other, just SELECT * FROM TABLE

    The problem with this table is that its result takes about 1.5M rows, and when I preview the result it says Memory limit is exceeded.

    I deployed the report to the Reporting server, and when I open it in the browser, it just doesn't load. 

    I opened it 3.5 hours ago, and it still doesn't show anything. It used to show a "Loading" message, and after 2 hours it just shows a blank page.

    Any ideas about this? Thanks!

    Tuesday, October 18, 2016 7:08 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sant016 - that is a fairly large rowset, you might first look at limiting your query via filters or parameterizing your report & query to subset the data. 

    If you need to ultimately pull down that size of rows, you will likely need to

    (1) increase the query and report execution timeout settings 

    (2) consider setting up a report or dataset caching plan, that schedules execution async and can run for hours if needed

    (3) run on a 64 bit version of server with sufficient RAM 

    Microsoft SQL Server Reporting Services

    • Proposed as answer by Xi Jin Wednesday, October 19, 2016 6:07 AM
    Tuesday, October 18, 2016 11:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi sant016,

    I just want to make some complement based on JonHP's reply.

    First you should know that the total time to generate a Reporting Service report can be divided to 3 elements:

    1.Time to retrieve the data (TimeDataRetrieval).
    2.Time to process the report (TimeProcessing)
    3.Time to render the report (TimeRendering)  

    You can check these elements in the Executionlog3 table in the Report Server database. Based on these 3 elements you can see which part costs the most time of your report rendering and optimize it.

    Since in your scenario, you issue can be related to the massive records of your DataSet. Then you can set the query and report execution time-out on Report Manager. Select the DataSet you used in Report Manager -> go to manage -> increase the Query time-out in seconds under Properties. Also, select the report -> go to manage -> change Report Timeout under Processing Options to Do not timeout report. 

    Reference: Setting Time-out Values for Report and Shared Dataset Processing (SSRS) 

    Reporting Services Performance and Optimization

    http://www.keepitsimpleandfast.com/2011/07/more-tips-to-improve-performance-of.html 

    If you still have any questions, please feel free to ask.

    Thanks,
    Xi Jin.


    • Proposed as answer by Xi Jin Monday, November 7, 2016 8:33 AM
    Wednesday, October 19, 2016 6:41 AM