Paging test for four types of SQL Server databases

Source: Internet
Author: User

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...

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.