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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe 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 :
Copy CodeThe code is as follows:
SELECT W2.N, w1.* from article W1, (
SELECT TOP 1030 row_number () over (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:
Copy CodeThe 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
Truly efficient paging queries for SQL Server (multiple scenarios)
