Research on paging algorithms for SQL stored procedures (supporting tens of millions of records)

1. Improved version of "Russian stored procedure"

CREATE procedure pagination1
(@ Pagesize int, -- page size, such as storing 20 records per page
@ Pageindex int -- current page number)
As set nocount on

Begin

Declare @ indextable table (id int identity (), nid int) -- Define table Variables
Declare @ PageLowerBound int -- defines the bottom code of this page.
Declare @ PageUpperBound int -- defines the top code of this page.

Set @ PageLowerBound = (@ pageindex-1) * @ pagesize
Set @ PageUpperBound = @ PageLowerBound + @ pagesize
Set rowcount @ PageUpperBound
Insert into @ indextable (nid) select gid from TGongwen where fariqi> dateadd (day,-365, getdate () order by fariqi desc
Select O. gid, O. mid, O. title, O. fadanwei, O. fariqi from TGongwen O, @ indextable t where O. gid = t. nid
And t. id> @ PageLowerBound and t. id <= @ PageUpperBound order by t. id

End

Set nocount off

Comments in the article:

The above stored procedures use the latest SQL server technology-Table variables. It should be said that this stored procedure is also a very good paging stored procedure. Of course, in this process, you can also write the table variables as temporary tables: Create Table # temp. But it is obvious that in SQL Server, using temporary tables does not use Quick table variables. So when I first started using this stored procedure, I felt very good and the speed was better than that of the original ADO. But later, I found a better method than this method.

In a sense, the efficiency is not too high.

2. Not in method:
Retrieve the N to M records from the publish table:

Select top M-n + 1 * From publish where (id not in (select top n-1 ID from publish ))

Keywords with ID publish table

Comments in the article:

When I saw this article at the time, I was truly inspired by the spirit and thought that the idea was very good. Later, I was working on an office automation system (Asp. net + C # + SQL Server), I suddenly think of this article, I want to modify this statement, this may be a very good paging storage process, so I searched for this article on the Internet. I did not expect this article to be found yet, however, I found a paging stored procedure based on this statement. This stored procedure is also a popular paging stored procedure.

If not in is used and not in is used, indexes cannot be used. Therefore, the efficiency is a little lower.

3. Max method:

Select top page size * From Table1 where ID>
(Select max (ID) from
(Select top (page number-1) * page size) ID from Table1 order by ID) as t)
Order by ID

Comments in the article:

We know that we can use max (field) or min (field) to extract the maximum or minimum values of almost any field, so if this field is not repeated, then, we can use the max or min of these non-repeated fields as the watershed to make them a reference object for separating each page in the paging algorithm. Here, we can use the operator ">" or "<" to accomplish this mission, so that the query statement conforms to the SARG format. For example:
Select top 10 * from table1 where id> 200

This is a little more efficient. But I don't know how max works, and how it works.

TIPS:

1. An efficient paging algorithm-Positioning method.

Declare @ pageSize int -- returns the number of records on a page
Declare @ CurPage int -- page number (page number) 0: First page;-1 last page.

Declare @ Count int
Declare @ id int

Set @ pageSize = 10
Set @ CurPage = 1

-- Positioning
If @ CurPage =-1
Begin
-- Last page
Set rowcount @ pageSize
Select @ id = newsID from newsTemp order by newsID
End

If @ CurPage> 0
Begin
Set @ Count = @ pageSize * (@ CurPage-1) + 1
Set rowcount @ Count
Select @ ID = newsid from newstemp order by newsid DESC
End

-- Return record
Set rowcount @ pagesize
Select * From newstemp where newsid <= @ ID order by newsid DESC

Set rowcount 0

Idea: This is the continuation of the above algorithm. That is to say, avoid using the not in and Max methods. That is, the idea: Select top 10 * From Table1 where ID> 200, locate -- that is, locate the critical point of paging. After finding it, the rest is easy to do.

Disadvantages: The values of Single-field sorting and sorting fields cannot be repeated (not necessarily repeated, but a few can be repeated ).

2. General method-the Reverse Method

Sometimes the disadvantage of the "Positioning Method" is unacceptable, but it does not matter. You can use this method.

Select * from table where ID in
(
Select top 10 ID from
(
Select top 20 ID, addeddate from table
Order by addeddate DESC
) As AA order by addeddate
)
Order by addeddate DESC

ID is the primary key and addeddate is the sorting field.

Disadvantage: a primary key is required.