Summary of common data paging Technologies

Based on the summary of the two blog posts

Summary of commonly used data paging technology: http://www.cnblogs.com/refactor/archive/2012/06/28/2552700.html

Summary http://www.cnblogs.com/kingboy2008/archive/2011/06/22/2086710.html of several data pages collected on the Internet

Http://www.cnblogs.com/builderman/archive/2011/07/19/2110108.html

1. Data paging Overview

• Generally, when the data volume is large on a web page, all data cannot be displayed on a single page.

• In some specific scenarios, no data that meets the conditions needs to be returned

• From the data perspective, data within the specified range must be returned.

2. Data Layer paging technology

• When querying a database, only data on a specific page is queried.

• Mainly completed by T-SQL

• Suitable for large data tables

• Advantage: the returned result set is small and the query speed is fast

• Disadvantage: You need to query the database multiple times.

3. data layer paging technology-use temporary tables

• Create a temporary table before querying data

• The table has one more column ID than the result set to be returned.

• Sort the results by specific columns and insert the results to the temporary table

• At this time, you can return a specific page according to the ID column

View code

Create procedure paging2 @ pagenum int, @ num int as begin select addressline1, addressline2, city, postalcode, identity (INT) num into # temp from person. address order by addressid ASC select * from # temp where num <= @ num * @ pagenum and num> @ num * (@ pageNum-1) order by num ASC drop table # temp endexec paging2 20, 5; -- five per page, 20th page data time: 1 s exec paging0 20000,5; -- five per page, 20 thousand page data time: 1 s exec paging0 20th, 5; -- five pages per page, million pages data time: 1 s

 

4. data layer paging technology-using Table Variables

• Create a table variable before querying data

• The table has one more column ID than the result set to be returned.

• Sort the results by specific columns and insert them into Table Variables

• At this time, you can return a specific page according to the ID column

5. data layer paging technology-repeated top fetch

• To obtain 91st-100 rows of data in a positive order of a specific column

• First, the top 100 is obtained in positive order of a specific column.

• Then, top 10 instances are sorted in reverse order.

• Finally, sort the results and return them.

Create procedure fenye (@ pagenum int, @ num INT) as begin select * from (select top (@ num) * from (select top (@ num * @ pagenum) * From person. address order by addressid ASC) B order by B. addressid DESC) C order by C. the addressid ascend method first extracts the previous @ num * @ pagenum data in the database, and then extracts the last @ num data from the result set, of course, the two sorting rules are different, which is very important, otherwise the paging effect will not be achieved. You can give it a try. The disadvantage is that the more pages you get, the slower the query speed. For example, exec paging1; -- five pages per page, and the tenth page data consumed: 1 s exec paging1; -- five pages per page, 200th page data time: 1 s exec paging1 20,000th, 5; -- five pages per page, 20th page data time: 1 s exec paging1, 5; -- five pages per page, page data time: 3 S

　　

　　

6. data layer paging technology-use row number

• In SQL Server 2005/2008

• Add a row number () When querying the result set to indicate the row number (do not understand row number (): see: http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html)

• Use the above result set as a subquery to filter out specific pages through row_number ()

View code

The third way to improve is because the top keyword is actually the performance optimized, so it is better than the execution efficiency of row_number () because it is used twice, why don't we use the two in combination, and the effect is not better. Let's improve it. Create procedure paging0 @ pagenum int, @ num int as begin select * from (select top (@ num * @ pagenum) measurepipe, measuretime, measurecycle, measuredata, doseratevalue, row_number () over (order by gmpipe. measuretime ASC) as num from gmpipe) A where. num> @ num * (@ pageNum-1) order by. measuretime desc end go performance exec paging0 20, 5; -- five per page, 20th page data time: 1 s exec paging0 20000,5; -- five per page, 20 thousand page data time: 1 s exec paging0 20th, 5; -- five pages per page, million pages data time: 1 s

 

7. The Code is as follows:

View Code USE AdventureWorks2008GOSELECT COUNT(*) FROM Production.TransactionHistoryArchiveGOSELECT TOP 50 * FROM Production.TransactionHistoryArchiveORDER BY ReferenceOrderID ASCGO--Use Top*TopDECLARE @Start datetime,@end datetime;SET @Start=getdate();DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);SET @PageNumber=5000;SET @Count=10;SET @Sql='SELECT T2.* FROM (    SELECT TOP 10 T1.* FROM         (SELECT TOP ' + STR(@PageNumber*@Count) +' * FROM Production.TransactionHistoryArchive        ORDER BY ReferenceOrderID ASC) AS T1    ORDER BY ReferenceOrderID DESC) AS T2ORDER BY ReferenceOrderID ASC';EXEC (@sql);SET @end=getdate();PRINT Datediff(millisecond,@Start,@end);GO--USE table valueDECLARE @Start datetime,@end datetime;SET @Start=getdate();DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);SET @PageNumber=5000;SET @Count=10;DECLARE @local_variable table (RowNumber int identity(1,1),[TransactionID] [int],    [ProductID] [int],    [ReferenceOrderID] [int],    [ReferenceOrderLineID] [int],    [TransactionDate] [datetime],    [TransactionType] [nchar](1),    [Quantity] [int],    [ActualCost] [money],    [ModifiedDate] [datetime]);insert into @local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASCselect * from @local_variable where RowNumber > (@PageNumber-1)*@Count and RowNumber <= @PageNumber*@CountSET @end=getdate();PRINT Datediff(millisecond,@Start,@end);GO--USE temp tableDECLARE @Start datetime,@end datetime;SET @Start=getdate();DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);SET @PageNumber=5000;SET @Count=10;create table #local_variable(RowNumber int identity(1,1),[TransactionID] [int],    [ProductID] [int],    [ReferenceOrderID] [int],    [ReferenceOrderLineID] [int],    [TransactionDate] [datetime],    [TransactionType] [nchar](1),    [Quantity] [int],    [ActualCost] [money],    [ModifiedDate] [datetime]);insert into #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASCselect * from #local_variable where RowNumber > (@PageNumber-1)*@Count and RowNumber <= @PageNumber*@CountSET @end=getdate();PRINT Datediff(millisecond,@Start,@end);GO--Use ROW_NUMBERDECLARE @Start datetime,@end datetime;SET @Start=getdate();DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);SET @PageNumber=5000;SET @Count=10;SELECT * FROM (    SELECT ROW_NUMBER()        OVER(ORDER BY ReferenceOrderID) AS RowNumber,          *    FROM Production.TransactionHistoryArchive) AS TWHERE T.RowNumber<=@PageNumber*@Count AND T.RowNumber>(@PageNumber-1)*@Count;SET @end=getdate();PRINT Datediff(millisecond,@Start,@end);GO

　　

8. application-layer paging technology (custom paging method)

• Cache data at the application/logic layer and display data in Segments

• Mainly completed by program code

• Advantage: the number of queries to the database is small, and the returned results are fast each time.

• Disadvantage: The first query is slow and occupies application-layer memory resources.

9. application-layer paging technology-gridview

• Apply the pagination function of the gridview

• Enable the cache function of the xxxdatasource Control

10. application-layer paging technology-datapager

• New Controls in. Net 3.5

• More flexible paging format definition

11. application-layer paging technology-pageddatasource object Paging

• Objects in. net

• More flexible paging format definition, but the code volume is too large to control

12. application-layer paging technology-LINQ

• Custom Data Binding

• Query using LINQ

• Use the SKIP () and take () Functions

12. Display layer paging technology (third-party view controls combined by JS and jquery)

• Data paging on the client

• Mainly implemented through client scripts

• Advantages: reduces network transmission traffic and improves bandwidth utilization

• Disadvantages: a large number of client scripts are required to increase development and maintenance costs.

13. Display layer paging technology-updatepanel

• The updatepanel control in ASP. net ajax can be used to convert the paging function provided by the application layer to the client.

• No code maintenance required

• Make full use of server-side controls

14. Display layer paging technology-Data Services

• Use the URI-based data access provided by data services to achieve data Paging

• At the presentation layer, you can directly parse through JavaScript

• Lightweight Data Transmission Format: XML/JSON

Summary

Let's change the number of entries per page.

Temporary table method:

Exec paging2 5000,200; -- two hundred entries per page, 5,000th page data time: 7 S

Top statement method:

Exec paging1 5000,200; -- two hundred entries per page, 5,000th page data time: 3 S

Row_number () function method:

Exec paging0 5000,200; -- five pages per page, 20th million pages data time: 1 s

 

 

 

Additional SQL Paging

The paging of the SQL Server Stored Procedure has been discussed for several years. Many friends are asking me, so I would like to share my opinion on creating a table here:

Create Table [testtable] ([ID] [int] identity (1, 1) not null, [firstname] [nvarchar] (100) Collate chinese_prc_ci_as null, [lastname] [nvarchar] (100) Collate chinese_prc_ci_as null, [country] [nvarchar] (50) Collate returns NULL, [note] [nvarchar] (2000) Collate chinese_prc_ci_as null) on [primary] Go

 

Insert data: (20 thousand, more data will be tested obviously) set identity_insert testtable on

Declare @ I int set @ I = 1 while @ I <= 20000 begin insert into testtable ([ID], firstname, lastname, country, note) values (@ I, 'firstname _ XXX', 'lastname _ XXX', 'country _ XXX', 'note _ XXX') set @ I = @ I + 1 end

Set identity_insert testtable off

 

-------------------------------------

Paging solution 1: (using not in and select top pages) Statement Form:

Select top 10 * From testtable where (id not in

(Select top 20 ID from testtable order by ID) Order by ID

Format:

Select top page size * From testtable where (id not in

(Select top page size * Page ID-1 from Table order by ID) Order by ID

-------------------------------------

Paging solution 2: (using the ID greater than the number and select top paging) Statement Form:

Select top 10 * From testtable where (ID>

(Select max (ID) from (select top 20 ID from testtable order by ID) as t) Order by ID

Format:

Select top page size * From testtable where (ID> (select max (ID)

From (select top page size * Page ID-1 from Table order by ID) as t) Order by ID

-------------------------------------

Paging solution 3: (using SQL cursor Stored Procedure paging)

Create procedure xiaozhengge @ sqlstr nvarchar (4000), -- query string

@ Currentpage int, -- page n

@ Pagesize int -- number of lines per page

As

Set nocount on

Declare @ P1 int, -- P1 is the cursor ID

@ Rowcount int exec sp_cursoropen

@ P1 output, @ sqlstr,@ Scrolopt = 1,

@ Ccopt = 1,@ Rowcount = @ rowcount output select ceiling (1.0 * @ rowcount/@ pagesize) Total number of as pages --, @ rowcount as total number of rows, @ currentpage as current page set @ currentpage = (@ currentpage-1) * @ pagesize + 1 exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize exec sp_cursorclose @ P1 set nocount off

Other solutions:

If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low. We recommend that you add primary keys and indexes during optimization to improve query efficiency.

Use the SQL query analyzer to display the comparison:

My conclusion is:

Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement

Paging solution 1: (using not in and select top pages) The efficiency is second, and SQL statements need to be spliced.

Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.