Big Data paging Solution

1. Writing Purpose
The paging solution is proposed to solve the performance problem when the system needs to retrieve the big data list.
2 terms, definitions, and abbreviations

3. Performance Analysis of Large Data Retrieval
There are problems with the performance of large data retrieval, mainly including

3.1 The large amount of data on the IE or CS client may lead to slow IE or even deadlocks.
Symptom:
IE browser crash
Browser whiteboard, stop responding

3.2 client-data transmission between Web servers results in slow client speed and low efficiency
Symptom:
White board when IE browser is waiting for a long time
Slow IE browser operation

3.3 constructing a big data list on the middle layer will result in lower performance of the middle layer
Symptom:
The CPU usage of the application server and database server is too large when there are many concurrent accesses.
Reducing system performance

3.4 selecting a large amount of data at a time in a database SQL statement will result in reduced database performance
3.4.1 performance will be affected if too much data is returned for one query
3.4.2 complex search conditions may also affect performance
Symptom:
When there are many concurrent accesses, the database server loads a large amount of CPU resources.
Communication between database servers and application servers may cause bottlenecks

4. Solution demonstration
4.1 traditional solution candidate solution-Web Server Page
Implement paging on the Web server, and select the specified data to return to the client based on the number of pages selected by the user.
Advantage: simple development
Advantage: it solves the problem of data transmission between the Web Client and the client.
Disadvantage: the performance of the intermediate layer and database cannot be solved.

4.2 optimization solution (1)-database Paging
Based on the number of pages selected by the user, this data is selected from the database each time. For example, if you select the second page, only 100 to 200th data entries are queried.
 
1) design analysis.
Advantage: all performance problems except 3.4.2 can be solved.
Disadvantage: You must use rownum to construct a subquery to select specified data for each query.
Disadvantage: Both Oracle and sqlserver2005 support, but earlier SQL Server versions only support top
Disadvantage: large data volumes cannot solve the performance problem of 1.4.2 if the search condition is low.
Disadvantage: development is a little complicated, but it can be solved through encapsulation.
4.3 optimization solution (2)-What you see is what you get in the virtual mode database page ()
The first query returns the list data on the first page, and then asynchronously obtains the serial_id of a certain order of magnitude to the client, when selecting the page number, the customer passes the serial_id of this page to the server to construct a query with ID = 001 or ID = 002 or ID = 003, and returns the list data ..
 
1. design issues.
Advantage: it can solve all the performance problems of large data volumes.
In this way, we mainly solve the performance problem of complex search conditions. The main scenario is that when the retrieval performance is low, the first page is slow, and then each page is fast (because it is retrieved through the primary key)
Advantage: no database features are used, and any database supports
Disadvantage: complicated development, but can be solved through Encapsulation

4.4 Other methods to improve performance

A) most users only view the first page of data. For the first time, select only the first pagesize data. Try the paging scheme again when you click each page number.
B) the BS end of the system does not have an excellent list control, and the display column customization has not yet been implemented. In this way, you can select only the data of the specified column on the DB end.
C) You can also use the scroll bar list in the virtual mode to browse data without paging mode. The actual display effect is better than paging mode.

Reference: (1)