Truly efficient paging queries for SQL Server (multiple scenarios)

Source: Internet
Author: User

Yuanwen:http://www.jb51.net/article/35212.htm

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)

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.