MySQL, MSSQL and Oracle Paging Query method detailed _ database other

This article describes the MySQL, MSSQL, and Oracle paging query methods. Share to everyone for your reference. The specific analysis is as follows:

Paging queries are one of the most common technologies in web development, and have a bit of experience with them recently.

A, MySQL in the paging query

Note:

M= (pageNum-1) *pagesize;n= pageSize;

Pagenum is the page number to query, pagesize is the amount of data per query,

Method One:

SELECT * FROM table order by ID limit m, n;

The meaning of the statement, query M+n records, remove the first m, return the following N records. There is no doubt that this query can implement paging functionality, but if the value of M is greater, the query will have a lower performance (the lower the number of pages, the less query performance), because MySQL also needs to scan the M+n record.

Method Two:

SELECT * FROM table where ID > #max_id # ORDER by ID limit n;

The query returns an n record at a time, but it doesn't need to be like the way 1 scans the M record, in the case of large data paging, performance can be significantly better than mode 1, but the paging query must get a maximum ID (or the minimum ID) of the previous query (previous page) for each query. The problem with this query is that we sometimes have no way to get the maximum ID (or the minimum ID) of the last query (previous page), such as the current on page 3rd, you need to query page 5th of the data, the query method is helpless.

Method Three:

In order to avoid a query that can not be implemented by means of two, it is also necessary to use the limit m, n clauses, in order to performance, you need to do a small effort to the value of M, such as currently on page 3rd, need to query page 5th, 10 data per page, the current 3rd page maximum ID is #max_id#:

SELECT * FROM table where ID > #max_id # ORDER by ID limit 20, 10;

In fact, this query method is partially solved the problem of mode two, but if currently on page 2nd, need to query page 100th or 1000 pages, performance will still be poor.

Method Four:

Copy Code code as follows:

SELECT * FROM table as a INNER join (SELECT ID from table order by ID limit m, n) as B on a.id = b.id ORDER by a.id;

The query is the same way as the value of M may be large, but because the internal subquery only scanned the field ID, rather than the entire table, so performance is better than the way a query, and the query can solve the two ways and the way three unresolved problems.

Mode five:

Copy Code code as follows:

SELECT * FROM table where ID > (select id from table order by ID limit m, 1) limit n;

The query is the same way four, the same through the subquery scan the field ID, the effect is the same way four. As for performance, the performance of mode five will be slightly better than mode four, because mode 5 does not need to be associated with a table, but rather a simple comparison.

Second, SQL Server paging query

Method One:

Applies to SQL Server 2000/2005

Select Top Page Size * from
 table1
 WHERE ID
      . (select top Page Size * (page-1) ID from table1 order by ID
      )
 ORDER BY ID

Method Two:

Applies to SQL Server 2000/2005

--Sequential writing:

 Select Top Page Size *
 from table1
 WHERE ID >=
 (
 Select ISNULL (MAX (ID), 0) 
 from 
 (
 SELECT Top Page Size * (page-1) +1 ID from table1 ORDER by ID) an ORDER by
 ID

--Descending style:

 Select Top Page Size *
 from table1
 WHERE ID <=
 (
 Select ISNULL (MIN (ID), 0) 
 from 
 (
 select top Page Size * (page-1) +1 ID from table1 order by ID Desc) an ORDER by
 ID Desc

Method Three:

Applies to SQL Server 2005

 Select Top Page Size * 
 from 
     (
     select Row_number () over (order by ID) as rownumber,* from table1
     ) A
 whe RE rownumber > Page Size * (pages-1)

Description, Page size: Number of rows per page; pages: page. When used, replace the page size and page size * (pages-1) with numbers.

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with a scenario, but it's inefficient.
When optimization is recommended, the query efficiency is increased by adding primary keys and indexes.

Display comparisons through SQL Query Analyzer: My conclusion is:

Paging Scheme two: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements
Paging Scheme one: (Use not in and select top paging) efficiency second, need to stitch SQL statements
Paging Scenario Three: (using SQL Cursor stored procedure paging) is the least efficient, but most versatile

Third, Oracle paged query

Method One:

SELECT * 
from (select A.*, rownum rn to 
  (SELECT * from tab) A 
   where rownum <=) 
     where RN >= 21 ;

This paging has less execution time and is more efficient than the following. Oracle automatically optimizes when the volume of data is large.

Method Two:

SELECT * FROM 
(select C.*,rownum rn from tab c) where RN between and 40

In contrast to both of these, the first query is much more efficient than the second one in most cases.

This is because in the CBO optimization mode, Oracle can push the outer query criteria into the inner query to improve the efficiency of the inner query execution.

For the first query, the second-level query condition where rownum <= 40 can be pushed into an inner query by Oracle, so that once the results of the Oracle query exceed the rownum limit, the query terminates the result.

The second query, because the query conditions between and 40 are present in the third layer of the query, and Oracle cannot push the third-tier query criteria to the inner layer

(even pushing to the inner layer is meaningless because the inner query doesn't know what the RN represents). Therefore, for the second query statement, the Oracle is returned to the middle tier with all the data that satisfies the condition, and the middle tier returns to the outermost and all data. The filtering of data is done at the outermost layer, which is obviously much less efficient than the first query.

The query that is analyzed above is not just a simple query for a single table, but is as effective for the most inner query as a complex multiple-table union query or for the most inner-level query that contains the sort.

I hope this article will help you with the database program design.