Comparison of multiple paging performance for SQL Server

I. Preface

　　Because of the working relationship, encountered a very large number of data paging problem, the total number of data is 80 million, this is obviously not a simple paging can be solved, need to consider from many aspects, from the sub-table, sub-library and so on. But this also let me consider the problem of paging performance, in the case of different data volume, different paging method efficiency will be different. I am here with a few more common methods of paging in different data volume, different page under the comparison, respectively: Top, Row_number () and offset Fetch. Here are only the simplest statements of their respective, as follows.

Top:

Create procTops@pageindex int,@pagesize int asBEGINSelect Top(@pagesize)*  fromCustomerswhereCustomerID not inch (Select Top((@pageindex - 1)* @pagesize) CustomerID fromCustomersOrder  byCustomerIDDESC)Order  byCustomerIDDESCEND

Row_number ():

Create procRowNumber@pageindex int,@pagesize int asBEGINSelect *  from (SelectRow_number () Over(Order  byCustomerIDdesc) asPx*  fromCustomers) asawhereA.pxbetween((@pageindex - 1)* @pagesize + 1) and(@pageindex*@pagesize)END

Offset Fetch:

Create procOffset_fetch@pageindex int,@pagesize int asBEGINSelect *  fromCustomersOrder  byCustomerIDdescOffset ((@pageindex - 1)* @pagesize) RowsFetch Next @pagesizeRows only  END

Three stored procedures all sort the data, set the statement relatively fair, here default 10 data per page. Top paging is basically all versions of SQL Server that can be used, row_number () is more than SQL 2005, and offset fetch requires SQL 2012 to support it.

Two. 20W Data volume

　1.Top

1th page 10 execution average time 29.1 milliseconds.

　　　　

10,000th page 10 execution average time 109.2 milliseconds.

　　　　

No. 20000 page 10 execution average time 126.8 milliseconds.

　　　　

　　2.row_number ()

　　　1th page 10 execution average time 20.2 milliseconds.

　　　　

10,000th page 10 execution average time 96.5 milliseconds.

　　　　

No. 20000 page 10 execution average time 153.8 milliseconds.

　　　　

　　3.Offset FECTH

　　　1th page 10 execution average time 19.3 milliseconds.

　　　　

10,000th page 10 execution average time 70 milliseconds.

　　　　

No. 20000 page 10 execution average time 75.1 milliseconds.

　　　　

Three. 200W Data volume

1.Top

1th page 10 execution average time 55.9 milliseconds.

100,000th page 10 execution average time ..... Milliseconds.

Executed for several 10 seconds .... Direct elimination

2.row_number ()

　　　1th page 10 execution average time 25.5 milliseconds

　　　　

100,000th page 10 execution average time 642.3 milliseconds

　　　　

No. 200000 page 10 execution average time 1257 milliseconds

　　　　

3.Offset FECTH

　　　　1th page 10 execution average time 24.7 milliseconds

　　　　

100,000th page 10 execution average time 220.5 milliseconds

　　　　

No. 200000 page 10 execution average time 396 milliseconds

　　　　

Three. 2000W Data volume

1.Top

　　　Because in the last round was eliminated, so this round will forget.

2.row_number ()

1th page 10 execution average time 57 milliseconds

100th W page 10 execution average time 6401.5 ms

　　　　

No. 200 W page 10 execution average time 14606.2 ms

　　　　

　　3.Offset FECTH

　　　1th page 10 execution average time 27.5 milliseconds

　　　　

100th W page 10 execution average time 1778.9 ms

　　　　

No. 200 W page 10 execution average time 3523.2 ms

　　　　

Four. Summarize

You can see that the larger the amount of data, the larger the paging page number, the greater the impact on paging efficiency. Top of the page method out early, it is obviously because the data in the not too large, so if there is a good top paging method can leave a message below, I will also test. Row_number page method is relatively good, and sql2005 and above the database can be used, the audience range is larger, and the offset fecth performance is more superior, but only sql2012 and above support.

Have any questions welcome the discussion!

　　　　

Comparison of multiple paging performance for SQL Server