Paging processing is a problem that is often encountered in web development. For different paging methods, a great performance difference is generated in the case of high load, in general, the processing method of paging is divided into two, one is The program paging, that is, after removing the recordset in the database, use the loop intercepted manner to obtain the required data, one is to process the SQL skills provided by the database, the latter provides good loads. Performance, especially in the case of more data volume, the following brief introduces the implementation of these two ways to use a sample table Create Table Student (ID INT NULL Primary Key, Name Varchar (20) Not null, Code Varchar (20) DESCRIPTION VARCHAR (100))
This can be implemented in different databases. It is best to modify VARCHAR for VARCHAR2 in Oracle. We take the data on page 10, 10 lines per page, and take 91-100 rows. Example, SQL = "Select * from student" set = conn.execute (sql); i = 0; do unsteil => 100 i = i 1 if i> 90Then Response.write RS .... End if rs.movenext loop This is the problem that all data is taken from the database, then traversing, so that it will create very large data interactions between database and application servers when the pressure is large, especially When data is low, the efficiency is low. The processing advantage of this method is to give full play to the advantages of the database. Only the required data is taken out, and the processing method of different databases is introduced 1) Oracle In Oracle, we use ROWNUM characteristics. Handling such problems, such as Select * from (SELECT ROWNUM RID, A. * From stay a) Where rid betWeen 91 and 100 if it is a complex SQL, we only need to add the ROWNUM ID after SQL, then in outer SQL It is possible to use a similar structure. It is important to note that do not repeat the field name in the target SQL. If you want to use it, you can use a cool field.
In general, you can remove the target SQL out Select, this character is set to Area_SQL, then Select * from (SELECT ROWNUM RID, Area_SQL) WHERE RID BETWEEN 91 and 100
There is another way to handle this problem, using minus, as follows Select * from student where rownum <101 minux select * from student Where RownUM <91 Using Minus's over-energy, this benefit is easier to modular, more common , But less efficient
This calls SET RS = Conn.execute (SQL) in ASP; Do UnTil Rs.eof Reponse.Write Rs ... rs.Movenext Loop
2) SQL Server handles this problem in SQL Server, because no pseudo columns are provided in SQL Server, there is no filtering function, so we want to achieve the same effects of Oracle, must write storage procedures to solve, and there is no versatility Need to write a stored procedure for each table, the stored procedure is not introduced here, the method that can generate a certain effect is that the SELECT TOP 100 * from stay is then handled by the program paging method, so efficiency is taken behind the data efficiency. It is also very low, it can reflect advantage when taken in front of data 3) MySQL MySQL is a development source database with fast speed, but lacks a lot of necessary functions, but in small web pages already use its paging system processing The problem is the following select * from student where ..... LIMIT 90, 10 LIMIT function is processed, the first parameter is the starting line, the second parameter is the number of rows read, so it takes 91-100 Data, Mysql's processing is very flexible, and it is easy to modular, as long as the starting line can be modified, in PHP as follows
$ r = mysql_query (SQL); while ($ line = mysql_fetch_array ($ r)) {echo $ line [...];
