Paging test for four types of SQL Server databases

Set statistics Io on
Set statistics time on
Go

 

View code ---- 1
Print 'fist begin'
Declare @ time datetime
Declare @ Ms int
Set @ time = getdate ()
Select * from client. Client as C
Inner join
(
Select rownum, clientid from
(
Select row_number () over (order by clientid ASC) as rownum, clientid from client. Client
) As CC
Where cc. rownum between 10000 and 10500
) As T
On C. clientid = T. clientid
Set @ Ms = datediff (MS, @ time, getdate ())
Print @ Ms
Print 'fist end'
Go

 

Method 2----2
Print 'second in in'
Declare @ time datetime
Declare @ Ms int
Set @ time = getdate ()
Select * from
(
Select row_number () over (order by clientid ASC) as rownum, * from client. Client
)
As c Where C. rownum between 10000 and 10500
Set @ Ms = datediff (MS, @ time, getdate ())
Print @ Ms
Print 'second end'
Go

 

 

Method 3 -- 3

Print 'third in in'
Declare @ time datetime
Declare @ Ms int
Set @ time = getdate ()
Select top (500) * from client. Client
Where clientid not in (select top 9999 clientid from client. Client)

Set @ Ms = datediff (MS, @ time, getdate ())
Print @ Ms
Print 'third end'
Go

 

 

Method 4 print 'Fourth begin'
Declare @ time datetime
Declare @ Ms int
Set @ time = getdate ()
Select top (500) * from client. Client
Where clientid> (select max (clientid) from
(Select top 9999 clientid from client. client order by clientid) as C)

Set @ Ms = datediff (MS, @ time, getdate ())
Print @ Ms
Print 'Fourth end'
Go

 

Fist begin

(501 row (s) affected)
Table 'client'. Scan count 1, logical reads 2658, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row (s) affected)

SQL Server execution times:
CPU time = 31 MS, elapsed time = 218 Ms.

SQL Server execution times:
CPU time = 0 MS, elapsed time = 0 ms.
220

Fist end

Second begin

(501 row (s) affected)
Table 'client'. Scan count 1, logical reads 1155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row (s) affected)

SQL Server execution times:
CPU time = 16 MS, elapsed time = 158 Ms.
156

Second end

Third begin

(500 row (s) affected)
Table 'worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'client'. Scan count 2, logical reads 1166, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row (s) affected)

SQL Server execution times:
CPU time = 47 MS, elapsed time = 140 ms.
140
Third end

 

Fourth begin

(500 row (s) affected)
Table 'client'. Scan count 2, logical reads 1166, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row (s) affected)

SQL Server execution times:
CPU time = 16 MS, elapsed time = 230 Ms.

230

Fourth end

 

 

 

6 million data records

Personal test results

Scan count: 4 = 3> 1 = 2 3 is 2, others are 1

Logical reads: 2> 3 = 4> 1 (2658> 1166> 1155)

Actual Time: 4> 1> 2> 3 depends on the execution sequence.

Theoretical time: 2> 3> 4> 1 ???????

The data volume is not tested.

It is estimated that the data volume is a problem, and the big data should be quite different. If you are interested, test it.

Please provide a suggestion...