The method of paging query has a lot of many, here I also join a member.
SQL Server has a setting of set rowcount, which means that the processing of the command is stopped to process the command after the number of rows specified, using this feature, we can use it to achieve high in a 10 million-row data table Performance paging query. Let's talk about the implementation:
1. Let's assume that there is a primary key field ID (integer type) that has established an index in Table, and we will take the data according to this field.
2, the size of the page we put in @PageSize
3, the current page number we put in @currentpage
4. How to let the record pointer quickly scroll to the line we have to take, this is the key! With SET ROWCOUNT, we are easy to achieve.
5. If we successfully scroll the record pointer to the beginning of the beginning of the data we have to take, then we record the value of the ID field of the record, then use Top and conditions, we are easy to get specified The data on the page. Of course, with set rowcount, do we still use TOP?
Take a look at how to help us:
Declare
@ID
int
Declare
@MoveRecords
int
-
@Currentpage and @Pagesize are incoming parameters
Set
@MoveRecords
=
@Currentpage
*
@PageSize
1
-
The following two lines implement the row to quickly scroll to the data we have to take, and record the ID.
Set
Rowcount
@MoveRecords
SELECT
@ID
=
Id
From
Table1
ORDER
BY
Id
Set
Rowcount
@PageSize
-
That hate to reduce trouble to use *, but here is convenient for explanation, temporarily use
SELECT
*
From
Table1
WHERE
Id
> =
@ID
ORDER
BY
Id
Set
Rowcount
0
You can try it, in a table 1,000 W record, turn the page to page 100 (100 per page), see how fast!
Http://name-lh.cnblogs.com/archive/2006/03/08/346059.html
