Efficient Paging for Oracle database queries

ArticleDirectory

• Comparative analysis:
• Practical Application

Due to the influence of web page rendering speedProgramThe data in a grid containing thousands or tens of thousands of rows is basically invisible on the webpage. Therefore, it is generally displayed in the form of pagination (or it may be loaded using the visual srolling method, enterprise application systems are not very common), Asp. NET data control generally has the paging function. After 3.5, it also provides a separate paging control, and also uses the aspnetpager, a third-party component.

The paging control is very convenient. The previous processing method was to take out all the data and then process it by the Control. When the data volume is small, it should be said that the control does not feel good or bad, however, since all the data is retrieved from the database every time, it will certainly increase the pressure on the database. When the database is transferred, the network bandwidth will also be under pressure. It is very likely that 10 thousand pieces of data will be found. At last, only 50 pieces of data will be displayed, and 10 thousand pieces of data will be re-checked during page turning, and another 50 pieces of data will be displayed.

The following paging SQL statements are common. SQL server also has a version that uses the top keyword. Remember how to use rownum between minvalue and maxvalue when I first learned oracle. Like the original principle of doubt, rownum is generated during the query process. Therefore, the following SQL statements are used to find 5300 rows, then the first 5000 rows are discarded, and the last 300 rows are returned. Of course, this has taken a big step, and the data returned by the database is smaller, but when the number of pages to be queried is large, there is still a waste of queries.

Select * from (select a. *, rownum as rnum from (select * From yz_bingrenyz) A where rownum <= 5300) Where rnum> = 5000
 

LINQ provides skip and take APIs for paging. Because Entity Framework is used, you can use efprofiler to view the generated SQL statements with curiosity. This mainly refers to the use of analysis functions such as row_numer () over (), where you can directly find the starting point of the 5,000th rows, and then retrieve 30 rows.

 
Select * from (select T. *, row_number () over (order by null) as "row_number" from yz_bingrenyz t) P where p. "row_number"> 5000) Q where rownum <= 300
 

Comparative analysis:

It takes 1.3 s for the local machine to test the former, and 0.25 s for the latter. The difference can also be seen from the following execution plans.

Practical Application

If you want to write such SQL statements for each query, it is certainly troublesome. You can use stored procedures for encapsulation. However, to dynamically Execute SQL statements, the efficiency must be reduced. Therefore, in ASP. it is better to use C # To encapsulate functions in. net, and use ADO for those that do not use the object framework..