Detailed description of mysql, mssql, and oracle paging query methods, mssqloracle

Detailed description of mysql, mssql, and oracle paging query methods, mssqloracle

This article describes mysql, mssql, and oracle paging query methods. Share it with you for your reference. The specific analysis is as follows:

Paging query is the most common technology in web development. Recently, I have learned a little about it through querying materials.

I. querying by page in mysql

Note:

m=(pageNum-1)*pageSize;n= pageSize;

PageNum is the page number to be queried, and pageSize is the data volume of each query,

Method 1:

select * from table order by id limit m, n;

This statement means to query m + n records, remove the first m records, and return the last n records. Undoubtedly, this query can implement the paging function. However, if the value of m is larger, the query performance will be lower (the more pages that follow the query, the lower the query performance ), because MySQL also needs to scan m + n records.

Method 2:

select * from table where id > #max_id# order by id limit n;

This query returns n records each time, but does not need to scan m records like method 1. In the case of large data volumes, the performance can be significantly better than method 1, however, this paging query must obtain the maximum id (or minimum id) of the previous query (Previous Page) at each query ). The problem with this query is that sometimes we cannot get the maximum id (or minimum id) of the last query (Previous Page). For example, if the current query is on page 3rd, We need to query the data on page 5th, this query method is useless.

Method 3:

To avoid queries that cannot be implemented in method 2, you also need to use the limit m and n clauses. For performance, you need to minimize the value of m, for example, if the current page is 3rd, You Need To Query 10 pieces of data per page. The maximum id of the current page 5th is # max_id #:

select * from table where id > #max_id# order by id limit 20,10;

In fact, this query method partially solves the problem of method 2. However, if you need to query 2nd or 100th pages on 1000 pages, the performance will still be poor.

Method 4:
Copy codeThe Code is 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;
This query is the same as method 1, and the m value may be very large. However, because the internal subquery only scans the field id, rather than the entire table, the performance is better than that of the method 1 query, in addition, this query can solve problems that cannot be solved by method 2 and method 3.

Method 5:
Copy codeThe Code is as follows: select * from table where id> (select id from table order by id limit m, 1) limit n;

This query method is the same as Method 4. It also scans the field id through the subquery, and the effect is the same as Method 4. As for the performance, the performance of Method 5 is slightly better than method 4, because Method 5 does not need to be associated with tables, but is a simple comparison.

Ii. SQL Server paging Query

Method 1:

Applicable to SQL Server 2000/2005

Select top page size * FROM table1 WHERE id not in (select top page size * (page-1) id FROM table1 order by id) order by id

Method 2:

Applicable 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) A) order by id

-- Writing in descending order:

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) A) order by id Desc

Method 3:

Applicable to SQL Server 2005

Select top page size * FROM (SELECT ROW_NUMBER () OVER (order by id) AS RowNumber, * FROM table1) a where RowNumber> page size * (page number-1)

Note: page size: the number of lines per page; page number: page number. During use, replace "page size" and "page size * (page size-1)" with numbers.

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.
We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays a comparison: My conclusion is:

Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement
Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.
Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

Iii. oracle paging Query

Method 1:

SELECT * FROM ( SELECT A.*, ROWNUM RN FROM   (SELECT * FROM tab) A    WHERE ROWNUM <= 40 )      WHERE RN >= 21;

This page is less efficient than the following execution time. Oracle will be automatically optimized when the data volume is large!

Method 2:

select * from (select c.*,rownum rn from tab c) where rn between 21 and 40

In most cases, the efficiency of the first query is much higher than that of the second query.

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

For the first query statement, the SQL query condition WHERE ROWNUM <= 40 can be pushed to the inner query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.

In the second query statement, because the query conditions BETWEEN 21 AND 40 exist on the third layer of the query, Oracle cannot push the query conditions on the third layer to the innermost layer.

(It makes no sense to push to the innermost layer, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.

The query analyzed above is not only a simple query for a single table, but also effective for complex multi-table joint queries or sorting in the innermost query.

I hope this article will help you design your database program.