DataGrid Connect Access Quick Pagination Method (2)--sql statement selection (Ascending)

access|datagrid| Pagination | statement DataGrid connection Access quick pagination (2)--sql statement selection (Ascending)
I. Related Concepts
In an ACCESS database, the primary key (PRIMARY key, also called the primary index) of a table must have a unique index (unique index), so the value of the primary key field is not duplicated. and the index page is sorted by the value of the indexed column, and each index record contains a pointer to the data row that it refers to. 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 that the primary key field is an INTEGER type, records in a database table have been sorted in ascending order by the value of the primary key field, and the record for the primary key field of "11" must be just ahead of the record with a value of "12" (assuming that a record with the primary key in the database table has a value of "12"). 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.
Second, ascending
(1) @PageIndex <= @FirstIndex

The first page of the data is simple enough, we just use the top @PageSize can be taken out of the first page to display the record. Because the records in the datasheet have been sorted in ascending order in the value of the primary key field, the ORDER BY clause is omitted and is faster.

SELECT Top @PageSize @QueryFields
From @TableName
WHERE @Condition
--ORDER by @PrimaryKey ASC


Figure: @PageIndex = 0



(2) @FirstIndex < @PageIndex <= @MiddleIndex

The performance can be improved effectively by separating the SQL statements that take the first half of the data table and the second half of the record. I'll explain the problem in detail later. Now look at the SQL statement that takes the first half of the record. First, remove the primary key value from all records before the current page, select the maximum value, and then remove the previous @PageSize record with the primary key value greater than the maximum value. It is worth noting that there are two order by @PrimaryKey ASC statements, respectively, in the innermost and outermost SELECT statements. As mentioned earlier, records in a database table have been sorted in ascending order by the value of the primary key field, so there is no need to gild the lily.

SELECT Top @PageSize @QueryFields
From @TableName
WHERE @PrimaryKey > (
SELECT MAX (@PrimaryKey) from (
SELECT Top @PageSize * @PageIndex @PrimaryKey
From @TableName
WHERE @Condition
--ORDER by @PrimaryKey ASC
) TableA
) WHERE @Condition
--ORDER by @PrimaryKey ASC


For example: @PageIndex = 1, red--> yellow--> Blue



(3) @MiddleIndex < @PageIndex < @LastIndex

Next look at the SQL statement that takes the second half of the data table. This statement is the same as the principle of the previous statement algorithm, except that the method is slightly different.

First, remove the primary key value from all records after the current page, select the minimum value, and then remove the previous @PageSize record with the primary key value less than the minimum value.

SELECT * FROM (
SELECT Top @PageSize @QueryFields
From @TableName
WHERE @PrimaryKey < (
SELECT MIN (@PrimaryKey) from (
SELECT Top (@RecordCount-@PageSize * (@PageIndex + 1)) @PrimaryKey
From @TableName
WHERE @Condition
ORDER BY @PrimaryKey DESC
) TableA
) WHERE @Condition
ORDER BY @PrimaryKey DESC
) TableB
ORDER BY @PrimaryKey ASC


The SQL statement that takes the first half of the data table and the second half of the record is written separately, because when you use the SQL statement that takes the first half of the record, the number of records in front of the current page increments with the page size, and we want to remove the value of their primary key field from those records and then select the maximum value from the record. As a result, paging speeds will slow as the number of pages increases. So I didn't do this, but I chose an algorithm that was faster when the current page index was larger than the Middle page index (@MiddleIndex < @PageIndex) with the increased number of pages. Thus, assuming that all the pagination is divided into the front, the middle and the next three parts, the first and the most behind the paging speed is the fastest, the most intermediate paging speed slowest.

For example: @PageIndex = 3, red--> yellow--> Blue



(4) @PageIndex >= @LastIndex

When you take the last page of records, you first calculate the number of records on the page, as a condition of the top statement, and not directly with the top @PageSize, so that the record is not just the last page. In fact, many websites do this.

SELECT * FROM (
SELECT Top (@RecordCount-@PageSize * @LastIndex) @QueryFields
From @TableName
WHERE @Condition
ORDER BY @PrimaryKey DESC
) TableA
ORDER BY @PrimaryKey ASC


For example: @PageIndex = 4