DataGrid connection ACCESS fast paging method (2) - SQL statement selection (ascended)
First, related concepts
In the Access database, a unique index is inevitable on a primary key (Primary Key, also known as the main index), so the value of the primary key field is not repeated. And the index page is sorted according to the value of the index column, and each index record contains a pointer to the data line it referenced. We can use the primary key to achieve the positioning of a record, so that the records you want to display on a paging are quickly removed.
For example, suppose the primary key field is an Integer type, the record in the database table has been ranked in the value of the primary key field, then the value of the value of the primary key field "11" is sure to have a record in the value of "12" (assuming There is a record of the "12" of the primary key in the database table). If the primary key field does not have a unique constraint, there may be a record of the value of "11" in two or more primary key fields in the database table, so that the front and rear position between these records cannot be determined.
Let's take a look at how to use the primary key to perform data segmentation query.
Second, ascending
(1)
@PageIndex
<=
@FirstIndex
Take the data on the first page is simple, and we can remove the records to display by using top @pageSize. Because the records in the data table have been ranked in the value of the primary key field, the ORDER BY clause is saved and the speed is faster.
Select Top @Pagesize @Queryfields
From @tablename
Where @condition
- Order by @PrimaryKey ASC
Figure:
@PageIndex
=
0
(2)
@FirstIndex
<
@PageIndex
<=
@MIDDleIndex
The SQL statement that records the first half of the data sheet and the SQL statement recorded in the subsequent part of the record can be effectively improved. Behind I explain this question in detail. Now look at the SQL statement that takes the first half of the record. First take the primary key value of all records before the current page, then select the maximum value, then remove the main key value greater than the front @PageSize strip record of the maximum value. It is worth noting that two ORDER BY @PrimaryKey ASC statements are eliminated here, respectively, in the most inside and the outer SELECT statement. As mentioned earlier, the records in the database table have been ranked in the value of the primary key field, so we don't have to draw snakes.
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
E.g:
@PageIndex
=
1, red -> yellow-> blue
(3)
@MIDDleIndex
<
@PageIndex <
@LastIndex
Next, look at the SQL statement recorded by the second half of the data table. The statement is the same as the principle of the previous statement algorithm, but the method is slightly different.
First take the primary key value of all records after the current page, then select the minimum, then take out the main key value less than the front @PageSize strip record of 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 reason why the SQL statement recorded in the first half of the data sheet and the semi-part record is written separately because the number of records in front of the current page is incremented with the number of pages, and we have to increase from these Remove the value of their primary key fields in the record, select the maximum. In this way, the page speed will slow down with the increase of the number of pages. So I didn't do this, but when the current page index greater than the intermediate page index (
@MIDDleIndex
<
@PageIndex
The algorithm is selected with the increase in paging speed with the increase of the number of pages. It can be seen that it is assumed that all pages are divided into front, middle and back three parts, the first and last paging speeds are the fastest, the most intermediate page speed is slower.
E.g:
@PageIndex
=
3, red -> yellow-> blue
(4)
@PageIndex
> =
@LastIndex
When you take the last page, you must first calculate the number of records of the page. As the condition of the TOP statement, you can't use TOP directly.
@PageSize
This taken the record is not just the last page. In fact, many websites do do this.
SELECT
*
From
(
SELECT TOP
(
@Recordcount
-
@PageSize
*
@LastIndex
)
@Queryfields
From @tablename
Where @condition
Order by @primaryKey DESC
TABLEA
Order by @primaryKey ASC
E.g:
@PageIndex
=
4
Author: Tripoli
