Jsp paging Principle

Paging is a very important topic of web applications. The data in the database may be several thousand, tens of thousands, and millions of rows. However, we cannot display tens of thousands of rows of data on the browser at a time, so that the browser cannot stand it and users cannot stand it. Generally, 20 rows are displayed on each page, which is an ideal display status.

There are two main paging methods:

1. Retrieve all Qualified Data, place it in the dataset or memory, and browse the data page by page. Then, you may only need to browse 20 records per page, but you need to retrieve millions of records. I call this paging method "pointer paging ". The pointer paging method identifies a dataset by pointer (or subscript of the set. For example, if 20 pieces of data are to be displayed on the page, the pointer on the first page starts from 1, the pointer on the second page starts from (2-1) * 20 + 1, and so on. "Pointer paging" is suitable for application systems with low data volume and concurrency, and is not suitable for massive data queries.

2. It is obviously the best solution to query massive data. If a table contains 2 million records, the first 20 records will be taken on the first page, and the second will be taken from 21 ~ 40. Here we use
Select top current page * Number of records per page * from Table A where primary key field not in (select top (current page-1) * Number of records per page primary key field from Table)

This form of statement is implemented. The primary key is used for this query. We call it "primary key paging ".

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

Next we will compare the execution efficiency of the SQL statements used in these two methods.

Environment: Ben M1.8
Memory: 1 GB
Database: SQL server2000
Data volume: about 2 million

Statement 1:
Select getdate ()
Select * from f6_7xic
Select getdate ()

Records: 1969152 rows

(10 Average values)
CPU usage: 100%
Query time: 22 seconds

Statement 2:
Select getdate ()
Select top 20 * from f6_7xic where id not in (select top 30000 id from f6_7xic)
Select getdate ()
(10 Average values)
CPU usage: About 24%
Query time: 0.2 seconds
It can be seen that the efficiency of primary key paging in processing massive data is very high, and it is also the first choice.

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

For a complete page, it should include the total number of records, total number of pages, current number of pages, current page, number of records per page, forward, backward, jump, etc. Therefore, whether it is a pointer paging or a primary key paging, you have to pass a statement like select count (*) as the total number of records from the table name to obtain the number of records.

Apparently, primary key paging is the first choice, but its writing method is too complicated and too troublesome. Besides, for joint queries and multi-table queries, the writing of this SQL statement is more complex, we will spend more on our limited brain cells. Therefore, it is necessary to obtain a general program that does not need to write such complex SQL statements, but also to solve common queries and massive query paging. People are looking forward to the call of the times!