Paging "test" of SQL SERVER Big Data

Source: Internet
Author: User

I use 5 million of the data to test, interested people can try tens;

1. Add a lot of data first

SQL SERVER millions data test "measurement"

2. Normal paging

A,row_number () over

 select  *   (select  row_number () over  ( Span style= "color: #0000ff;" >order  as   row,  *  from Users as   as   TT  where  tt.row between  999000  and  1000000 ; 

Test: "Millions" first page time:126MS, 100W time 1073MS, close to the first page 10 times times,500W time 58696MS 100W 55 times times , 466 times times the first page .

B, two times top page

SELECT TOP  + * fromUsersWHEREUserid not inch(SELECT TOP( + *( the - 1) UserID fromUsersORDER  byUserID)ORDER  byUserID;

Test: "Millions" first page time:140MS, 100W time 1190MS, 500W time 5526MS 100W 5 times times, the first page 39 times times.

Why is the top top more efficient than row_number () when I test the acquisition (500W)?

At least: 100W of data can be used row_number () Such paging, more elegant efficiency is slightly better, reluctantly acceptable, the data is larger, how to optimize the code is useless, this involves the database computing power, Similar to both of the pass.

2. Optimize paging ( first time )

Paging "test" of SQL SERVER Big Data

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.