Performance Comparison of Three paging modes of sqlserver

Create a clustered index for the CreateTime column in The Liwu_Items table

First, the paging syntax unique to sqlserver2005
Copy codeThe Code is as follows:
Declare @ page int
Declare @ pagesize int
Set @ page = 2
Set @ pagesize = 12

Set statistics io on
SELECT a. * FROM (
SELECT ROW_NUMBER () OVER (order by B. CreateTime DESC) AS [ROW_NUMBER], B .*
FROM [dbo]. [Liwu_Items] AS B) AS
WHERE a. [ROW_NUMBER] BETWEEN @ pagesize + 1 AND (@ page * @ pagesize)
Order by a. [ROW_NUMBER]

Result:

(12 rows affected) Table 'liwu _ Items '. 1 scan count, 7 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
Logical reads are 7 times

Execution Plan:

The main overhead is clustered index scanning.

Second, two top orders are arranged in the forward and reverse order, and a total of other subqueries are used to implement paging,
Copy codeThe Code is as follows:
Declare @ page int
Declare @ pagesize int
Set @ page = 2
Set @ pagesize = 12

Set statistics io on
Select * from (
Select top (@ pagesize) * from
(Select top (@ page * @ pagesize) * from Liwu_Items order by CreateTime desc)
Order by CreateTime asc) B
Order by CreateTime desc

Result

(12 rows affected) Table 'liwu _ Items '. 1 scan count, 7 logical reads, 0 physical reads, 317 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads.
Execution Plan

The execution plan is similar to the first one, but the two orders occupy a large amount of resources.

The third is the most spam, which is implemented using the not in clause, as follows:
Copy codeThe Code is as follows:
Declare @ page int
Declare @ pagesize int
Set @ page = 2
Set @ pagesize = 12

Set statistics io on
Select top (@ pagesize) * from Liwu_Items
Where ItemId not in (
Select top (@ page-1) * @ pagesize) ItemId from Liwu_Items order by CreateTime desc)
Order by CreateTime Desc

Result

(12 rows affected) Table 'worktable '. 1 scan count, 70 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'liwu _ Items '. Scan count 2, logical read 18 times, physical read 0 times, pre-read 0 times, lob logical read 0 times, lob physical read 0 times, lob pre-read 0 times.
The performance is the worst. The two tables are processed with high logic reading and perspiration.

Execution Plan

This execution plan cannot be understood. nested loops and table off-line occupy a large amount of resources.

Conclusion: The second paging method is similar to the first paging method, but the second method can be used for SQL Server or access in earlier versions.