DataGrid Quick Pagination method based on access

The DataGrid is a very powerful ASP.net Web server-side control that enables you to dynamically sort, edit, and page pages of data in a table in addition to the ability to format the data in a table in a variety of ways. Frees web developers from cumbersome code. Implementing the paging function of the DataGrid has been a thorny issue for many novice asp.net, especially the custom paging feature, which is a variety of ways to implement and very flexible. This article will introduce a Datagird control to the Access database in the fast pagination method, to help beginners master the DataGrid paging technology.

The current paging method

The paging method built into the DataGrid uses SQL statements such as "SELECT * from <TABLE>" To remove all records from the database table into the dataset, after the DataGrid control is bound to the dataset, Its automatic paging function will help you filter out the current paging data from the dataset and display it, and other useless data will be discarded.

Another approach is to use the custom paging feature to set the AllowCustomPaging property of the DataGrid to True, and then use the DataAdapter Fill method to advance the filtering of the data to the dataset. Instead of letting the DataGrid help you sift through:

public int Fill (
　DataSet dataSet, //要填充的 DataSet。
　int startRecord, //从其开始的从零开始的记录号。
　int maxRecords, //要检索的最大记录数。
　string srcTable //用于表映射的源表的名称。
);

The method first populates the dataset with the results from the query, and discards the data that does not need to be displayed. Of course, the custom paging feature needs to be done more than that, which is described later in this article.

Both of these methods work by taking all the records out of the database and then filtering out useful data for display. As you can see, the efficiency of both approaches is basically consistent because they do not take effective steps to reduce access to the disk in the data access phase. For a small number of records, this overhead may be relatively small, and if the paging for large amounts of data is expensive, the cost will be very large, resulting in a very slow paging rate. In other words, even if the data on each DataGrid page is just 10 of a database table with tens of thousands of records, every time the DataGrid is paginated, all the records are taken out of the table.

Many people are aware of this problem and have come up with a workaround: Use custom paging to fetch only the data to be displayed from the database at a time. In this way, we need to work on the SQL statement. Because Access does not support true stored procedures, it is not as free to write a paging algorithm as SQL Server. SQL Server can use temporary tables in stored procedures to achieve efficient paging algorithm, which is widely used. For access, however, we have to find a way to implement the most efficient algorithm within an SQL statement.

There are several ways to get a piece of data in a single SQL statement. The algorithm is different and the efficiency is different. After a rough test, I found that the worst-performing SQL statement took about 3 times times as much time as the most efficient SQL statement! And that number will increase as the number of records increases. The following two common SQL statements are described below.

In order to facilitate the next discussion, we will first agree as follows:

Variable	Description	Variable	Description
@PageSize	Total number of records displayed per page	@MiddleIndex	Index of intermediate pages
@PageCount	Total Paging	@LastIndex	Index of last page
@RecordCount	Total number of records in datasheet	@TableName	database table name
@PageIndex	Index of current page	@PrimaryKey	Primary key field Name
@FirstIndex	Index of the first page	@QueryFields	Set of fields to query

Variable	Defined
@PageCount	(int) Math.ceiling (double) @RecordCount/@PageSize)
@FirstIndex	0
@LastIndex	@PageCount –1
@MiddleIndex	(int) Math.ceiling ((double) @PageCount/2) –1

Let's take a look at the least efficient SQL statement:

SELECT TOP @PageSize * FROM @TableName
WHERE @PrimaryKey NOT IN (
　SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName
　ORDER BY @PrimaryKey ASC
) ORDER BY @PrimaryKey ASC

This SQL statement is slow slow in this case, the value of each @primarykey traversed by the main SELECT statement is compared to the value of each @primarykey in the result set of the child SELECT statement, so the time complexity is very high. We have to remind you that you should try to avoid using the not in statement when writing SQL statements, because it often increases the time complexity of the entire SQL statement.

The other is an SQL statement that uses two top and three order by, as follows:

SELECT * FROM (
　SELECT TOP @PageSize * FROM (
　SELECT TOP @PageSize*(@PageIndex+1) * FROM @TableName
　　ORDER BY @PrimaryKey ASC
　) TableA ORDER BY @PrimaryKey DESC
) TableB ORDER BY @PrimaryKey ASC

This SQL statement has a larger space complexity. If the page you want to display is just the last page, it's less efficient than simply select all the records. Therefore, for the paging algorithm, we should also concrete analysis of specific circumstances, can not generalize. Here's a brief introduction to the concepts that you can skip directly if you are familiar with primary keys and indexes.

About the concepts of primary keys and indexes

In Access, a table's primary key (PRIMARY key, also called the primary index) is necessarily a unique index (unique index), and its value is not duplicated. In addition, the index is sorted according to the value of the indexed column, and each index record contains a pointer to the data row it refers to, which is helpful for the execution of the order by. We can take advantage of the two features of a primary key to locate a record, and quickly remove the record to be displayed on a page.

For example, suppose a primary key field is an integer, in a database table, the index of the record has been sorted by the value of the primary key field (by default), and the index of the record with the primary key field value of "11" must precede the index of the record with the value "12" (assuming that the value of the primary key exists in the database table is "12 "record). If a primary key field does not have a unique constraint, it is possible for a database table to have records with a value of "11" for two or more primary key fields, so that the position between the records is not determined.

Let's take a look at how to use the primary key for data segmentation queries.