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