Efficient paging query for SQL Server (recommended)

SQL Server database paging query has been SQL Server's short board, Idle to do nothing, think of several methods, assuming that there is a table article, Field ID, year ... (other omitted), Data 53,210 (customer real data, amount of small), page 30 per page, query page 1500th (that is, 第45001-45030条 data), field ID clustered index, Year no index, SQL Server version: 2008R2

The first scenario, the simplest and the most common method:

The code is as follows:

Select top article WHERE ID not in (SELECT top 45000 ID from Article ORDER by year DESC, ID DESC) Order by year D Esc,id DESC

Average 100 times required: 45s

The second scenario:

The code is as follows:

SELECT * 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

Average 100 times required: 138S

The third scenario:

The code is as follows:

SELECT * FROM article W1,
(
SELECT TOP 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

Average 100 times required: 21S

The fourth option:

The code is as follows:

SELECT * FROM article W1
WHERE ID in
(
SELECT Top 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 100 times required: 20S

The fifth option:

The 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

Average 100 times required: 15S

Query 第1000-1030条 Records

The first scenario:

The code is as follows:

Select top $ * FROM article WHERE ID ' not ' in (select top-ID from Article ORDER by year DESC, ID DESC) Order by year DE Sc,id DESC

Average 100 times required: 80s

The second scenario:

The code is as follows:

SELECT * 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

Average 100 times required: 30S

The third scenario:

The code is as follows:

SELECT * FROM article W1,
(
SELECT TOP 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 100 times required: 12S

The fourth option:

The code is as follows:

SELECT * FROM article W1
WHERE ID in
(
SELECT Top 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

Average 100 times required: 13S

The fifth option:

The code is as follows:

Select W2.N, w1.* from article W1, (select TOP 1030 row_number () Up (ORDER by year DESC, id DESC) n, id from article) W2 WHERE w1.id = w2.id and W2.N > ORDER by W2.N ASC

Average 100 times required: 14S

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

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

The 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 outputs
@RCount int output,--total number of records 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 conditions
@sys_Order nvarchar (100),--sort field
@sys_Begin int,--Start position
@sys_PageIndex int,--current page
@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 @[email protected]_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 pages to the current page if the current number of pages entered is greater than the actual total page count
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

Efficient paging query for SQL Server (recommended)