File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Improving performance in Pagination Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Improving performance in Pagination" Watch "Improving performance in Pagination" New topic

Improving performance in Pagination

Vimal Kumar Venugopal
Ranch Hand

Joined: Aug 20, 2011
Posts: 43

Howdy Rangers,

I implemented pagination with JDBC. The problem is due to the large number of records (in excess of 15000 records) returned. I use the following query:

select * from ( select a.* , ROWNUM rnum , count(*) over() as total_rows from ( /* your select statement goes here */ ) a ) b where b.rnum >= :start_row and b.rnum <= :end_row

I get a very sluggish screen and I guess the performance gets a hit because the inner most query returns all 15000 records and the outer queries filter them to 100.
Now is there any way to form a query which returns 100 rows in the first place itself?

Any help would be truly appreciated.


"Value has a Value only if its value is valued!"
Lawrence Xu

Joined: Feb 11, 2004
Posts: 11
you can cache you data
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

Ron, as your question is actually related to the database, I've moved the topic to a better suited forum.

The main problem seems to be the count(*) over() as total bit. To compute this number, Oracle needs to materialize all rows from the inner query and there is no easy way around this. Most elegant solution is to just drop the count(*) from your query. Display just the first page and buttons/links to advance to the few next pages to your users, and don't display the total number of results.

How others do this? You might notice that number of total hits displayed by Google, for example, is just an estimation. I've seen many times that Google has told me there are hundreds/thousands of hits at the first page, but the results actually ended at the second or third page. The number of hits was just an approximation which bears some inaccuracy. Similar approach might be feasible in your project, but that will be a bit complicated and might depend on the exact structure of your data and your needs.

If the query is still slow after you remove the count(*) expression, you might need to add an index. Feel free to ask again if you need help with that.
I agree. Here's the link:
subject: Improving performance in Pagination
jQuery in Action, 3rd edition