Big Data paging solution and data paging Solution

Source: Internet
Author: User

Big Data paging solution and 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)


What is the solution for paging in the case of large data volumes in ORACLE?

The query efficiency is actually a matter of data volume. If you have never thought about index and field optimization, consider splitting tables and partitions. If you use summarized data, you can summarize the queried data several times before performing the query.

Large data volume query and paging display problems

For a large amount of data paging, we can retrieve data as needed.
The total number of records in the row, Rscount,
Then, set the number of records per page, for example, 12.
Calculate the number of pages that can be divided into: Pagecount = Rscount/12

If the page is 5th, take 12 records on the 6th page. You can use variables for the page number, such as PageSize, to give different page values each time.
Select top 12 * from table where Id not in (select top 5*12 Id from Table order by id desc) order by Id desc

And so on.

The access for the database with 0.2 million records is too poor!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.