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

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