Paging query of mainstream Databases

Paging query of mainstream Databases
1. background summary as a project needs to display a large amount of data on the page, all the data is first queried during database query and then displayed, at this time, paging query operations are essential. This article describes the use of paging query for Mysql, Oracle, and SQL Server databases. 2 It is expected that the number of readers can communicate with the internal staff of a large number of computer enthusiasts. 3. The paging query refers to displaying excessive results on a limited page by multiple pages. Generally, the paging query is divided into two categories: logical and physical pages. Logical paging is the first time a user accesses the database, all the records of the database are queried, added to a large collection, and then stored in the session object, the page number is used to calculate the data content to be displayed on the current page, store it in a small list set, and store it in the request object. The page is displayed on the JSP page and displayed through traversal. When a user accesses the session for the second time, as long as the browser is not closed, data will be obtained from the session for display. This method is called logical paging because it displays computing pages in the memory session object instead of truly paging our database. Disadvantage: if the amount of data to be queried is too large, the session will consume a large amount of memory, because it is used to obtain data in the session. If the browser access is disabled for the second or more times, the session is accessed directly, so the data cannot be updated. Advantage: the unified code processing method makes it easier to migrate data across databases. Physical paging: uses the paging mechanism of the database itself, such as rownum of the Oracle database or limit in the Mysql database to perform paging operations. Because it is a paging condition query for database data, it is called a physical paging. Each physical page is connected to the database. Advantage: the data can be up-to-date. Because a small amount of data is queried Based on paging conditions, it does not occupy too much memory. CTE (Common Table Expression, Common Table Expression) this Expression is derived from a simple query, it can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, or create view statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query. 4. The solution is to query the database through physical paging. 5. First, create a project on the development platform and use the System Log Module in the new project as an example to perform the paging query function of mysql and oracle. 5.1 Mysql performs operations in the database: the paging query of mysql is the simplest. You can use the limit keyword to perform queries. The query statement is in the general form: select o. * from (SQL) o limit firstIndex, in pageSize, the SQL statement can be the result of a single table query or the result of a multi-Table query. firstIndex indicates the starting position of the display result (mysql uses 0 as the starting position) pageSize directly queries the table based on the number of displayed records. We can see that the query time is 0.005s and the paging query is used. 15 pieces of data are displayed on one page. The query time is 0.001 s, which is embodied in the project: first, find SystemLogQueryImpl in the project. one thing to note about the java class is that the return value of the method must be PageList rather than List. In the development platform, this class is the log function server. Implementation class, where ec_p is the number of pages, ec_rd is the number of display records staratNum is the start index, endNum is the end index because MySQL's start index starts from 0, the calculation method of the number of records to be displayed is: End index-start index + 1 displayed on the page in sqlMap: 5.2 There are two ways to query OracleOracle: ROWNUM and row_number () 5.2.1 ROWNUM in the database: the general form of the query statement: select * from (select o. *, ROWNUM num from (SQL) o where ROWNUM <= (endIndex) where num> = firstIndex queries the table directly. The query time is 16 msecs, time consumption 7 msecs reflected in the project: Find the corresponding service implementation class: service implementation class content: startNum is the starting index, endNum is the ending index because The two parameters used in the ROWNUM method are the starting index and the ending index. Therefore, you can directly use them. In SQL: the effect on the page: 5.2.2 row_number () query statement common form: select * from (select t. *, row_number () over (order by orderColumn) as rownumber from (SQL) t) p where p. rownumber> firstIndex) where rownum <= pageSize: queries the table directly. It takes 22 minutes for msecs to query the table using a paging query statement. the time consumed is 12 msece in the project: the content of the service implementation class is the same as that of ROWNUM. The difference is that because the order by sorting function is added to SQL, the query speed slows down, so this method is not used in the development platform. For oracle paging queries, the main difference is that when rownum is used for sorting, a pseudo-column rownum is added to the result set before sorting. The row_number () function () after the sorting clause is included, the row number is sorted first and then calculated. 5.3 due to the large number of Sqlserver versions, the paging query method is also different. 5.3.1 Use row_number () in SQL Server 2005/2008 to query the common form: SELECT * FROM (SELECT *, ROW_NUMBER () OVER (order by orderColumn) AS RowNumber FROM tableName) EmployeePage WHERE RowNumber> = startIndex AND RowNumber <= endIndex order by orderColumnGO directly queries the table, using paging query, 5.3.2 SQL 2005/2008 use the CTE method to implement the general Query Form: WITH EmployeePage AS (SELECT *, ROW_NUMBER () OVER (ORDER BY orderColumn) AS RowNumber FROM tableName) SELECT * FROM EmployeePage WHERE RowNumber> = firstIndex AND RowNumber <= endIndex order by orderColumnGO use paging query. 5.3.3 SQL SERVER 2012 supports the common form of OFFSET query, as shown below: SELECT * FROM tableNameORDER BY orderColumnOFFSET (page-1) rows fetch next size ROWS ONLYPage indicates the number of pages to be displayed. Because I do not have a database with SQL Server 2012, performance tests cannot be performed for the moment