ASP.NET Dynamic GridView becomes unresponsive when saving 300+ rows. BtnSave_Click event fired with 150 rows

Well, I never seen a web site that shoves out 300 rows to the user, have you?

The simple matter that’s too many rows.

Introduce a datapager, and that will reduce the markup sent to the browser.

However, keep in mind that a data pager will STILL pull the 300 records from the database, but only send say 20 rows (or whatever your page size is) to the browser. However, a data pager is a bit of a kludge, since it does NOT limit the data pulled from the database.

So, for great performance you want to address both issues. They are reducing the data base rows pulled, and then reducing the rows sent to the browser.

I mean, how does any database system work? The simple matter be it web based, accounting systems, or whatever?

You pull the one row to edit.

Since adding a data pager is rather easy, then let’s NOT use a data pager for the gridview, but roll our own custom pager.

We do this, since we going to edit a table with 150,000 rows.

And our goal should be to not allow ANY delay or response time of over 1/2 second (else we are just torturing our users).

Furthermore, we not going to use ajax, or any client code to speed this up. I will pop a dialog (jQuery.UI one) to edit a row, but that has ZERO impact on the performance.

So, with 150,000 rows, simple BASIC code (I still like basic code, and the performance is fantastic).

So, here is how the example code runs:

Note that you NEVER perceive ANY delay that even approaches 1/2 a second, yet we are editing a table with 150,000 rows of data.

I moved out the load data button to a button, just to show that I am NOT starting the page out with loaded data, but wanted to demonstrate that we are loading the data, and how fast the table loads.

So, then we have this:

Note close how fast the page loaded.

Note how fast the paging to the next page works (I jump to page number 2, and it don’t matter how many pages you have, or what page you are on. The response time is instant). I then jump to page 1390, and again, the page jump occurs in a blink of an eye.

Note how fast the edit pops up, and how fast the save button is.

Like any good database application (desktop or web), the simple concept is to limit data pulled from the database. And for web based, then limiting the rows pulled into the browser is important, since compared to desktop software?

In desktop land, you have a CPU that can directly write to the display memory.

In web land, you’re having to send the HTML to the client-side browser, and that is thus a VERY limited resource that you have to take care off, and not send 300 rows of data to that slow display. This is not a desktop display, but you are sending a character stream over the internet to some browser. As a result, you can’t send a lot of data. However, be it old FoxPro days, MS-Access or now web based software?
The rules for good performance are all the same. We can’t pull too much data, but with web based software, then the bottle neck is the display device, which is a browser on the other end of the internet, and it is several 1000 times slower then a memory based monitor attached to your computer and CPU.

So, the grid view markup is simple, and is this:

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
            CssClass="table table-hover" Width="45%"
            DataKeyNames="ID"
            >

            <Columns>
                <asp:BoundField DataField="country_name" HeaderText="Country" />
                <asp:BoundField DataField="state_name" HeaderText="State/Province" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="latitude" HeaderText="Latitude" />
                <asp:BoundField DataField="longitude" HeaderText="Longitude" />
                <asp:BoundField DataField="Description" HeaderText="Description" ItemStyle-Width="400px" />
                <asp:TemplateField HeaderText="Edit"
                    ItemStyle-HorizontalAlign="Center" ItemStyle-Width="130px">
                    <ItemTemplate>
                      <Button ID="cmdEdit" runat="server" type="button" class="btn myshadow" 
                         onserverclick="cmdEdit_ServerClick" >
                          <span aria-hidden="true" class="fa fa-edit fa-lg"></span> Edit</Button>

                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

I should point out that I don’t bother with the built in Grid view commands, as they are a pain.

You can drop in a simple button, link button, or even a button with runat="server". They all work the same. I used a "button" in place of asp.net one, and that’s due to me wanting the icon in the button. However, a simple button can be dropped into the row. They all work the same, and all can have a standard server side click event attached to such controls.

So, then we need the code to pick up the current row click.

That edit button code is thus this:

Protected Sub cmdEdit_ServerClick(sender As Object, e As EventArgs)

    Dim btn As HtmlButton = sender
    Dim gRow As GridViewRow = btn.NamingContainer
    Dim PK As Integer = GridView1.DataKeys(gRow.RowIndex).Item("ID")

    Dim rstCity As DataRow =
        MyRst($"SELECT * FROM cities WHERE ID = {PK}").Rows(0)

    Call fLoader(EditRecord, rstCity)

    Session("PK") = PK


    Dim sJava As String = $"myedit('{btn.ClientID}')"

    ScriptManager.RegisterStartupScript(Page, Page.GetType, "my java", sJava, True)


End Sub

Note how I used "naming" container. I suggest you do the same, and dump all the messy command stuff for the GridView control (the built in commands are a pain to use, and you can see the above simple code works just as well anyway).

MyRst is just a global helper routine I have. Nothing special, but for completeness, that MyRst code is this:

Public Function MyRst(strSQL As String) As DataTable
    ' general get any data from SQL
    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.Countries)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using
    Return rstData
End Function

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable
    ' general get any data from SQL command

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.Countries)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

So, right below the grid view, I have another div, and again, it just standard markup. I’ll not post all of it, but it looks like this:

<div id="EditRecord" runat="server" 
    style="padding:16px;display:none" 
    clientidmode="Static"  >

    <div style="float:left" class="iForm">
            <label>Country</label><asp:TextBox ID="txtCountry" runat="server" f="country_name" width="280"></asp:TextBox> <br />
            <label>Country Code</label><asp:TextBox ID="tFN" runat="server" f="country_code" Width="140"></asp:TextBox> <br />
            <label>State Name</label><asp:TextBox ID="tState" runat="server" f="state_name" Width="140"></asp:TextBox> <br />
            <label>State Code</label><asp:TextBox ID="tStateCode" runat="server" f="state_code" Width="140"></asp:TextBox> <br />
            <label>City</label><asp:TextBox ID="tCity" runat="server" f="City" Width="140"></asp:TextBox> <br />
    </div>
    <div style="float:left;margin-left:20px" class="iForm">
        <label>Description</label> <br />
        <asp:TextBox ID="txtNotes" runat="server" Width="400" TextMode="MultiLine" 
            Height="150px" f="Description" ></asp:TextBox> <br />

            <label>Latitude</label><asp:TextBox ID="txtLat" runat="server" f="latitude" Width="140"></asp:TextBox> <br />
            <label>Longitude</label><asp:TextBox ID="txtLong" runat="server" f="longitude" Width="140"></asp:TextBox> <br />
    </div>

So, again, nothing special. However, I fast became tired of loading up controls, so I built a "general" control loader, and all it does is take the one data row, and fill out the controls for you (Floader).

Note how the EditRecord has display:none. This is due to me wanting to have a popup. I could however use 100% server side code, and hide the GridView, and just show the EditRecord div. However, I did want that fancy pop up, and thus I used a jQuery.UI dialog.

So, while not for performance, the jQuery.UI code (JavaScript) below the EditRecord div is this:

<script>
    function myedit(Sbtn) {
        var btn = $('#' + Sbtn)
        myDialog = $("#EditRecord")
        myDialog.dialog({
            title: "Edit City Record",
            modal: true,
            sizable: true,
            width: '830',
            appendTo: "form",
            closeText: "",
            dialogClass: "dialogWithDropShadow",
            position: { my: 'center top', at: 'left bottom', of: btn }
        })
    }
</script>

And that jQuery.UI dialog is very nice, since I can position the dialog on the row click (in this case right below the button clicked).

So, the only part left is the data paging. And as noted, I used SQL server data paging, since it is multiple orders faster then using a GridView data pager. (it will ONLY pull the page size of rows).

So, that code looks like this, along with the code for the first button click to load the grid:

Protected Sub cmdLoad_Click(sender As Object, e As EventArgs)

    Dim strSQL As String = "SELECT COUNT(*) FROM Cities"
    lblRows.Text = MyRst(strSQL).Rows(0)(0)

    Dim intPages As Integer = lblRows.Text / PagerSize
    lblPageOn.Text = 1
    lblNumPages.Text = intPages

    LoadData()

End Sub

Sub LoadData()


    Dim iOnPage As Integer = lblPageOn.Text

    Dim strSQL As String =
        "SELECT * FROM Cities ORDER BY country_name, state_name, City
        OFFSET ((@PageNumber - 1) * @RowsPerPage) ROWS
        FETCH NEXT @RowsPerPage ROWS ONLY;"

    Dim cmdSQL As New SqlCommand(strSQL)
    cmdSQL.Parameters.Add("@PageNumber", SqlDbType.Int).Value = iOnPage
    cmdSQL.Parameters.Add("@RowsPerPage", SqlDbType.Int).Value = PagerSize

    Dim rstData As DataTable = MyRstP(cmdSQL)

    GridView1.DataSource = rstData
    GridView1.DataBind()

End Sub

So, note the sql server paging I used here.

The simple calculation is seen in the SQL, and is:

  OFFSET ((@PageNumber - 1) * @RowsPerPage) ROWS
  FETCH NEXT @RowsPerPage ROWS ONLY;"

So, with a given page number you want, and the number of rows per page, then above will only pull those rows from SQL server. It is blinding fast, and I used the above against tables with 12 million rows, and pulling say one page of data (18 rows) occurs absolute instant.

So, now the page next, and previous buttons become this simple code:

Protected Sub cmdPrevious_Click(sender As Object, e As EventArgs)

    Dim iPageOn As Integer = lblPageOn.Text
    If iPageOn > 0 Then
        iPageOn = iPageOn - 1
        lblPageOn.Text = iPageOn
    End If

    LoadData()

End Sub

Protected Sub cmdNext_Click(sender As Object, e As EventArgs)

    Dim iPageOn As Integer = lblPageOn.Text
    If iPageOn < lblNumPages.Text Then
        iPageOn = iPageOn + 1
        lblPageOn.Text = iPageOn
    End If

    LoadData()

End Sub

So, that’s about it, but as you can see, even with a table of 150,000 rows, there is NEVER at any time any kind of perceptible delay experienced by the end user.

And event the text box to enter the page?

Well, that’s just a simple text box (with auto postback). That code is this:

Protected Sub lblPageOn_TextChanged(sender As Object, e As EventArgs)

    Dim iPageOn As Integer = lblPageOn.Text
    If iPageOn < lblNumPages.Text Then
        LoadData()
    End If

End Sub

Now, one can introduce fancy techniques such as ajax, and you can thus prevent the page suffering a post-back, but I would only go that extra mile (and cost) of introducing ajax (and web method calls) if the results were slow, but as above shows, with simple and clean server side code, blistering performance is not all that hard to achieve.

I point out that we did not turn off view state or anything else in attempting to improve performance, since the results are more then sufficing from a user response point of view.