Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Improving performance in Pagination

Ranch Hand
Posts: 43
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
you can cache you data
Posts: 3837
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic