This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Improving performance in Pagination

 
Vimal Kumar Venugopal
Ranch Hand
Posts: 43
Eclipse IDE Java Oracle
  • 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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can cache you data
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic