Efficient SQL Server paging query

SQL Server database paging query has been a short board of SQL Server, idle, come up with several methods, assuming that there are table article, Field ID, year ... (other omitted), Data 53,210 (customer real data, quantity is not large), paging query each page 30, query page 1500th (ie 第45001-45030条 data), field ID clustered index, Year no index, SQL Server version: 2008R2

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