Analysis: Oracle jdbc paging Efficiency Test

After studying Oracle for a long time, I will now share with you the test on the paging efficiency of Oracle jdbc. I hope you will surely have a lot of gains after reading this article.

First, I wrote a simple jdbc paging test. In the case of 1 million records, I performed the absolute location operation on the database. It takes about 5 seconds to retrieve one page record from 0.2 million, but add it to the above. For example, the memory overflows from 0.4 million. This shows that oracle's jdbc does need to read data row by row when using absolute. In addition, we found that setting fetchsize will increase the speed by about 10%.

Then we use oracle's rownum Method for paging testing.

 
 

  
  
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM demo_table) A WHERE ROWNUM <= 200010) WHERE RN >= 200000  
 
 

The execution speed is indeed faster, which is about 2-3 seconds. However, this method should be slower to the backend, and it will start from the 0.9 million position, which is about 13 seconds. In this way, the modification implementation method seems to be necessary, But the strange thing is that there is no memory overflow problem when using ibatis paging, 1 million records to the last page, it may be more than 10 seconds.

I checked the queryForList method of ibatis and debug it. I found that he has a judgment, for the forward_only type of resultset, we use the loop next to locate it. Remember that we did this for compatibility in our paging implementation), instead of the absolute method I mistakenly thought.

It seems that the next method for the resultset of forward_only in oracle jdbc does not produce real read operations. I tested it with jdbc. There is no memory overflow problem from 0.9 million records. The execution time is about 22 seconds. I don't know if this is the same for mysql and db2. I will try another test next time. As for the gap between next and rownum execution efficiency, I guess the former must be located in the order of cursors.

The problem now is that the execution time of the two is only one time different, or an order of magnitude. To make the modifications, the SQL statement cannot be performed across databases, it seems that it is not worth it at all. Even if the user does not directly flip to the last few pages, the speed difference will not be felt at all. Later, I thought that foreigners sometimes have a bad mind. What kind of projects will require you to go over millions of records. Keep it as it is. Where can I leave it?

Test environment: oracle9i 2, Spanish character set. Other parameters are not tested.

The preceding is a simple example of the Oracle jdbc paging efficiency test. To learn more about the solution, please pay attention to the related posts on 51cto.com.

1. Code required to modify the Oracle storage process
2. Summary of Oracle stored procedures
3. Code for implementing the Oracle storage process
4. In-depth high-performance dynamic Oracle SQL Development
5. Analysis of Oracle SQL optimization rules