Back to your question, the solution to the large amount of data query there are the following two:
(1), all the data first query to memory, and then paging in memory, this way of memory occupation is large, you must limit the amount of data in a query.
(2), the use of stored procedures in the database paging, this way of relying on the database, different database implementation mechanism does not work, and query efficiency is not ideal. Both of these ways are not friendly enough for users.
2. Solution idea
By adding a self-growing field for querying on the database table to be queried, and then using this field for paging query can be a good solution to this problem. The following is an example of this paged query scheme.
(1), in the form to be queried to increase a long type of self-growing columns, named "queryId", mssql, sybase directly support self-growing fields, oracle can use sequence and trigger to achieve. Then add an index on the column.
The statement to add the queryId column is as follows:
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID numeric(19) identity
Oracle:
CREATE SEQUENCE queryId_S
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999999999999 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), in the query of the first page, first according to the reverse order of the size of the order to find out all the queryId,
statement as follows: select queryId from test_table where + query condition +order by queryId desc .
Because it is only querying the queryId field, even if the table has a large amount of data, the query will get results very quickly. The resulting queryId is then saved in an array on the application server.
(3), the user in the client page operation, the client will be querying the page number as a parameter to the application server, the server through the page number and queryId array to calculate the query to be queried queryId maximum and minimum values, and then query.
The algorithm to figure out the maximum and minimum values of queryId is as follows, where page is the page number to be queried, pageSize is the size of each page, and queryIds is the array of queryId generated in the second step:
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 criteria + "(queryId <= " + startId + " and queryId >= " + endId + ")";
3. Evaluation of the effect
The paging query method is applicable to all databases, with lower cpu and memory consumption for application servers, database servers, and querying clients and faster querying, which makes it a more ideal paging It is a more ideal paging query implementation scheme. After testing, querying 4 million pieces of data, the first page of data can be displayed within 3 minutes, and each subsequent page operation is basically within 2 seconds. There is no significant increase in memory and cpu usage.
The above is only a paging query to see the results of the problem, you need to write to Excel, you also need to consider Excel write code execution efficiency, this part is worth studying.