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:

Average 100 times required: 138S

The third scenario:

The code is as follows:

SELECT * FROM article W1, (    select top with 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 with 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:

Average 100 times required: 15S

Query 第1000-1030条 Records

The first scenario:

The code is as follows:

Average 100 times required: 80s

The second scenario:

The code is as follows:

SELECT * FROM  

Average 100 times required: 30S

The third scenario:

The code is as follows:

SELECT * FROM article W1, (    select top with 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 with ID from             (                select top 1030 ID, year from article O Rder 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:

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] Gocreate PROCEDURE [dbo]. [SYS_PAGE_V2] @PCount int output,--Total number of pages @rcount int outputs,--Total records output @sys_table nvarchar (100),--query table name @sys_key VARC Har (50),--primary key @sys_fields nvarchar (500),--query field @sys_where nvarchar (3000),--Query condition @sys_order nvarchar (100),- -sort field @sys_begin int,--Start position @sys_pageindex int,--current page @sys_pagesize int--page size asset NOCOUNT ONSET Ansi_  WARNINGS onif @sys_PageSize < 0 OR @sys_PageIndex < 0BEGIN returnenddeclare @new_where1 NVARCHAR (DECLARE) @new_order1 NVARCHAR DECLARE @new_order2 NVARCHAR (+) DECLARE @Sql NVARCHAR (4000) DECLARE @SqlCount NVARCHAR (4000) DECLARE @Top intif (@sys_Begin <=0) set @sys_Begin =0else set @[email protected]_begin-1if ISNULL (@sys_Where, ' ') = ' Set @new_where1 = ' ELSE set @new_where1 = ' WHERE ' + @sys_WhereIF 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_OrderENDELSEBEGIN set @new_order1 = ' ORD ER by-id DESC ' SET @new_order2 = ' ORDER by id ASC ' endset @SqlCount = ' SELECT @RCount =count (1), @PCount =ceiling ((COUNT (1 ) +0.0)/' + CAST (@sys_PageSize as NVARCHAR) + ') from ' + @sys_Table + @new_where1EXEC sp_executesql @SqlCount, N ' @ rcount int output, @PCount int output ', @RCount output, @PCount outputif @sys_PageIndex > CEILING ((@RCount + 0.0)/@sys_PageSize)--if the current number of pages entered is greater than the actual total page count, the actual total page count is assigned to the current page begin SET @sys_PageIndex = CEILING ((@RCount +0.0)/@sys_PageSize ) Endset @sql = ' SELECT ' + @sys_fields + ' from ' + @sys_Table + ' W1 ' + ' where ' + @sys_Key + ' in (' + ' Select to P ' + 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_order2print (@sql) Exec (@sql) GO

Efficient paging query for SQL Server (recommended)