SSRS Export to Excel taking too much time RRS feed

  • Question

  • Hi

    We have following statistics from DB

    Database Query Performance Statistics:

    Time taken by query:

    Parameters: Default as ‘All’

    No. Of Records: 300+ thousand

    Report Execution time : 2-3 minutes

    Exporting to Excel time : 5- 10 min

    The size of the Excel(with 2 tabs): 45 MB

    Number of Rows in both sheets : 65thousand

    Number of Columns in both the sheets : 119 columns

    as we have one column in the columns which is a tablix the number of columns can be dynamic and the given number of columns is maximum.

    Need to reduce the export time , is there any solution for this.

    SSRS Version 2009.

    RAM Size :24GB

    Thanks in advance.

    Friday, December 1, 2017 11:36 AM

All replies

  • Hi RajeshBabuM,

    According to your description, you have large data and will take long time to Export.

    Based on my research, report rendering occurs after data and layout are combined into an interim format and then passed to a rendering extension. Rendering time can be affected by the amount of data, the number of instances of report items, and paging. As for excel, which was processed by a powerful and complicate engine, which provide brilliant appearance and rich features, as a result, it would cost longer time to export so large quantity of data. We can use following steps to help improve rendering performance for a report:

    1.Do not merge cells in a Tablix data region.
    2.Consider using page breaks in the report.
    3.Select a format that produces a smaller file. Since CVS file is just a flat text file, it has a fast write speed, we can export the report to csv format and then open it with excel.

    You could refer to Troubleshooting Reports: Report Performance and this blog for details.

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Visakh16MVP Monday, December 4, 2017 2:42 AM
    Monday, December 4, 2017 1:34 AM