Three simple ways to test the efficiency of paging

The three paging schemes in this article come from:

Just do a larger amount of data, a different location page comparison.

Create a table:

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) not NULL,
[FirstName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[LastName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL,
[Country] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL,
[Note] [nvarchar] (Watts) COLLATE chinese_prc_ci_as NULL
) on [PRIMARY]
Go

Insert data: (1 million article)

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 Scheme one: (using not in and select top pagination)
Statement form:

SELECT Top Page Size *
From TestTable
WHERE (ID not in
(SELECT top Page size * Pages ID
From table
Order by ID)
ORDER BY ID

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

Paging Scheme two: (with ID greater than how much and select top pagination)

SELECT Top Page Size *
From TestTable
WHERE (ID >
(SELECT MAX (ID)
From (SELECT top page size * Pages ID
From table
Order by ID) as T)
ORDER BY ID

Paging Scenario Three: (using SQL Cursor stored procedure paging)

CREATE PROCEDURE Xiaozhengge
@sqlstr nvarchar (4000),--query string
@currentpage int,--nth page
@pagesize INT--Number of rows per page
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1, @rowcount = @rowcount output
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF

Test results:
The test is 10 per page, three digits in sequence for three scenarios, the time required for the results, in seconds:
2nd page: 18,10,29
NO. 500 page: 12,8,21
No. 50000 page: 16,18,22
No. 500000 page: 24,16,22
The main purpose of this test is to test the efficiency of page-flipping of different parts of large data volumes. It was supposed to be a linear result, and it turned out to be a strange change. Multiple test results error in 1, 2 seconds, the estimated SQL Server for paging is also based on different location optimization. Look at the query analysis, the main cost or order by, this is the primary key, if not a primary key, or a string, estimated more slowly.
Because there are other things to be busy, and did not do further testing, interested friends can continue to do 100,000, no index, string content of various tests, remember to tell me the results.