none
report with subreports - development box vs. server box - execution time issue

    Question

  • hello,

    one main report developed on my workstation contains 10 sub-reports. The execution time on the development box in Visual Studio and the deployed version is about 12-15 seconds. Once the reports were deployed on the production server the execution time is about 3-4 minutes. I am using SQL 2008 R2 with SP2 recently installed. The reports hit one medium cube and one individual MDX statement is executed in 1 second on both environments (dev and prod).

    So if each MDX statement has the same execution time on both envs, what to check on the server side for the difference between 4 min and 15 seconds ?

    Thank you for any hint/URL/doc!

    Few details about the 2 environments:

    - SSRS is installed with SharePoint on the production box and in a standalone mode on the dev box.



    • Edited by dagriq Wednesday, May 15, 2013 6:31 PM
    Wednesday, May 15, 2013 6:18 PM

All replies

  • Hi Dariq,

    Report execution takes into account various items apart from query execution. These include Data_Retrieval time, response time for the server etc.

    Do check this blog to get an idea to understand these times with using fiddler -

    http://www.mssqltips.com/sqlservertip/2328/sql-server-reporting-services-reports-performance-debugging-and-analysis/

    Also do check ReportServer logs which gives you more detailed information on these things per report basis and see for the TimeDataRetrieval, TimeProcessing, TimeRendering values-

    http://stackoverflow.com/questions/3596311/ssrs-runs-report-for-30-sec-however-it-takes-only-3-sec-in-sql-studio

    Karthik


    • Edited by V Karthik Thursday, May 16, 2013 9:05 AM
    Thursday, May 16, 2013 9:04 AM
  • thanks for the two links.

    The second link mentions one specific property [InteractiveSize] , in my case it is set to 8.5in|11in - I mean the main report, the report contains 10 subreports each one on its on page. What values do you suggest ?

    I can analyze the ExecutionLog3 view in SSMS and what I found is the TimeProcessing value is huge on the server box comparing it with the dev box. SSRS process computes something during the TimeProcessing period, how can I tune it ?

    Thanks!


    • Edited by dagriq Thursday, May 16, 2013 2:35 PM
    Thursday, May 16, 2013 1:29 PM