Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Improving performance in Pagination

 
Vimal Kumar Venugopal
Ranch Hand
Posts: 43
Eclipse IDE Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Ron.
 
Lawrence Xu
Greenhorn
Posts: 11
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can cache you data
 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic