WebDataGrid issues with large amounts of data

I typically implement security by using the following code in the Init event of every page…

  If Session.IsNewSession = True Or Session("Login") = "" Then
       Response.Redirect("SessionTimeout.aspx")
 End If

I've noticed some peculiar behavior when using a WebDataGrid that contains a large amount of data (say 50,000 rows) and utilizing paging (say 500 rows per page).  I use a SQL Server 2005 backend.

When I click to navigate to a different page, I get a "Server Not Responding" error popup from the WebDataGrid control (there are other variations of that error that I sometimes get as well).

As soon as I get that sort of error and try to navigate to another page, I am redirected to my SessionTimeout.aspx page … indicating that my Session must have been lost as a result of this error.

Has anyone else experienced this kind of issue?  Is there simply a limitation on how much data a WebDataGrid can manage ?

Thanks,
John Zivic
Chandler, AZ

 

 

Hello John,

Could you attach your grid markup please? I think that 500 records per page are not small amount of data but you should not get any errors.

I would request also the build number of your version as well.

Thanks,

My Build number is:
Infragistics35.Web.v10.3, Version=10.3.20103.1013
(I'm on a corporate server that has 3.5 framework … so can't run 4.0)

This is my WebDataGrid markup in my .ASPX page.
However, I dynamically add columns to the grid (see below)

<ig:WebDataGrid ID="wdgMain" runat="server" Height="92%" 
StyleSetName="Office2007Black" Width="95%" DefaultColumnWidth="100px"
AutoGenerateColumns="False">
  <Behaviors>
    <ig:Activation Enabled="true" />
    <ig:Paging PageSize="25" PagerAppearance="Both">
    </ig:Paging>
    <ig:Selection Enabled="False">
    </ig:Selection>
    <ig:RowSelectors HeaderRowSelectorCssClass="rowSelectorWidth">
    </ig:RowSelectors>
    <ig:ColumnMoving DragMarkupCssClass="">
    </ig:ColumnMoving>
    <ig:ColumnResizing>
    </ig:ColumnResizing>
    <ig:Sorting SortingMode="Single" Enabled="true">
    </ig:Sorting>
    <ig:ColumnFixing>
    </ig:ColumnFixing>
  </Behaviors>
</ig:WebDataGrid>

In the Page_Load event, I loop through a DataReader that contains a list of columns that the user wants to display in the data grid.  A typical iteration to add a column looks like this:

Dim BoundDataField As New BoundDataField
BoundDataField.Key = Trim(dbReaderView("Column_Name") & "")
BoundDataField.DataFieldName = Trim(dbReaderView("Column_Name") & "")
BoundDataField.Header.Text = Trim(dbReaderView("Column_Label") & "")
BoundDataField.Width = CInt(strPixel_Width)
BoundDataField.CssClass = strCellCSS
wdgMain.Columns.Add(BoundDataField)

I also control the pagesize:
wdgMain.Behaviors.Paging.PageSize = CInt(ddlItemsPerPage.Text)

I control how many rows I'm returning based on a dropdownlist.  This results in queries such as:
SELECT TOP 500 ….  SELECT TOP 1000 …. SELECT TOP 5000, or just SELECT which returns all records.

Here are the error scenarios I am experiencing:

With query… "SELECT TOP 500"  with 92 columns in the SELECT statement
(1) WebDataGrid renders fine the first time with paging set to 100 rows per page (takes maybe 30 seconds to load)
(2) click on paging hyperlink
(3) Ajax loading indicator spins for about 10 seconds
(4) Popup error:  Message from webpage … Server does not respond.  OR
    Popup error:  Message from webpage … Deserialization failure: Invalid response.

When using WebExporterExcel on same data grid:

(1) Several minutes of processing
(2) Finally get Save/Open Excel dialog.  Select Open
(3) ERROR:  The connection with the server was reset

Get similar result when I don't limit the number of rows … so for 10 columns and 55,000 rows

Hello  John,

I would suggest increase the timeout period of the grid like this:

$find("WebDataGrid1")._callbackManager.setTimeout(60000);  Please check this  post for more information: https://www.infragistics.com/community/forums/f/ultimate-ui-for-windows-forms/92985/ultrachart-columns-and-scatter-combination#92985

Please make sure when you switch pages you provide the same datasource to the grid on postback.

About the ExcelExporter scenario maybe would be better to save the exported file on the server and then provide link to the user to be able to download it. I guess that you are directly writing the excel to the stream which is the default setup of the ExcelExporter component.

Hope that helps.

Rado:

Thanks for your suggestion.  I tried implementing as you suggested, but I'm still getting the "Server does not respond." error popup.

One question I didn't see answered in any of the posts about this was WHERE this should be implemented.

This method worked for me, but I'm not sure it is the correct place to implement.  I have a Master Page, and inside one of the ContentPlaceHolders I added the following to some existing JavaScript code …

 <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script type="text/javascript">

        function pageLoad() {
            $find("<%= WebDataGrid1.ClientID%>")._callbackManager.setTimeout(60000);
        }

</script>
</asp:Content>

It runs without error (it finds the object … and I also added an alert to make sure it was firing) … but I'm still seeing the error.

Any other suggestions?

I filled a WebDataGrid with a small SQL Server stored proc result set of 61 rows x 79 varchar(max) columns that took 40 seconds to execute from SQL Server Mgmt Studio.  

At first, I got a timeout error on my WebDataGrid page that said, "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I added this code, which FIXED my TimeOut error:

    <script type="text/javascript" language="javascript">

    function pageLoad()

    {

        var grid = $find("WebDataGrid1");

        grid._callbackManager.setTimeout(60000);

 

         //All three of these variations produced the same result: Fixed TimeOut Error; Caused null reference error.

         //$find("<%= WebDataGrid1.ClientID%>")._callbackManager.setTimeout(60000);

        //$find("WebDataGrid1")._callbackManager.setTimeout(60000);

    }

    </script>

 

But now the page throws this error on the .setTimeout line above. "Microsoft JScript runtime error: '_callbackManager' is null or not an object."

 

My page works properly, with no TimeOut or null reference errors, if I surround the .setTimeout line with try{} and an empty catch{}, as shown in the code below.

Why is _callbackManager null at runtime? 

How did this fix my TimeOut Error when _callbackManager was null at runtime?

Thanks.

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"

    CodeFile="DataPointRatioValue.aspx.cs" Inherits="Grids_DataPointRatioValue" %>

<%@ Register Assembly="Infragistics4.WebUI.WebResizingExtender.v10.3, Version=10.3.20103.2073, Culture=neutral, PublicKeyToken=7dd5c3163f2cd0cb"

    Namespace="Infragistics.WebUI" TagPrefix="igui" %>

<%@ Register Assembly="Infragistics4.Web.v10.3, Version=10.3.20103.2073, Culture=neutral, PublicKeyToken=7dd5c3163f2cd0cb"

    Namespace="Infragistics.Web.UI.GridControls" TagPrefix="ig" %>

<%@ Register Assembly="Infragistics4.WebUI.WebDataInput.v10.3, Version=10.3.20103.2073, Culture=neutral, PublicKeyToken=7dd5c3163f2cd0cb"

    Namespace="Infragistics.WebUI.WebDataInput" TagPrefix="igtxt" %>

<%@ Register Assembly="Infragistics4.Web.v10.3, Version=10.3.20103.2073, Culture=neutral, PublicKeyToken=7dd5c3163f2cd0cb"

    Namespace="Infragistics.Web.UI.ListControls" TagPrefix="ig" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">

    <script type="text/javascript" language="javascript">

        function pageLoad()

        {

            try

            {

                var grid = $find("WebDataGrid1");

                grid._callbackManager.setTimeout(60000);

 

                //$find("<%= WebDataGrid1.ClientID%>")._callbackManager.setTimeout(60000);

                //$find("WebDataGrid1")._callbackManager.setTimeout(60000);

            }

            catch (e)

            {

                //catch and just suppress error

            }

        }

    </script>

    <table runat="server" cellpadding="5" cellspacing="2">

        <tr>

            <td align="left" colspan="4">

                <asp:Label runat="server" Font-Size="Larger" ID="lblMessage" ForeColor="Maroon"></asp:Label>

            </td>

        </tr>

        <tr>

            <td align="left">

                <ig:WebDropDown ID="ddlDataPointValueSet" runat="server" Width="411px" DropDownContainerWidth="544px"

                    DataKeyFields="DataPointValueSetID" DataSourceID="dSrc_DataPointValueSet" TextField="Name"

                    ValueField="DataPointValueSetID" EnableLoadOnDemand="True" EnableMultipleSelection="True"

                    EnableClosingDropDownOnSelect="False">

                    <DropDownItemBinding TextField="Name" ValueField="DataPointValueSetID" />

                </ig:WebDropDown>

            </td>

            <td align="left">

                <ig:WebDropDown ID="ddlCategory" runat="server" Width="411px" DropDownContainerWidth="544px"

                    DataKeyFields="SubCategoryID" TextField="Category" ValueField="SubCategoryID"

                    EnableLoadOnDemand="True" EnableMultipleSelection="True" DataSourceID="dSrc_Category"

                    EnableClosingDropDownOnSelect="False">

                    <DropDownItemBinding TextField="Category" ValueField="SubCategoryID" />

                </ig:WebDropDown>

            </td>

            <td align="left">

                <igtxt:WebImageButton ID="WebImageButton1" runat="server" Text="Show" AccessKey="w"

                    UnderlineAccessKey="true" OnClick="WebImageButton1_Click" ClickOnEnterKey="true">

                </igtxt:WebImageButton>

            </td>

            <td align="left">

                <igtxt:WebImageButton ID="WebImageButton3" runat="server" Text="Export" AccessKey="x"

                    OnClick="WebImageButton3_Click">

                </igtxt:WebImageButton>

            </td>

        </tr>

    </table>

    <igui:WebResizingExtender ID="WebResizingExtender1" runat="server" TargetControlID="WebDataGrid1" />

    <ig:WebDataGrid ID="WebDataGrid1" runat="server" Height="444px" Width="100%" DataSourceID="dSrc_DataPointRatioValue"

        Visible="False" EnableDataViewState="false">

        <Behaviors>

            <ig:ColumnResizing>

            </ig:ColumnResizing>

            <ig:Filtering>

            </ig:Filtering>

            <ig:VirtualScrolling>

            </ig:VirtualScrolling>

            <ig:Sorting>

            </ig:Sorting>

        </Behaviors>

    </ig:WebDataGrid>

    <ig:WebExcelExporter runat="server" ID="WebExcelExporter1" ExportMode="Download" />

    <asp:SqlDataSource ID="dSrc_DataPointRatioValue" runat="server" ConnectionString="<%$ ConnectionStrings:dbExcelConnectionString %>"

        SelectCommand="usp_Select_Scorecard_Data" SelectCommandType="StoredProcedure">

        <SelectParameters>

            <asp:Parameter Name="DataPointValueSetIDList" Type="String" />

            <asp:Parameter Name="SubCategoryIDList" Type="String" />

        </SelectParameters>

    </asp:SqlDataSource>

    <asp:SqlDataSource ID="dSrc_DataPointValueSet" runat="server" ConnectionString="<%$ ConnectionStrings:dbExcelConnectionString %>"

        SelectCommand="usp_Select_List_DataPoint_Value_Set" SelectCommandType="StoredProcedure">

        <SelectParameters>

            <asp:Parameter DefaultValue="False" Name="AddNullRow" Type="Boolean" />

        </SelectParameters>

    </asp:SqlDataSource>

    <asp:SqlDataSource ID="dSrc_Category" runat="server" ConnectionString="<%$ ConnectionStrings:dbExcelConnectionString %>"

        SelectCommand="usp_Select_Category" SelectCommandType="StoredProcedure"></asp:SqlDataSource>

</asp:Content>

"Server Not Responding" would seem to indicate the client isn't getting a response.  The fact that your session variable is being reset means the app_pool has recycled.  App pools can recycle for many reasons, including unhandled application errors, or excessive memory consumption.  If there was an unhandled exception I would think the Grid would get a response, just not the one it wanted – which makes me lean towards memory.

First I'd check the system event log (Application Log) and look for any ASP.NET errors that were logged.  They could lead you to your solution.  

Are you storing anything else in session?  If you're storing say 50,000 rows in session you could easily push the app_pool over it's max memory threshold, which would force the app_pool to recycle.  

Thanks for your suggestions.

No, I have only a handful of Session variables that I'm using.  And I only lose session some of the time when this happens.

I have a number of pages where this error happens.  The only common thing among them is that this occurs when there are a large number of rows.  The identical grid works fine when I populate the query with "SELECT TOP 1000" …. but when I don't limit the SELECT and it selects 15,000 rows, the grid loads ok, but I get this error when I click on paging (showing 100 records per page).  It's as if it is trying to retrieve the data from say Page 45, but after 15 seconds or so it stops and gives that error.

This is hosted on a large corporate shared server environment, but I am also able to reproduce it on my localhost.  No errors being reported in any of the Application or other logs.  It happens in DEV, TEST, and PROD which are all on different servers.

That's all good information.  If the application isn't crashing, then something is making it take a really long time to load.  Do you have EnableDataViewState set to true for the grid?  Or are you pushing your datasource into viewstate manually?  50,000 rows of data is much too large to fit into viewstate, and it would significantly slow down processing.  But more importantly since upload speeds are usually the bottleneck, form posts (which is what happens when you click a pager link) will likely cause the application to break completely.  If you're not pushing anything up into viewstate, I'd set a breakpoint in your app code and try clicking on a pager link.  See whether the breakpoint is getting hit, and when the response is written.  That would help determine whether the problem is on the browser's form post side, or the server's response side.

Hello John,

I have created a simple example based on your scenario that includes a “WebDataGrid” with 15 000 rows and paging enabled with 100 records per page.

I have handled the “PageIndexChanged()” event and put thread in “Sleep”  mode for 30 sec.:

protected void WebDataGrid1_PageIndexChanged(object sender, Infragistics.Web.UI.GridControls.PagingEventArgs e)

    {

        Thread.Sleep(30000);

    }

 

When the thread resumed its execution after “Sleep” mode the error message from the page was sent – "Server does not respond.".

Then I have added the following function (provided in previous responses) to the “Grid_Initialize()” client-side event:

function WebDataGrid1_Grid_Initialize(sender, eventArgs)

{

       ///<summary>

       ///

       ///</summary>

       ///<param name="sender" type="Infragistics.Web.UI.ControlMain"></param>

       ///<param name="eventArgs" type="Infragistics.Web.UI.EventArgs"></param>

 

       $find("<%= WebDataGrid1.ClientID%>")._callbackManager.setTimeout(31000);       

 

}

 

The error was not present anymore and after 30 sec. of waiting the paging displayed the next page from the grid without the above error message.

You can try the above resolution and let me know what the results are on your machine.

Hello John,

Let me know if you need further assistance with this question.

From the above,

How can i call the WebDataGrid1_Grid_Initialize function?

Is it code behind or JavaScript?

Help me.

Thanks in advance.

Hello,

The Initialize function is a client side event and you would set WebDataGrid1.ClientEvents.Initialize to the name of the JavaScript function that you want called for this event.

Hi John,

I presume "Enable AJAX" option in your grid is turned on (default setting is enabled).  

The problem in your scenario is if a user interacts with the grid after the server user session expires the grid sends an ajax request (you see the ajax indicator) and then during that ajax call instead your page to return the grid data requested your authentication code above issues a redirect and the grid ajax call never gets the data requested eventualy timing out with the "Server does not respond" message you see.

The solution is to handle the session timeout for ajax calls on the server and instead issuing a redirect. Alternatively you can set EnableAjax="False" in on your grid and the authentication code you have will behave as expected redirecting the user to the session time out page when the user initiates a postback and the server user session has expired. 

To verify my answer you can put a breakpoint on the redirect line in your code and you will notice the breakpoint will be hit everytime prior getting  "Server does not respond" on the client.

I hope this helps.