Very large amount of data on gridview in asp.net [closed]

Question

I have a large amount of data (a sql query with 20000 records) and filling my datagrid with that amount of data takes like 10 minutes, this is my gridview definition:

<asp:GridView ID="g" runat="server" Height="113px" Width="817px" 
BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" 
CellPadding="3" GridLines="Vertical" AllowPaging="True" Font-Size="Small" 
     PageSize="30">
    <AlternatingRowStyle BackColor="#DCDCDC" />
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#0000A9" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#000065" />
    <PagerStyle cssClass="gridpager" HorizontalAlign="Left" />  

</asp:GridView>

As you can see I have enabled to true the AllowPaging property.

This is how I bind the data:

DataSet dts = new DataSet();
OracleDataAdapter oad = new OracleDataAdapter(query, co.conn);

cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();

oad.Fill(dts);


g.DataSource = dts.Tables[0];
g.DataBind();

How can I improve the performance?

When I fill the dataset (oad.Fill(dts);) takes 10 minutes to complete. Is this because I set the 20000 records at once? Is there a way to show only the first 30 records and recall the data when the user paginates the gridview? Is there another way to improve the performance?

Answer 1

If my understanding is correct, you want to add server paging

When you simply add AllowPaging="True" to the grid, by default, the GridView has no idea how to paging your data from the server, the paging is being executed in-memory after the whole results have been fetched from the database and this happens every time the GridView is bind

I think you want to add server paging (paging in the server and only sending to the client a small bunch of records), in order to do that, you could take advantage of the several ASP.Net data source controls.

Since you are doing the connection to your database manually, then you need to manually add paging code in your queries and map that code to the control

I think the only data source controls that can help you (since you are using Oracle as the database) are

• SqlDataSource. Sadly it does not support server paging out-of-the-box, you would need to tweak it
• ObjectDataSource. It can be easily integrated with the GridView control to provide paging, however you would need to manually add code to your queries or store procedures to paginate your data in the server
• EntityDatasource. It's used to connect with your database when using EntityFramework

If you would be using EF or NHibernate for example, it would be easier, the code would look like:

g.DataSource = myContext.MyTable.Skip(pageIndex * pageSize).Take(pageSize);
g.DataBind();

Example using the ObjectDatasource

ASPX

    <asp:ObjectDataSource runat="server" ID="ods" TypeName="MyObject" EnablePaging="True"
        SelectMethod="FindPaging"
        MaximumRowsParameterName="pageSize"
        SortParameterName="sortColumn"
        StartRowIndexParameterName="startIndex"
        SelectCountMethod="FindPagingCount" onselected="ods_Selected"
    >
        <SelectParameters>
            <asp:Parameter Name="sortColumn" Type="String" />
            <asp:Parameter Name="startIndex" Type="Int32" />
            <asp:Parameter Name="pageSize" Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

            <asp:GridView ID="grid" runat="server" AllowPaging="True" AllowSorting="True" PageSize="10"
                DataSourceID="ods" AutoGenerateColumns="true">
            </asp:GridView>

Data Component

[System.ComponentModel.DataObject]
public class MyObject
{
    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public IEnumerable<employee> FindPaging(int startIndex, int pageSize, string sortColumn)
    {
        // place here your code to access your database and use the parameters to paginate your results in the server
    }

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public int FindPagingCount(int startIndex, int pageSize, string sortColumn)
    {
        var c = new DataClassesDataContext();

        return c.employees.Count();
    }
}

Answer 2

The easy solution would be to grab the top 30 rows, then when you grab the next set you grab the next 30 by doing top 30 where id > @lastid.

That way you only ever request 30 rows and dont have to bother with getting the whole recordset from the database.