Test and comparison of three paging efficiency measures for SQL Server stored procedures

Many of my friends are interested in the paging technology of big databases. In SQL Server, stored procedures are given priority. I also use stored procedure distribution, and this is the second solution in this instance, next we will use examples to compare the efficiency of using Not In and select top pages, using the three paging technologies of ID greater than and select top pages, and using SQL cursor stored procedure paging.

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 cyclically: (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 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 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 results:

The test results are 10 entries per page, and the three numbers are the time required for the results of the three schemes, in seconds:

Page 2nd:, 29

500th pages:, 21

Page 50,000th:, 22

Page 500,000th: 24, 16, 22

The main purpose of this test is to test the page turning efficiency of different parts of the large data volume. 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.