100% cpu and over 1GB of memory for web server when performing ExportToExcel

[Covertix]

Hello all,

I've implemented an export all pages to Excel in my grid. My grid uses custom paging, template columns and is dynamically created on the server side.
I have a customer that tried to export all pages (80000 rows, not very big of a number) to Excel and his web server went to 100% CPU with over 1GB of RAM consumed for over 10 minutes. Then the CPU returned to normal but the memory stayed there for additional 20 minutes.Below please find the code snippet:

RadGrid GridReport = (RadGrid)GridPlaceHolder.FindControl("GridReport");

        if (GridReport.MasterTableView.Columns.FindByUniqueNameSafe("ObjectName") != null)
        {
            GridReport.MasterTableView.Columns.FindByUniqueName("ObjectName").Visible = false;
            GridReport.MasterTableView.Columns.FindByUniqueName("ObjectNameText").Visible = true;
        }

        //GridReport.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
        GridReport.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
        GridReport.ExportSettings.IgnorePaging = true;
        GridReport.ExportSettings.OpenInNewWindow = true;
        GridReport.ExportSettings.ExportOnlyData = true;


        if (cbExportAllPages.Checked == true)
            GridReport.PageSize = GridReport.MasterTableView.VirtualItemCount;

        GridReport.DataSource = reportResult(Session["sqlStr"].ToString());
        GridReport.Rebind();

        GridReport.MasterTableView.ExportToExcel();

I searched all over the forums, saw that other people had this issue, but couldn't find a vaible solution.
Tried IgnorePaging flase and true. Tried Format of HTML and ExcelML. nothing.
The ExportToExcel() function simply kills the website.

Anyone has an idea?

Thanks,
T.

[Antonio Stoilkov]

Hi Tzach,

We strongly recommend not to export large amounts of data since there is a chance to encounter an exception (Timeout or OutOfMemory) if more than one user tries to export the same data simultaneously. RadGrid is not suitable for such scenarios and therefore we suggest that you limit the number of columns and rows. Also it is important to note that the hierarchy and the nested controls have a considerable effect on the performance in this scenario. 

In such scenarios you could set the AllowPaging="true" and the PageSize. Additionally, set the IngnorePaging to false. Note that you could alter the PageSize in order to modify the performance.

All the best,
Antonio Stoilkov
the Telerik team

[Covertix]

Hi Antonio.

Thank you for your answer.
The Grid that my client tries to export has 14 columns. No hierarchy or nested controls. The client want to export 80000 rows , which in an enterprise class application is not that large of a number, and still the performance is very bad. My client needs a solution (as he can't work) and currently I'm looking for other third party controls to provide him with one. 
I think you should really consider either rewriting the export function or remove it altogether, as current export capability is simply not suitable for medium to large organizations.

Thank you,
Tzach.

[Antonio Stoilkov]

Hello Tzach,

Unfortunately there is little that can be done when trying to export large amounts of data. RadGrid export performance depends on the number of rows and columns. Therefore, our recommendations in such scenarios are lowering either the number of rows by not ignoring paging or the columns by hiding them will significantly improve the current performance.

However, I can suggest you to try exporting in ExcelML format instead of HTML. I doubt it will affect the usability much but the measured time should be better.

Alternatively you can create another RadGrid which exposes the important data only (for example: 8000 items instead of 80000) . You can also use Progress indicator to improve the user experience.

Regards,
Antonio Stoilkov
the Telerik team