Performance issue when exporting a large number of records to Excel - is it normal?

[Kelly]

I'm getting a performance issue when I try to export my grid to Excel.  The data source has about 67,000 records and the filtered result set has about 12,000 records.  Is it normal for the Excel export to run for 20 minutes without showing any results when there are that many records?  Is there anything in my code that I could tweak to avoid this issue?

	<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PinList.aspx.cs" Inherits="PinList"
	MasterPageFile="~/Admin/Masterpages/AdminPageWithMenu.master" Theme="MillenniumAdmin"
	Title="PIN List" %>
	<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
	<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
	<script type="text/javascript">
	function OpenRadWindowCreateNewPins()
	{
	var oWindow = radopen("Popups/NewPIN.aspx", null);
	oWindow.op
	oWindow.SetSize(600, 520);
	}
	//Reloads PIN Grid
	function OnClientClose(radWindow)
	{
	location.reload();
	}
	</script>
	</asp:Content>
	<asp:Content ID="Content2" ContentPlaceHolderID="PageTitle" runat="Server">
	PIN List
	</asp:Content>
	<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
	<telerik:RadWindowManager ID="winMgr" Skin="Outlook" Behaviors="Close, Move, Resize"
	Modal="true" VisibleStatusbar="false" runat="server" Width="520px" Height="450px"
	OnClientClose="OnClientClose">
	</telerik:RadWindowManager>
	<asp:Label ID="Message" runat="server" EnableViewState="false"></asp:Label>
	<telerik:RadGrid ID="PinGrid" runat="server" AutoGenerateColumns="false" AllowAutomaticDeletes="false"
	AllowAutomaticInserts="false" AllowAutomaticUpdates="false" ShowStatusBar="true"
	AllowMultiRowSelection="false" AllowPaging="true" PageSize="30" OnNeedDataSource="PinGrid_NeedDataSource"
	OnItemCreated="PinGrid_ItemCreated" AllowFilteringByColumn="true" OnPreRender="PinGrid_PreRender"
	OnItemCommand="PinGrid_ItemCommand" Width="90%">
	<ExportSettings>
	<Excel Format="ExcelML" />
	</ExportSettings>
	<MasterTableView AllowNaturalSort="false" CommandItemDisplay="Top" DataKeyNames="PINID"
	AutoGenerateColumns="false" AllowSorting="true">
	<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
	<CommandItemTemplate>
	<asp:LinkButton ID="lnlRebind" runat="server" OnClientClick="javascript:location.reload()"
	CommandName="RebindGrid" Visible='true'><img class="middle" alt="" src="../Images/Telerik/Refresh.gif" /> Refresh</asp:LinkButton>&nbsp;&nbsp;
	<asp:HyperLink NavigateUrl="#" ID="GenerateNewPins" runat="server" Visible='<%# NewMillennium.Authorization.UserHasPermission(NewMillennium.FunctionalityName.CreateEditReps) %>'
	onclick="OpenRadWindowCreateNewPins();return false;"><img class="middle" alt="" src="../Images/Telerik/new.gif" />
	Generate New PINs</asp:HyperLink>&nbsp;&nbsp;
	<asp:LinkButton ID="lnlExportToExcel" runat="server" OnClick="ExportToExcelClick"
	Visible='true'><img class="middle" alt="" src="../Images/Telerik/excel.gif" /> Export</asp:LinkButton>&nbsp;&nbsp;
	&nbsp;
	</CommandItemTemplate>
	<Columns>
	<telerik:GridBoundColumn HeaderText="PinID" DataField="PINID" UniqueName="PINID"
	SortExpression="PINID" ReadOnly="true" Visible="false">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn DataField="DateCreated" HeaderText="Date Generated" SortExpression="DateCreated"
	CurrentFilterFunction="Between" AutoPostBackOnFilter="true" UniqueName="DateCreated"
	DataFormatString="{0:M/d/yyyy}" AllowFiltering="false">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="PIN" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true"
	DataField="PIN" UniqueName="PIN" SortExpression="PIN" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="Program" CurrentFilterFunction="StartsWith"
	AutoPostBackOnFilter="true" DataField="ProjectName" UniqueName="ProjectName"
	SortExpression="ProjectName" DataType="System.String" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="Rep First Name" CurrentFilterFunction="StartsWith"
	AutoPostBackOnFilter="true" DataField="RepFirstName" UniqueName="RepFirstName"
	SortExpression="RepFirstName" DataType="System.String" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="Rep Last Name" CurrentFilterFunction="StartsWith"
	AutoPostBackOnFilter="true" DataField="RepLastName" UniqueName="RepLastName"
	SortExpression="RepLastName" DataType="System.String" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="Territory Name" CurrentFilterFunction="StartsWith"
	AutoPostBackOnFilter="true" DataField="TerritoryName" UniqueName="TerritoryName"
	SortExpression="TerritoryName" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn HeaderText="Used" AutoPostBackOnFilter="true" DataField="UsedString"
	UniqueName="Used" SortExpression="UsedString" ReadOnly="true" Visible="true">
	</telerik:GridBoundColumn>
	<telerik:GridBoundColumn DataField="OrderDate" HeaderText="Date Used" SortExpression="OrderDate"
	CurrentFilterFunction="Between" AutoPostBackOnFilter="true" UniqueName="OrderDate"
	DataFormatString="{0:M/d/yyyy}" AllowFiltering="false">
	</telerik:GridBoundColumn>
	</Columns>
	</MasterTableView>
	</telerik:RadGrid>
	</asp:Content>

	using System;
	using System.Collections;
	using System.Collections.Generic;
	using System.Configuration;
	using System.Data;
	using System.Data.SqlClient;
	using System.Web;
	using System.Web.Security;
	using System.Web.UI;
	using System.Web.UI.HtmlControls;
	using System.Web.UI.WebControls;
	using NewMillennium;
	using Telerik.Web.UI;
	public partial class PinList : MillenniumPage
	{
	protected void Page_Load(object sender, EventArgs e)
	{
	if (!Authorization.UserHasPermission(FunctionalityName.ViewRepList)) FormsAuthentication.RedirectToLoginPage();
	Common.SetGridSkin(PinGrid);
	}
	protected void PinGrid_PreRender(object sender, EventArgs e)
	{
	}
	/// <summary>
	///
	/// </summary>
	protected void PinGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
	{
	PinGrid.DataSource = PinData.GetPinList();
	Common.HideExtraFilterOptions(PinGrid);
	}
	protected void PinGrid_ItemCreated(object sender, GridItemEventArgs e)
	{
	Common.HideFilterButtonsForNewVersion(PinGrid, e.Item);
	}
	protected void ExportToExcelClick(object sender, EventArgs e)
	{
	string filename = "PinExport-" + DateTime.Now.ToLongDateString().Trim().Replace(" ", "-").Replace(",", "");
	this.PinGrid.ExportSettings.FileName = filename;
	this.PinGrid.ExportSettings.OpenInNewWindow = true;
	this.PinGrid.ExportSettings.ExportOnlyData = true;
	this.PinGrid.ExportSettings.IgnorePaging = true;
	foreach (GridItem commandItem in this.PinGrid.MasterTableView.GetItems(GridItemType.CommandItem))
	{
	commandItem.Visible = false;
	}
	this.PinGrid.MasterTableView.ExportToExcel();
	}
	protected void PinGrid_ItemCommand(object sender, GridCommandEventArgs e)
	{
	if (e.CommandName == RadGrid.FilterCommandName && e.Item is GridFilteringItem)
	{
	}
	}
	protected void DisplayMessage(string message, string cssClass)
	{
	Message.Text = message;
	Message.CssClass = cssClass;
	}
	}

[Kelly]

PS. The GetPinList() function in my code takes about 3 seconds to run - so it doesn't seem like the 20-minute performance issue would be related to that, unless something is going on behind the scenes that I don't know about (it's not loading the entire dataset for each row in the grid, correct?)

[Kelly]

FYI, I was able to fix this with 2 setting changes:

1) Switched to Html format instead of ExcelML for the excel file format. Not sure why, but one is apparently a lot faster than the other.

2) Switched to client-side exporting instead of server-side exporting. This doesn't affect the performance for an export that finishes normally, but it does affect the web server CPU usage if the user closes their web browser or goes to a different page while waiting for the export to finish. With server-side exporting, web server CPU usage remains at 100% pretty much permanently after the request has been cancelled.  With client-side exporting, web server CPU usage goes back to normal if the request is cancelled.

Public service announcement - when exporting the RadGrid to Excel, avoid exporting with ExcelML format, and avoid server-side exporting.  This issue completely froze my production web server yesterday and caused timeouts for every request (not just the PIN export).

[Peter]

Hi Kelly,

I am a software developer. Currently, I am evaluating the Telerik's RadControls and everything is looking fine till now. I tried to use your scenario, but I couldn't find how to export the data on client-side. Actually I couldn't find any documentation how to export on client-side. Could you share your know-how about this?
Did you perform any comparison between the ExcelML and the HTML export modes? Could you provide any statistics which show which mode give better performance?

Regards,
Peter

[Kelly]

I had to dig to find the client-side documentation too, here's my page using the client-side export.

With Html export my page with 67,000 records took 15 seconds to show the save/open dialog.  With ExcelML format the exact same export took 2 minutes to show the save/open dialog under good circumstances.  (With either method, the save/open dialog didn't show up at all after 20 minutes if the web server was at 100% CPU usage from a previous server-side export not finishing, so I think switching to client-side exporting is the most important.)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PinList.aspx.cs" Inherits="PinList"
    MasterPageFile="~/Admin/Masterpages/AdminPageWithMenu.master" Theme="MillenniumAdmin"
    Title="PIN List" %>

<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">

    <script type="text/javascript">
        function OpenRadWindowCreateNewPins() {
            var oWindow = radopen("Popups/NewPIN.aspx", null);
            oWindow.op
            oWindow.SetSize(600, 520);
        }

        //Reloads PIN Grid
        function OnClientClose(radWindow) {
            location.reload();
        }
        function ExportGrid() {
            var masterTable = $find("<%=PinGrid.ClientID %>").get_masterTableView();
            masterTable.exportToExcel('PIN_list.xls');
            return false;
        }
    </script>

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="PageTitle" runat="Server">
    PIN List
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <telerik:RadWindowManager ID="winMgr" Skin="Outlook" Behaviors="Close, Move, Resize"
        Modal="true" VisibleStatusbar="false" runat="server" Width="520px" Height="450px"
        OnClientClose="OnClientClose">
    </telerik:RadWindowManager>
    <asp:Label ID="Message" runat="server" EnableViewState="false"></asp:Label>
    <telerik:RadGrid ID="PinGrid" runat="server" AutoGenerateColumns="false" AllowAutomaticDeletes="false"
        AllowAutomaticInserts="false" AllowAutomaticUpdates="false" ShowStatusBar="true"
        AllowMultiRowSelection="false" AllowPaging="true" PageSize="30" OnNeedDataSource="PinGrid_NeedDataSource"
        OnItemCreated="PinGrid_ItemCreated" AllowFilteringByColumn="true" OnPreRender="PinGrid_PreRender"
        OnItemCommand="PinGrid_ItemCommand" Width="90%" EnableAjaxSkinRendering="true">
        <ExportSettings ExportOnlyData="true" FileName="PINList" IgnorePaging="true" OpenInNewWindow="true">
            <Excel Format="Html" FileExtension="xls" />
        </ExportSettings>
        <MasterTableView AllowNaturalSort="false" CommandItemDisplay="Top" DataKeyNames="PINID"
            AutoGenerateColumns="false" AllowSorting="true">
            <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
            <CommandItemTemplate>
                <asp:LinkButton ID="lnlRebind" runat="server" OnClientClick="javascript:location.reload()"
                    CommandName="RebindGrid" Visible='true'><img class="middle" alt="" src="../Images/Telerik/Refresh.gif" /> Refresh</asp:LinkButton>&nbsp;&nbsp;
                <asp:HyperLink NavigateUrl="#" ID="GenerateNewPins" runat="server" Visible='<%# NewMillennium.Authorization.UserHasPermission(NewMillennium.FunctionalityName.CreateEditReps) %>'
                    onclick="OpenRadWindowCreateNewPins();return false;"><img class="middle" alt="" src="../Images/Telerik/new.gif" />
                        Generate New PINs</asp:HyperLink>&nbsp;&nbsp;
                <asp:LinkButton ID="lnlExportToExcel" runat="server" OnClientClick="return ExportGrid()"
                    Visible='true'><img class="middle" alt="" src="../Images/Telerik/excel.gif" /> Export</asp:LinkButton>&nbsp;&nbsp;
                &nbsp;
            </CommandItemTemplate>
            <Columns>
                <telerik:GridBoundColumn HeaderText="PinID" DataField="PINID" UniqueName="PINID"
                    SortExpression="PINID" ReadOnly="true" Visible="false">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="DateCreated" HeaderText="Date Generated" SortExpression="DateCreated"
                    CurrentFilterFunction="Between" AutoPostBackOnFilter="true" UniqueName="DateCreated"
                    DataFormatString="{0:M/d/yyyy}" AllowFiltering="false">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="PIN" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true"
                    DataField="PIN" DataFormatString="{0}&nbsp;" UniqueName="PIN" SortExpression="PIN"
                    ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="Program" CurrentFilterFunction="StartsWith"
                    AutoPostBackOnFilter="true" DataField="ProjectName" UniqueName="ProjectName"
                    SortExpression="ProjectName" DataType="System.String" ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="Rep First Name" CurrentFilterFunction="StartsWith"
                    AutoPostBackOnFilter="true" DataField="RepFirstName" UniqueName="RepFirstName"
                    SortExpression="RepFirstName" DataType="System.String" ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="Rep Last Name" CurrentFilterFunction="StartsWith"
                    AutoPostBackOnFilter="true" DataField="RepLastName" UniqueName="RepLastName"
                    SortExpression="RepLastName" DataType="System.String" ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="Territory Name" CurrentFilterFunction="StartsWith"
                    AutoPostBackOnFilter="true" DataField="TerritoryName" UniqueName="TerritoryName"
                    SortExpression="TerritoryName" ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn HeaderText="Used" AutoPostBackOnFilter="true" DataField="UsedString"
                    UniqueName="Used" SortExpression="UsedString" ReadOnly="true" Visible="true">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="OrderDate" HeaderText="Date Used" SortExpression="OrderDate"
                    CurrentFilterFunction="Between" AutoPostBackOnFilter="true" UniqueName="OrderDate"
                    DataFormatString="{0:M/d/yyyy}" AllowFiltering="false">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>
    <div id="div_processing">
        <h3>
            Exporting PINs</h3>
        <p>
            Exporting PIN list ... please wait.</p>
        <p>
            <center>
                <img src="../images/Telerik/progress.gif" /></center>
        </p>
    </div>
    <div id="overlay">
    </div>
</asp:Content>

[Peter]

Hi Kelly,

Thank you for the statistics. I suppose the HTML mode could be faster, because the ExcelML conversion in general should be created after the grid creation. Anyway, I am just sharing my thoughts.

I tried your scenario, but I am pretty confident that the export is still performed on server-side. I think the suggested method is a client method which calls a server Export method to perform the exporting of the RadGrid's data. In other words the exporting is still depending on the server, and does not export the data depending on the rendered HTML mark-up code. That's why I believe that there should be no difference between client "exportToExcel()" method and the server one. As I mentioned I couldn't notice the described difference.

Regards,
Peter

[Kelly]

I agree the client-side and server-side export both depend on the server - I think they called it "client-side" because it's initiated on the client.

The behavior I was seeing with the server-side export is this:
1. Start a server-side export while monitoring CPU usage on the web server - web server CPU usage goes to 100%
2. While it is exporting a large number of records, in the web browser, click on a link to a different page and/or close the web browser - web server CPU usage remains at 100% even though the export request has been cancelled.  At this point my users were re-trying the export and that would make it even worse - it basically crashed my web server and caused timeouts on every page in my website.

The behavior I saw with the client-side export is:
1. Start a client-side export while monitoring CPU usage on the web server - web server CPU usage goes to 100%
2. While it is exporting a large number of records, in the web browser, click on a link to a different page and/or close the web browser - web server CPU usage goes back to normal.

[Peter]

Hi Kelly,

Thank you for confirming my thoughts. I used the depicted scenario to create a test project which uses the both approaches. I bound the grid with 10000 rows. When I perform the export with the ExportToExcel (server-side) method and close the web browser, CPU usage stays 100% just for a few seconds more. After that it goes to a normal usage. If I export with the exportToExcel (client-side) method I noticed that there is a just a second difference in favor to the client-side method for exporting. So I still cannot replicate the described scenario. Maybe I am not doing something correctly in order to replicate this scenario. Could you provide a live link, which depicts this scenario?

Regards,
Peter

[Pradeep Deshpande]

I am trying to export the 250000 records from telerik grid using "XLSX" format its taking 7 minutes to show the save link.

Can we improve the performance of export to excel.

[Kostadin]

Hello Pradeep,

The best approach is to create the Excel document manually by using the ExportInfrastructure. Please check out the following code library which demonstrates how to manually fetch the data from your datasource and create an Excel structure. Also I would suggest you to check out the following help article for additional information about ExportInfrastructure.

Regards,
Kostadin
Telerik