| Pagination in big result sets [message #351198] |
Mon, 29 September 2008 17:12  |
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  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
