Test the efficiency of three paging Methods

The three paging schemes in this article come from:
Http://blog.csdn.net/lihonggen0/archive/2004/09/14/103511.aspx
We only compared the larger data volume and different page locations.

Create a table:

Create   Table   [ Testtable ] (
  [ ID ]   [ Int ]   Identity ( 1 , 1 ) Not   Null ,
  [ Firstname ]   [ Nvarchar ] ( 100 ) Collate chinese_prc_ci_as Null ,
  [ Lastname ]   [ Nvarchar ] ( 100 ) Collate chinese_prc_ci_as Null ,
  [ Country ]   [ Nvarchar ] ( 50 ) Collate chinese_prc_ci_as Null ,
  [ Note ]   [ Nvarchar ] ( 2000 ) Collate chinese_prc_ci_as Null  
) On   [ Primary ]
Go

 

Insert data: (1 million)

Set   Identity_insert Testtable On
Declare @ I Int
Set @ I = 1
While @ I <= 1000000
Begin
Insert   Into Testtable ( [ ID ] , Firstname, lastname, country, note) Values (@ I, ' Firstname_xxx ' , ' Lastname_xxx ' , ' Country_xxx ' , ' Note_xxx ' )
Set @ I = @ I + 1
End
Set   Identity_insert Testtable Off

------------------------------------

Paging solution 1: (use not in and select top pages)
Statement format:

Select   Top Page size *
From Testtable
Where (ID Not   In
( Select   Top Page size * Page number ID
From Table
Order   By ID ))
Order   By ID

-------------------------------------

Paging solution 2: (use the ID greater than the number and select top pages)

Select   Top Page size *
From Testtable
Where (ID >
( Select   Max (ID)
From ( Select   Top Page size * Page number ID
From Table
Order   By ID) As T ))
Order   By ID

Paging solution 3: (using SQL cursor Stored Procedure paging)

Create Procedure Xiaozhengge
@ Sqlstr Nvarchar ( 4000 ), -- Query string
@ Currentpage Int , -- Page n
@ Pagesize Int   -- Number of lines per page
As
Set Nocount On
Declare @ P1 Int , -- P1 is the cursor ID
@ Rowcount Int
Exec Sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1 , @ Ccopt = 1 , @ Rowcount = @ Rowcount output
Select   Ceiling ( 1.0 * @ Rowcount / @ Pagesize) As Total number of pages -- , @ Rowcount as total number of rows, @ currentpage as current page
Set @ Currentpage = (@ Currentpage - 1 ) * @ Pagesize + 1
Exec Sp_cursorfetch @ P1, 16 , @ Currentpage, @ pagesize
Exec Sp_cursorclose @ p1
Set Nocount Off

Test Result:
the test results are 10 entries per page. The three numbers are the time required for the three schemes to output the results in sequence, in seconds:
page 2nd: 500th, 29
50,000th pages: 500,000th, 21
pages:, 22
pages: 24, 16, 22
the main purpose of this test is to test the page turning efficiency of different parts of large data volumes. I thought it should be a linear result, and the results found that the change was very strange. The result error of Multiple tests is within one or two seconds. It is estimated that the SQL Server optimizes page turning based on different locations. After reading the query analysis, the main cost is order by, which is the primary key. If it is not a primary key or a string, it is estimated to be slower.
there are other things to be busy with and no further tests have been conducted. If you are interested, you can continue to perform various tests on 0.1 million items, no indexes, and string content, tell me the result.