Large Table - approach?

[rcombis]

Using PGSQlDataSource for a table with 400,000 records. Using Paging with an asp.net GridView.

Works pretty well. Speed acceptable. Sorting/Paging work as expected.

Now I am working with a table with over 100 million rows. Using the same approach as above.

Operation times out. I haven't had time to put dbMonitor in the app, but will do tomorrow.

My question is, what is best approach for dealing with this many records? I only want to display 25/50 at a time with sorting/paging.

Thanks,
Rob

[Pinturiccio]

PgSqlDataSource supports the Paging mode. There are two kinds of the Paging mode:
1. DataPagingMode = DataPagingMode.Auto — allows using paging for PgSqlDataSource with standard settings, but requires executing a query for retrieving the number of rows in a table, which in its turn takes a lot of time if the table contains a lot of records.
pgAdmin executes the query 'select count(*) from table' where the table contains 100 million records for a long time too.
2. DataPagingMode = DataPagingMode.Manual — here you specify the required settings yourself. The described issue can be solved using this mode.

Below is an example of solving the issue. This example is designed for demonstrating DataPagingMode.Manual only, and you can improve it yourself.
1. Set the following properties for the GridView component:
- AllowPaging = true;
- AllowSorting = true;
- PageSize = 25;
2. Set the DataPagingMode property value for the PgSqlDataSource component to 'Manual';
3. Open PgSqlDataSource Editor, for this select the 'Configure Data Source' item in the component's menu;
4. Enter the following command text:
select * from table order by 1 LIMIT :Count OFFSET :Start
Where 'table' is your table with 100 million records;
5. Click OK and generate parameters automatically;
6. In the event handler of the Selecting event of the PgSqlDataSource object, use the following code:

Code: Select all

protected void PgSqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Arguments.MaximumRows = 25;
    e.Arguments.TotalRowCount = e.Arguments.StartRowIndex + 50;
    e.Command.Parameters["Start"].Value = e.Arguments.StartRowIndex;
    e.Command.Parameters["Count"].Value = e.Arguments.MaximumRows;
}

After this, you can use Paging for your table.