Common Stored Procedure paging pkcompetition-simple test and analysis common stored procedure paging speed

Source: Internet
Author: User

Address: http://www.cnblogs.com/yangyy753/archive/2013/01/23/2872753.html

 

Data paging is a feature that we are no longer familiar with, and various paging Methods emerge one after another. Today, I will list some common stored procedures in pages and test the performance simply. This is a summary of the knowledge and a good idea for you. Don't talk nonsense. Let's get started ~~

1. First create a test table

 --  Create test table  Set Ansi_nulls On  Go  Set Quoted_identifier On  Go  Create   Table   [  DBO  ] . [  Testtable  ]  (  [  ID ]   [  Int  ]   Identity ( 1 , 1 ) Not   Null  ,  [  Testdate  ]   [  Datetime  ]   Not   Null   Constraint   [  Df_testtable_testdate  ]    Default ( Getdate  ()),  [  Name  ]   [ Nvarchar  ] ( 50 ) Collate chinese_prc_ci_as Not   Null  ,  [  Description  ]   [  Nchar  ] ( 50 ) Collate chinese_prc_ci_as Not   Null  ,  [  Ordercolum  ]   [  Float  ]   Not   Null  ,  Constraint   [  Pk_testtable ]   Primary   Key   Clustered  (  [  ID  ]   ASC  )  With (Ignore_dup_key =   Off ) On   [  Primary  ]  )  On   [  Primary  ] 

2. Insert 1000000 pieces of test data cyclically

Declare @ I IntSet @ I = 1While @ I < 1000001BeginInsert IntoTesttable ([Name],[Description],[Ordercolum])Values('Pagetest','Http: // www.3ymao.com',@ I * Rand())Set @ I = @ I + 1End

3. expose my system hardware and software (test environment)

Okay, the preparation is complete. Start to enter the topic (For convenience, the followingCodeI will not write the stored procedure for testing and demonstration.)~~ Too many! The most common not in

1) not in
 Declare   @ Timediff   Datetime  Declare   @ Pageindex   Int  Declare   @ Pagesize   Int  Declare   @ SQL   Varchar ( 500  )  Set   @ Pageindex  =  1  Set   @ Pagesize  =  10  Set   @ Timediff  =  Getdate  () Set   @ SQL  =  '  Select top (  '  +  Cast ( @ Pagesize   As   Varchar ) +  '  ) * From testtable where (id not in (select top  '  +  Cast ( @ Pagesize  * ( @ Pageindex  -  1 ) As   Varchar ) +  ' ID from testtable order by ID) Order by ID  '  Exec ( @ SQL  )  Select   Datediff (MS, @ Timediff , Getdate ())

@ Pageindex = 1, run: 0 ms (Awesome!)

@ Pageindex = 50000, run: 346 ms (why 50000 pages won't work)

@ Pageindex = 100000, run: 326 ms (why is it less than 50000 page ?)

2) max ()
 Declare   @ Timediff   Datetime  Declare   @ Pageindex   Int  Declare  @ Pagesize   Int  Declare   @ SQL   Varchar ( 500  )  Set   @ Timediff  =  Getdate  ()  Set   @ Pageindex  =  1  Set   @ Pagesize  =  10  Set   @ SQL  =  '  Select top (  '  +  Cast ( @ Pagesize   As  Varchar ) +  '  ) * From testtable where (ID> = (select max (ID) from (select top  '  +  Cast (( @ Pagesize  * ( @ Pageindex  -  1 ) +  1 ) As   Varchar ) +  '  ID from testtable order by ID) as a) Order by ID  '  Exec ( @ SQL  )  Select   Datediff (MS, @ Timediff , Getdate ())

@ Pageindex = 1, run: 0 ms (It's awesome.!)

@ Pageindex = 50000, run: 123 Ms (good)

@ Pageindex = 100000, run: 220 ms (the page number is proportional to the query time)

3) row_number ()
 Declare   @ Timediff   Datetime  Declare   @ Pageindex   Int  Declare   @ Pagesize   Int  Declare   @ SQL   Varchar ( 500  )  Set   @ Timediff =  Getdate  ()  Set   @ Pageindex  =  1  Set   @ Pagesize  =  10  Set   @ SQL  =  '  Select * from (select *, row_number () over (order by id asc) as rowindex from testtable) as idwithrownumber where rowindex  '  +  Cast ((( @ Pageindex  -  1 ) *  @ Pagesize ) +  1  As   Varchar ) +  '  And  '  +  Cast ( @ Pageindex  *  @ Pagesize   As   Varchar ) +  ''  Exec ( @ SQL  )  Select   Datediff (MS, @ Timediff , Getdate ())

@ Pageindex = 1, run: 0 ms (Okay ...... When there is a small amount of data, this is the urine)

@ Pageindex = 50000, run: 280 ms (slightly inferior)

@ Pageindex = 100000, run: 580 ms (the number of pages is proportional to the query time! !)

4) temporary table
 Declare   @ Timediff   Datetime  Declare   @ Pageindex   Int  Declare   @ Pagesize   Int  Declare   @ SQL   Varchar ( 500  )  Declare   @ Str   Varchar ( 500  )  Set   @ Timediff  =  Getdate  ()  Set  @ Pageindex  =  1  Set   @ Pagesize  =  10  Set   @ Str  =  '  With temptable as (select ceiling (row_number () over (order by id asc ))/  '  +  Cast ( @ Pagesize   As   Varchar ) +  '  ) As page_num, * From testtable)  '  Set   @ SQL  =  @ Str  +  ' Select * From temptable where page_num =  '  +  Cast ( @ Pageindex  -  1   As   Varchar ) +  ''  Exec ( @ SQL  )  Select   Datediff (MS, @ Timediff , Getdate ())

@ Pageindex = 1, run: 280 ms (No, right! This is not mainstream)

@ Pageindex = 50000, run: 280 ms (not scientific ......)

@ Pageindex = 100000, run: 280 ms (well, this product is not affected by the number of pages and will always be at this speed)

5) Intermediate Variables
 Declare   @ Timediff   Datetime  Declare   @ Pageindex   Int  Declare   @ Pagesize   Int  Declare   @ Count   Int  Declare   @ ID   Int  Declare   @ SQL   Varchar ( 500  )  Set   @ Pageindex  =  1  Set   @ Pagesize  =  10 Select   @ ID  =  0 , @ Count  =  0 , @ Timediff  =  Getdate  ()  Select   @ Count  =  @ Count  +  1 , @ ID  =  Case   When   @ Count  = ( @ Pageindex  -  1 ) *  @ Pagesize   Then IDElse   @ ID   End   From Testtable Order   By  ID  Set   @ SQL  =  '  Select top  '  +  Cast ( @ Pagesize   As   Varchar ) +  '  * From testtable where ID>  '  +  Cast ( @ ID   As   Varchar ) + ''  Exec ( @ SQL  )  Select   Datediff (MS, @ Timediff , Getdate ())

@ Pageindex = 1, run: 360 ms (brother, no, it's just the first page.)

@ Pageindex = 50000, run: 360 ms (I guess the speed of 100000 pages ......)

@ Pageindex = 100000, run: 360 ms (well, it is not affected by the number of pages)

 

From the above data, I will briefly analyze and summarize:

Not in: when the data volume is small, the speed is good, but the speed is a little inferior when the data volume is large, but fortunately as the number of query pages increases, the speed will not change much.

MAX: when the data volume is small, it is the fastest, but unfortunately, the larger the page number, the slower the speed.

Row_number (): similar to Max, but not faster than Max

Temporary table: it does not change the query speed because of the number of pages to be queried. It is only related to the size of the data. I personally think it is suitable for large data volumes and may be used when a large number of pages are queried.

Intermediate variable: similar to a temporary table, but inferior to a temporary table

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.