This article aims to introduce a method for querying large data tables in the database by page. This method has low CPU and memory usage on the application server, database server, and query client, the query speed is fast, which is an ideal paging query implementation solution.
1. Question proposal
In software development, querying a large amount of data is a common problem. It is often used to query a large amount of data. There are two common solutions for querying large amounts of data: 1. query all the data in the memory first, and then perform paging in the memory. This method occupies a large amount of memory, the data size of a query must be limited. 2. The stored procedure is used for paging in the database. This method relies heavily on the database, and different database implementation mechanisms are not available, and the query efficiency is not ideal. The above two methods are not friendly to users.
2. Solution
By adding an auto-increment field for query to the database table to be queried, and then using this field for paging query, this problem can be well solved. The following is an example of this paging query solution.
1. Add a long-Type Auto-increment column to the table to be queried and name it "queryid". MSSQL and Sybase directly support auto-increment fields. Oracle can use sequence and trigger to implement this. Add an index to the column. The statement for adding the queryid column is as follows:
MSSQL: [queryid] [bigint] identity (1, 1)
SYBASE: queryid numeric (19) Identity www.yueluo.net
ORACLE:
Create sequence queryid_s
Increment by 1
Start with 1
Max value 999999999999999 minvalue 1
Cycle
Cache 20
Order;
Create or replace trigger queryid_t before insert
On "test_table"
For each row
Begin
Select queryid_s.nextval into: New. queryid from dual;
End;
2. When querying the first page, find all queryids in descending order of size. The statement is as follows: Select queryid from test_table where + query condition + order by queryid DESC. Because only the queryid field is queried, the query results will be quickly obtained even if the data volume in the table is large. Save the obtained queryid to an array on the application server. String 9
3. When you perform page flip operations on the client, the client passes the page number to be queried as a parameter to the application server. The server calculates the maximum and minimum values of the queyid to be queried through the page number and queyid array, then perform the query.
Calculates the maximum and minimum values of the queyid. Algorithm As follows, page is the page number to be queried, pagesize is the size of each page, and queryids is the queryid array generated in step 2:
Int startrow = (page-1) * pagesize;
Int endrow = page * pagesize-1;
If (endrow> = queryids. length)
{
Endrow = This. queryids. Length-1;
}
Long startid = queryids [startrow];
Long endid = queryids [endrow];
The query statement is as follows:
String SQL = "select * From test_table" + query condition + "(queryid <=" + startid + "and queryid> =" + endid + ")";
String 7
3. Performance Evaluation
This paging query method applies to all databases. It consumes less CPU and memory on the application server, database server, and query client, and the query speed is fast, it is an ideal paging query implementation solution. After testing, a query of 4 million pieces of data can display the home page data within 3 minutes. In the future, each page turning operation will be within 2 seconds. Memory and CPU usage did not increase significantly.
This article from: monthly Network (www.yueluo.net) detailed source reference: http://www.yueluo.net/Mysql/20070828/1551.
