Several common ways to compare SQL paging efficiency (RPM)

Http://www.cnblogs.com/iamowen/archive/2011/11/03/2235068.html

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 (RPM)