Efficient paging query for SQL Server (recommended)

Source: Internet
Author: User

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:


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:

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

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

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

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

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

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

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

The code is as follows:


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:

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]
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

Efficient paging query for SQL Server (recommended)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.