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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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_Where IF ISnull (@sys_Order, ') <> ' BEGIN set @new_order1 = ' Order by ' + Replace (@sys_Order, ' desc ', ') set @new_order 1 = Replace (@new_order1, ' ASC ', ' desc ') Set @new_order2 = ' ORDER by ' + @sys_OrderENDELSEBEGIN set @new_order1 = ' OR DER 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_PageSiz e) Endset @sql = ' SELECT ' + @sys_fields + ' from ' + @sys_Table + ' W1 ' + ' where ' + @sys_Key + ' in (' + ' select t Op ' + 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
Original address: http://www.jb51.net/article/35213.htm
Efficient SQL Server paging query (reprint)