A simple test of the efficiency of three paging methods

Just compared the larger amount of data and pages at different locations.

Create 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, &#39;FirstName_XXX&#39;,&#39;LastName_XXX&#39;,&#39;Country_XXX&#39;,&#39;Note_XXX&#39;)
    set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF

Paging solution one: (Use Not In and SELECT TOP paging)

Statement form:

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

Paging scheme two: (Use ID greater than and SELECT TOP paging)

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

Paging solution three: (Using SQL cursor stored procedure paging)

create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off

Test Results:

The tests are all 10 items per page. The three numbers are the time it takes for the three solutions to produce results. The unit is seconds:

Page 2: 18, 10, 29

Page 500: 12, 8, 21

Page 50000: 16, 18, 22

Page 500000: 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, but found that the change was strange. After testing several times, the error is within 1 or 2 seconds. It is estimated that SQL Server is also optimized for page turning according to different positions. After looking at the query analysis, the main cost is still order by, which is still the primary key. If it is not the primary key, or it is a string, it will probably be slower.

Because there are other things to be busy with, no further testing has been done. Friends who are interested can continue to do various tests with 100,000 items, no index, and string content. Remember to tell me the results.

The above is the detailed content of A simple test of the efficiency of three paging methods. For more information, please follow other related articles on the PHP Chinese website!