Improving performance in Pagination

[Vimal Kumar Venugopal]

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]

you can cache you data

[Martin Vashko]

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.