Home » RDBMS Server » Performance Tuning » Pagination in big result sets (Oracle 10g)
Pagination in big result sets [message #351198] Mon, 29 September 2008 17:12 Go to next message
javier.rivas.arias
Messages: 1
Registered: September 2008
Junior Member
Hi,

I am using hibernate to do pagination and it works and performance very well with tables up to a million records.
However, when the tables are quite big (5 million records) and I query the last pages, performance is quite bad.

For example:


To query the second page, it takes 0,360 sec.

SQL: select * from ( select row_.*, rownum rownum_ from ( select domdatasou0_.id as id308_, domdatasou0_.version as version308_, domdatasou0_.name as name308_ from dom_datasource domdatasou0_ order by domdatasou0_.id desc ) row_ where rownum <= 34) where rownum_ > 17


And to query page number 235295, it takes 3,898 sec.

SQL: select * from ( select row_.*, rownum rownum_ from ( select domdatasou0_.id as id308_, domdatasou0_.version as version308_, domdatasou0_.name as name308_ from dom_datasource domdatasou0_ order by domdatasou0_.id desc ) row_ where rownum <= 4000016) where rownum_ > 3999999


Is there any other sql that I can use to speed things up?

Best Regards,
Javier Rivas
Re: Pagination in big result sets [message #351212 is a reply to message #351198] Mon, 29 September 2008 22:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't think there is anything you can do. In order to get the 1,000th page, you must find (but not fetch) the 999 pages that precede it. The higher number the page, the longer it will take.

If you were using a technology with a dedicated database connection (eg. SQL*Forms, TOAD) then Oracle can open a cursor and keep it open. You fetch new pages only as you scroll down by performing additional fetches against the cursor.

To my knowledge, this is not possible with a light-client architecture that uses connection pooling or establishes a new connection with each round-trip to the database.

Ross Leishman
Previous Topic: Why does t.rowid = t.rowid improves performance?
Next Topic: Need to improve query performance
Goto Forum:
  


Current Time: Sat Dec 03 08:00:33 CST 2016

Total time taken to generate the page: 0.04141 seconds