Paging control-for SQL Server. It takes only 15 ms to 900 ms for millions of data.

Download the paging control (including the source code and demo)

Paging control usage

Misunderstandings about Paging
Misunderstanding 1: During paging, the efficiency is high only when the stored procedure is used.
Misunderstanding 2: The role of indexes is ignored.

The last two articles seem to be not very detailed. Here we will detail the paging algorithm used in the paging control, that is, the SQL statement.

Paging is generally divided into four situations

1. Sorting by single field. There are no duplicate values in the sorting field.
2. Sort individual fields with duplicate values.
3. Multi-field sorting. The last sorting field has no repeated values.
4. Multi-field sorting. The last sorting field has duplicate values.

In both cases 2nd and 4, you can add a sorting field (such as the primary key) to convert it to the third case.

Therefore, two paging algorithms are set for pages 1 and 3.

1. Sorting by single field. There are no duplicate values in the sorting field.

Formula:

1 declare @ Col int
2
3 select top {pagesize * (PageIndex-1) + 1} @ Col = [sort field]
4 From [Table Name ‑ view name]
5 [where query conditions]
6 order by [sorting field] ASC sort DESC
7
8 select top pagesize fields to be displayed
9 from [Table Name ‑ view name]
10 where [sorting field]> = @ col
11 [and query conditions]
12 order by [sorting field] ASC sort DESC
Take the Products table in the northwind database as an example. Assume that 10 pieces of data are displayed on one page, and categoryid = 3 is the query condition. The SQL statement is
Declare @ Col int

Select top 11 @ Col = productid from products where categoryid = 3 order by productid DESC

Select top 10 * from products where productid> = @ Col and categoryid = 3 order by productid DESC
Note:
The definition of the first line needs to be modified according to the field type, which is troublesome, but it can be handed over to the paging control. The user only needs to set the attributes of the paging control.
Rows 5 and 11th can be added here if you need to add query conditions.

The third line is a "positioning", which is unique to SQL Server and a tolerance of SQL Server. In the example of the Products table, after the first select statement is executed, @ Col records the values of the 11-bit records in the inverted order of productid in the records with categoryid = 3.

After the first select statement is located, the second SELECT statement can continue to search for data based on the "position.

Although productid (primary key) is used for sorting In the example, it does not mean that this algorithm can only be sorted by the primary key, and can be used for all fields, but it must comply with the first situation, that is, "There is only one sorting field and there is no duplicate value in the sorting field "!

3. Multi-field sorting. The last sorting field has no repeated values.

What if the products table wants to be sorted by the unitprice field? The above algorithm is not suitable. We need to use another algorithm, which is similar to the reverse method, but I have made some optimizations.

Formula:
Select [fields to be displayed] from [Table Name preview view name] Where [primary key field] In

(Select top pagesize [primary key field] From
(Select top {pagesize * pageindex} [primary key field], [sort field] From -- there are several sort fields to write several fields

[Table Name ‑ view name]
[Where query condition]
Order

[Sorting Field 1] ASC shortdesc,
[Sorting Field 2] DESC sort ASC,

[Primary key field] ASC includesc
) As AA
Order
[Sort Field 1] DESC sort ASC, -- if the above is inverted, then here is the forward order, the same below
[Sorting Field 2] ASC shortdesc,

[Primary key field] DESC shortasc

)

Order
[Sorting Field 1] ASC shortdesc, -- if the above is an inverted order, it is a forward order.
[Sorting Field 2] DESC sort ASC,

[Primary key field] ASC includesc
Take the Products table in the northwind database as an example. If 10 pieces of data are displayed on a page and categoryid = 3 is the query condition, the unitprice field may have repeated values in reverse order, therefore, add a sorting field-productid, that is, sort by unitprice DESC and productid. If you want to display the data on the second page, the SQL statement is
Select * from products where productid in
(Select top 10 productid from
(Select top 20 productid, unitprice from
Products
Where categoryid = 3
Order
Unitprice DESC,
Productid
) As AA
Order
Unitprice ASC, -- if the above is in reverse order, it is in positive order, the same below
Productid DESC
)
Order
Unitprice DESC, -- if the above is an inverted order, it is a forward order.
Productid
Note:
1. Add the query condition once.
2. Does ASC shortdesc seem a bit dizzy? Well, that's right.
3. The most important thing is the third SELECT statement. It takes the data from the first data to the page to be displayed. It can be seen that the more records the Top N will be, therefore, the extracted data must be simplified and only the fields (primary key fields and sorting fields) required for sorting are written ).
4. The second SELECT statement is to remove the data from the page that is not needed before, and only retain the data in the page number to be displayed.
5. In the first select statement, use the primary key field in () to extract other required fields.
6. This paging algorithm has a small bug, that is, several more records will be generated when the last page is displayed, but this bug has been fixed in the paging control, the paging algorithm on the last page uses a special SELECT statement.
7. Efficiency: there is no problem with indexing. The previous article has already been tested.
8. This algorithm is "invasive", that is, it requires that the table must have a primary key and cannot be a joint primary key, leading to the use of in to query data. However, the primary key itself is not required to be able to be sorted.

Test Results
Number of records: 2523136.
Five records are displayed on one page.

// Paging algorithm 1 sorts a single field and the sorting field is a clustered index.
// Within 15 ms on the 1000 page
// Within 30 ms on the 10000 page
// Within 50000 pages, more than 100 milliseconds
// Within 100000 pages, more than 200 milliseconds
// The last page jumps to more than 4 seconds for the first time
// The last few pages flip forward for 1 second, 156 milliseconds

// The page number takes a long time to jump to a large range, but it is also less than 1 second. At the same time, the memory occupied by SQL Server is increased by 120 MB. The last page reaches 320 MB.

========================================================== ======================================
The following is the paging of multiple sorting fields. The sorting fields are unitprice and productid.

// Paging algorithm 2: The index-free homepage is 8 seconds and 187 milliseconds.
// Within 2 seconds and 812 milliseconds for 10 pages
// If the speed is too slow, I will not test it.

// Page 2 Non-clustered index unitprice homepage 468 Ms
// Within 2 seconds and 671 milliseconds for 10 pages
// If the speed is too slow, I will not test it.

// Paging algorithm 2 unclustered index unitprice, productid homepage 500 ms
// Within 2 seconds and 796 milliseconds for 10 pages
// Within 4 seconds, 100 ms, 796 pages
// If the speed is too slow, I will not test it.

// Paging algorithm 2 unclustered index unitprice, productid DESC homepage 500 ms
// 0-15 ms for 10 pages
// Within 15-46 milliseconds on the 100 page
// Within 1000 pages, milliseconds
// Within 10000 pages, 100 milliseconds
// Within 50000 pages,-milliseconds
// Within 100000 pages, 900 milliseconds
// The last few pages jump to 4 seconds for the first time, 421 milliseconds
// The last few pages are consecutive pages turning forward for 4 seconds 375 milliseconds

// Page number jump in a large range takes a long time, but it is also less than 1 second,
// The Memory occupied by SQL Server is increased by about MB.