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 scheme, 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 type of programme：
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
Fourth Scenario：
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
Fifth Scenario：
Copy Code code as follows:
SELECT W2.N, w1.* from ARTICLE W1, (
SELECT top 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 * FROM (SELECT top 1030 * to 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 Enquiry 100 Time: 30S
The third type of programme：
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
Fourth Scenario：
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
Fifth Scenario：
Copy Code code as follows:
SELECT W2.N, w1.* from ARTICLE W1, (
SELECT Top 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
