Efficient SQL Server paging query (recommended) _mssql

The first scenario, the simplest, the common method:

Copy Code code as follows:

Select Top * from ARTICLE WHERE ID. (select top 45000 ID to ARTICLE order by year DESC, id DESC) Esc,id DESC

Average Enquiry 100 Time: 45s

The second option:

Copy Code code as follows:

SELECT * FROM (select top 45030 * from ARTICLE, DESC, ID DESC) F ORDER by F.year ASC, F.id DESC) s ORDER by S.year Desc,s.id DESC

Average Enquiry 100 Time: 138S

The third option:

Copy Code code as follows:

SELECT * from ARTICLE W1,
(
SELECT Top ID from
(
SELECT top 50030 ID, year from ARTICLE, 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 Enquiry 100 Time: 21S

Scenario Fourth:

Copy Code code as follows:

SELECT * FROM ARTICLE W1
WHERE ID in
(
SELECT Top ID from
(
SELECT top 45030 ID, year from ARTICLE, DESC, id DESC
) w ORDER by w.year ASC, w.id ASC
)
ORDER by W1. Year DESC, W1.id DESC

Average Enquiry 100 Time: 20S

Scenario Fifth:

Copy Code code as follows:

Select W2.N, w1.* from ARTICLE W1, (Select up 50030 row_number () over (order by year DESC, id DESC) n, IDs from ARTICLE ) W2 WHERE w1.id = w2.id and W2.N > 50000 order by W2.N ASC

Average Enquiry 100 Time: 15S

Query 第1000-1030条 Records

First scenario:

Copy Code code as follows:

Select Top * from ARTICLE WHERE ID. (select Top 1000 ID to ARTICLE order by year DESC, id DESC) Sc,id DESC

Average Enquiry 100 Time: 80s

The second option:

Copy Code code as follows:

SELECT * FROM (SELECT top 1030 * to ARTICLE ORDER by year DESC, ID DESC) F, f.year ASC , f.id DESC) s ORDER by S.year Desc,s.id DESC

Average Enquiry 100 Time: 30S

The third option:

Copy Code code as follows:

SELECT * from ARTICLE W1,
(
SELECT Top ID from
(
SELECT Top 1030 ID, year from ARTICLE, 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 Enquiry 100 Time: 12S

Scenario Fourth:

Copy Code code as follows:

SELECT * FROM ARTICLE W1
WHERE ID in
(
SELECT Top ID from
(
SELECT Top 1030 ID, year from ARTICLE, DESC, id DESC
) w ORDER by w.year ASC, w.id ASC
)
ORDER by W1. Year DESC, W1.id DESC

Average Enquiry 100 Time: 13S

Scenario Fifth:

Copy Code code as follows:

Select W2.N, w1.* from ARTICLE W1, (Select up 1030 row_number () over (order by year DESC, id DESC) n, IDs from ARTICLE) W2 WHERE w1.id = w2.id and W2.N > 1000 ORDER by W2.N ASC

Average Enquiry 100 Time: 14S

This shows that in front of the query number of pages, the efficiency of 3>4>5>2>1, 5>4>3>1>2 after the page numbers, and then according to user habits, the general user's search only to see the first few pages, so choose 3 4 5 options can be, If a comprehensive consideration of scenario 5 is the best choice, but note that SQL2000 does not support the Row_number () function, due to time and condition constraints do not do more in-depth, broader testing, interested can be carefully studied.

The following is a paging stored procedure written under the fourth scenario:

Copy Code code 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 number of pages
@RCount int output,--Total record number outputs
@sys_Table nvarchar (100),--Query table name
@sys_Key varchar (50),--primary key
@sys_Fields nvarchar (500),--Query field
@sys_Where nvarchar (3000),--Query criteria
@sys_Order nvarchar (100),--Sort fields
@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)--assigns the actual total number of pages to the current page number if the current page number is greater than the actual total number of pages
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