Efficient SQL Server paging query (recommended) _mssql

Source: Internet
Author: User

The first scenario, 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 option:

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

Scenario Fourth:

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

Scenario Fifth:

Copy Code code as follows:

Select W2.N, w1.* from ARTICLE W1, (Select up 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 1030 * to ARTICLE ORDER by year DESC, ID DESC) F, f.year ASC , f.id DESC) s ORDER by S.year Desc,s.id DESC

Average Enquiry 100 Time: 30S

The third option:

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

Scenario Fourth:

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

Scenario Fifth:

Copy Code code as follows:

Select W2.N, w1.* from ARTICLE W1, (Select up 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

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.