Efficient SQL Server paging query (recommended)

The first solution, the simplest and common method:

Copy codeThe Code is as follows:
Select top 30 * from article where id not in (select top 45000 id from article order by year desc, id desc) order by year desc, ID DESC

Average query time: 45 s

Solution 2:

Copy codeThe Code is as follows:
SELECT * FROM (select top 30 * FROM (select top 45030 * from article order by year desc, id desc) f order by f. year asc, f. id desc) s order by s. year desc, s. ID DESC

Time required for an average of 100 queries: 138 S

Solution 3:

Copy codeThe Code is as follows:
SELECT * from article w1,
(
Select top 30 ID FROM
(
Select top 50030 ID, year from article order by year desc, ID DESC
) W order by w. year asc, w. ID ASC
) W2 WHERE w1.ID = w2.ID order by w1.YEAR DESC, w1.ID DESC

Time required for an average query of 100 times: 21 S

Solution 4:
Copy codeThe Code is as follows:
SELECT * from article w1
Where id in
(
SELECT top 30 ID FROM
(
SELECT top 45030 ID, year from article order by year desc, ID DESC
) W order by w. year asc, w. ID ASC
)
Order by w1.YEAR DESC, w1.ID DESC

Average query time: 20 S

Solution 5:

Copy codeThe Code is as follows:
SELECT w2.n, w1. * from article w1, (select top 50030 row_number () OVER (order by year desc, id desc) n, id from article) w2 WHERE w1.ID = w2.ID AND w2.n> 50000 order by w2.n ASC

Time required for an average query of 100 times: 15 S

Query records from 1000-1030

Solution 1:

Copy codeThe Code is as follows:
Select top 30 * from article where id not in (select top 1000 id from article order by year desc, id desc) order by year desc, ID DESC

Time required for an average query of 100 times: 80 s

Solution 2:
Copy codeThe Code is as follows:
SELECT * FROM (select top 30 * FROM (select top 1030 * from article order by year desc, id desc) f order by f. year asc, f. id desc) s order by s. year desc, s. ID DESC

Time required for an average query of 100 times: 30 S

Solution 3:

Copy codeThe Code is as follows:
SELECT * from article w1,
(
Select top 30 ID FROM
(
Select top 1030 ID, year from article order by year desc, ID DESC
) W order by w. year asc, w. ID ASC
) W2 WHERE w1.ID = w2.ID order by w1.YEAR DESC, w1.ID DESC

Average query time: 12 S

Solution 4:

Copy codeThe Code is as follows:
SELECT * from article w1
Where id in
(
SELECT top 30 ID FROM
(
SELECT top 1030 ID, year from article order by year desc, ID DESC
) W order by w. year asc, w. ID ASC
)
Order by w1.YEAR DESC, w1.ID DESC

Time required for an average query of 100 times: 13 S

Solution 5:
Copy codeThe Code is as follows:
SELECT w2.n, w1. * from article w1, (select top 1030 row_number () OVER (order by year desc, id desc) n, id from article) w2 WHERE w1.ID = w2.ID AND w2.n> 1000 order by w2.n ASC

Average query time: 14 S

It can be seen that when the query page number is top, the efficiency is 3> 4> 5> 2> 1, and the page number is back 5> 4> 3> 1> 2. Then, based on user habits, generally, the user's search only looks at the first few pages, so you can select the 3 4 5 solution. If you consider solution 5 comprehensively, it is the best choice, but note that SQL2000 does not support the row_number () function, since there is no more in-depth and extensive test on the restrictions of time and conditions, you can study it carefully if you are interested.

The following is a paging stored procedure based on the fourth solution:

Copy codeThe Code is as follows:
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [sys_Page_v2] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [dbo]. [sys_Page_v2]
GO

Create procedure [dbo]. [sys_Page_v2]
@ PCount int output, -- total page number output
@ RCount int output, -- output of the total number of records
@ Sys_Table nvarchar (100), -- query the table name
@ Sys_Key varchar (50), -- primary key
@ Sys_Fields nvarchar (500), -- query a field
@ Sys_Where nvarchar (3000), -- Query Condition
@ Sys_Order nvarchar (100), -- Sort Field
@ Sys_Begin int, -- start position
@ Sys_PageIndex int, -- current page number
@ Sys_PageSize int -- page size
AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON

IF @ sys_PageSize <0 OR @ sys_PageIndex <0
BEGIN
RETURN
END

DECLARE @ new_where1 NVARCHAR (3000)
DECLARE @ new_order1 NVARCHAR (100)
DECLARE @ new_order2 NVARCHAR (100)
DECLARE @ SQL NVARCHAR (4000)
DECLARE @ SqlCount NVARCHAR (4000)

DECLARE @ Top int

If (@ sys_Begin <= 0)
Set @ sys_Begin = 0
Else
Set @ sys_Begin = @ sys_Begin-1

If isnull (@ sys_Where, '') =''
SET @ new_where1 =''
ELSE
SET @ new_where1 = 'where' + @ sys_Where

If isnull (@ sys_Order, '') <>''
BEGIN
SET @ new_order1 = 'ORDER BY' + Replace (@ sys_Order, 'desc ','')
SET @ new_order1 = Replace (@ new_order1, 'asc ', 'desc ')

SET @ new_order2 = 'ORDER BY' + @ sys_Order
END
ELSE
BEGIN
SET @ new_order1 = 'order by id desc'
SET @ new_order2 = 'order by id asc'
END

SET @ SqlCount = 'select @ RCount = COUNT (1), @ PCount = CEILING (COUNT (1) + 0.0 )/'
+ CAST (@ sys_PageSize as nvarchar) + ') FROM' + @ sys_Table + @ new_where1

EXEC SP_EXECUTESQL @ SqlCount, n' @ RCount int output, @ PCount INT output ',
@ RCount OUTPUT, @ PCount OUTPUT

IF @ sys_PageIndex> CEILING (@ RCount + 0.0)/@ sys_PageSize) -- IF the input current page number is greater than the actual total page number, the actual total page number is assigned to the current page number.
BEGIN
SET @ sys_PageIndex = CEILING (@ RCount + 0.0)/@ sys_PageSize)
END

Set @ SQL = 'select' + @ sys_fields + 'from' + @ sys_Table + 'w1'
+ 'Where' + @ sys_Key + 'in ('
+ 'Select top '+ ltrim (str (@ sys_PageSize) + ''+ @ sys_Key + 'from'
+ '('
+ 'Select top' + ltrim (STR (@ sys_PageSize * @ sys_PageIndex + @ sys_Begin) + ''+ @ sys_Key + 'from'
+ @ Sys_Table + @ new_where1 + @ new_order2
+ ') W' + @ new_order1
+ ')' + @ New_order2

Print (@ SQL)

Exec (@ SQL)

GO