Several common ways to compare SQL paging efficiency-GO

Source: Internet
Author: User

The original address: several common SQL paging method efficiency comparison

Paging is important, and interviews are met. You may wish to review and summarize.

1. Create a test environment, (inserting 1 million data takes about 5 minutes).

Create DATABASE DBTest
Use DBTest

--Create a test table
CREATE TABLE Pagetest
(
ID int identity (a) is not NULL,
COL01 int NULL,
COL02 nvarchar () null,
COL03 datetime NULL
)

--1 Record Set
DECLARE @i int
Set @i=0
while (@i<10000)
Begin
Insert INTO Pagetest Select CAST (Floor (rand () *10000) as int), Left (newid (), ten), GETDATE ()
Set @[email protected]+1
End


2. Several typical paging SQL, the following example is 50 per page, 198*50=9900, take the 199th page of data.

--Writing 1,not In/top
Select Top * from Pagetest
where ID not in (the select top 9900 ID from the pagetest ORDER by ID)
ORDER BY ID




--Writing 2,not exists
Select Top * from Pagetest
Where NOT EXISTS
(select 1 from (select top 9900 ID from Pagetest ORDER by id) a where a.id=pagetest.id)
ORDER BY ID

--Notation 3,max/top
Select Top * from Pagetest
Where id> (select Max (ID) from (the Select top 9900 ID from the pagetest ORDER by ID) a)
ORDER BY ID

--Notation 4,row_number ()
Select Top * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900

SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900 and rownumber<9951

SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where RowNumber between 9901 and 9950

---5, in the csdn on a post to see, Row_number () variant, not based on the existing field generated record ordinal, first by criteria filtering and sequencing, and then on the result set to a constant column to generate the record sequence number
SELECT *
From (
Select Row_number () over (order by Tempcolumn) rownumber,*
From (select Top 9950 tempcolumn=0,* from Pagetest where 1=1 order by ID) a
) b
where rownumber>9900

3. Test under 10,000, 100,000 (take 1990 pages), 100 (take 19900 pages) record set.

Test sql:

DECLARE @begin_date datetime
DECLARE @end_date datetime
Select @begin_date = getdate ()

< YOUR code.....>

Select @end_date = getdate ()
Select DateDiff (MS, @begin_date, @end_date) as ' milliseconds '

10,000: The basic feeling is not different.

100,000:

1 million:

4. Conclusion:

1.max/top,row_number () is a good paging method. Compared to Row_number () only supports sql2005 and above, Max/top has better portability and can be used for sql2000,access.

2.not exists feeling is a little more efficient than not.

3.row_number () 3 different ways of writing efficiency looks similar.

The variant of 4.row_number () is based on the fact that my test efficiency is not good. The original posts here http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html

PS. The page sort above is based on the self-increment field ID. The test environment also provides a int,nvarchar,datetime type field, or you can try it. However, the efficiency of large data sequencing that is not indexed for non-primary keys should be very unsatisfactory.

5. Simply encapsulate the rownumber,max/top in a stored procedure.

ROWNUMBER ():

CREATE proc [dbo]. [Spsqlpagebyrownumber]
@tbName varchar (255),--table name
@tbFields varchar (1000),--return field
@PageSize int,--page size
@PageIndex int,--page
@strWhere varchar (1000),--Query condition
@StrOrder varchar (255),--Sort condition
@Total int output--Returns the total number of records
As
DECLARE @strSql varchar (5000)--the subject sentence
declare @strSqlCount nvarchar (500)--Query the total number of records the subject sentence

Total number of records-----------------------------
If @strWhere! = "
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName + ' where ' + @strWhere
End
Else
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName
End
--------------Paging------------
If @PageIndex <= 0
Begin
Set @PageIndex = 1
End

Set @strSql = ' SELECT * FROM (select Row_number () over (' [email protected]+ ') rowId, ' + @tbFields
+ ' from ' + @tbName + ' where 1=1 ' + @strWhere + ') TB where Tb.rowid > ' +str ((@PageIndex-1) * @PageSize)
+ ' and Tb.rowid <= ' +str (@PageIndex * @PageSize)

EXEC sp_executesql @strSqlCount, N ' @TotalCout int output ', @Total output
EXEC (@strSql)

Max/top: (Simply write down, need to satisfy the primary key field name is "id")

CREATE proc [dbo]. [Spsqlpagebymaxtop]
@tbName varchar (255),--table name
@tbFields varchar (1000),--return field
@PageSize int,--page size
@PageIndex int,--page
@strWhere varchar (1000),--Query condition
@StrOrder varchar (255),--Sort condition
@Total int output--Returns the total number of records
As
DECLARE @strSql varchar (5000)--the subject sentence
declare @strSqlCount nvarchar (500)--Query the total number of records the subject sentence

Total number of records-----------------------------
If @strWhere! = "
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName + ' where ' + @strWhere
End
Else
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName
End
--------------Paging------------
If @PageIndex <= 0
Begin
Set @PageIndex = 1
End

Set @strSql = ' Select Top ' +str (@PageSize) + ' * from ' + @tbName + '
Where id> (select Max (ID) from (select Top ' +str ((@PageIndex-1) * @PageSize) + ' ID from ' + @tbName + ' [email protected]+ ' A
' [email protected]+ ']

EXEC sp_executesql @strSqlCount, N ' @TotalCout int output ', @Total output
EXEC (@strSql)

The garden found max/top in such a version, looks very powerful, http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html

Call:

DECLARE @count int


Select @count

Several common ways to compare SQL paging efficiency-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.