Hibernate/Display tag Pagination (Poor/slow performance for the last set of pages)/ Oracle 10 G

[Santosh Ramachandrula]

Hi,
I am using Hibernate/Display Tag for Pagination. Our DB has almost 8 million records and my query returns 653,151 records. Pagination performance is good when I am hitting the initial pages (0.1-2 Secs) but as I keep going towards the last set of pages the performance is very poor (~120 seconds). Can you guys suggest any tuning tips that would help in improving the performance?

Hibernate query code is as following (queryString is in HQL)

                     Query  query = session.createQuery(queryString);				
				int pageSize = Integer.parseInt(pageSizeEnum.getValue());
				query.setFirstResult(page * pageSize);
				query.setMaxResults(pageSize +1);				
				query.setReadOnly(true);
 

Hibernate generates the following query and this is taking major chunk of time

   select * from ( select row_.*, rownum rownum_ from ( select .......... ) row_ where rownum <= 261159) where rownum_ > 261140; 

[Cameron Wallace McKenzie]

I'd be going right to the database side to find points of optimization. Creating some type of View might be good. You've definitely got to have an index. Maybe even accessing data through a stored procedure.

With a million records, you've got to start pulling out some serious database side optimizations.

-Cameron McKenzie

[Brett Maclean]

Cameron's comments are spot on.

Also, is there another way that you can achieve what the requirement is ? I'm not sure what you're trying to do from a functional perspective, but if it's presenting to a UI then it's unlikely that your user will page through 600,000+ records (unless they are very patient or very bored :-). Maybe this is something that could be achieved through filtering (e.g. return all records where status = ACTIVE) or some kind of typeahead (e.g. return all records where username like 'Br%') rather than go for the whole result set ?

Either way you'll probably want to index the column you're performing the restriction on.

[Santosh Ramachandrula]

Cameron and Brett, thank you for the suggestion. I will work on these hint and get back to you.

(We have put indexes, including functional indexes in place where required.)

[Rahul Babbar]

I have a few doubts about the problem and its solution.

1) Why should the performance of the query reduce when you select the last set of records, since the query that you are executing is the same, only that the rownum values are different.(0.1-2 to 120 secs) is too much of a difference for the SAME query being fired.
2) I doubt your original query returning 353151 records will have much of a problem, simply because you original query fired will always have "rownum < N1 and rownum > N2" if you are using pagination. So, your number of records should not be much of a difference.
3) Only reason where you could have a bad performing query is if you use "ORDER BY " clause. This is because, when you do so, DBMS will create a temporary table, store all the result set in it and then try to sort them, so in that case the number of results your original query has, will matter.(Even though you may have an "ORDER BY" clause, i find it hard to convince myself that your query will be slow only when you are at the end).

So, what i suggest is, just see which queries are being fired during the first set of records and last set of records), mostly they will be similar.
If they are different, find out why are they different(they shouldnot be, only the rownum values should be different)
If they are same, they will take the same time(you can confirm this by viewing their explain plan or by directly running them on the DB through some client).

[Paul Sturrock]

) Why should the performance of the query reduce when you select the last set of records, since the query that you are executing is the same, only that the rownum values are different.(0.1-2 to 120 secs) is too much of a difference for the SAME query being fired.

Its not the same query. Its repeated simmilar queries (see the SQL generated).

[Rahul Babbar]

Paul Sturrock wrote:
Its not the same query. Its repeated simmilar queries (see the SQL generated).

I am not sure i got that...

I think the queries will be similar when the 5th set of rows are accessed or 500th set of row are accessed..

It will be

  
select * from ( select row_.*, rownum rownum_ from ( select .......... ) row_ where rownum <= 100) where rownum_ > 80; 

for the 5th set of rows
and

  
select * from ( select row_.*, rownum rownum_ from ( select .......... ) row_ where rownum <= 10000) where rownum_ > 9980; 

for 500th set of rows....(considering display size of 20 in each page)

These queries are similar..
So, ideally the performance of both these queries has to be same...

I am sure Hibernate will not keep on repeating queries inside each query for getting the 500th (or more) set of records.....If it did, i suppose it could be a big problem....which is not the case...

Please correct me in case i have missed something or i am wrong....

[Santosh Ramachandrula]

Rahul,
For your questions 1 & 2 please see similar issue at http://forum.springsource.org/archive/index.php/t-61016.html

For 3, I am not using "order by" clause.

As suggested by forum members DB optimization and re-visiting the user requirement seem to be the option(s).

[Rahul Babbar]

Santosh Ramachandrula wrote:Rahul,
For your questions 1 & 2 please see similar issue at http://forum.springsource.org/archive/index.php/t-61016.html

Thanks for the above link, it indeed is the same issue.
However, the discussion(in the link) also does not seem to give a proper explanation as to why the query should be slower for the last set of records...

It could be a bug in some version of Oracle 10g or.....(well, i cant see any other reason... )

[Estelle DeBlois]

Rahul Babbar wrote:

Santosh Ramachandrula wrote:Rahul,
For your questions 1 & 2 please see similar issue at http://forum.springsource.org/archive/index.php/t-61016.html

Thanks for the above link, it indeed is the same issue.
However, the discussion(in the link) also does not seem to give a proper explanation as to why the query should be slower for the last set of records...

It could be a bug in some version of Oracle 10g or.....(well, i cant see any other reason... )

It's been a few months since somebody posted into this thread but I happened to wander through this and saw that the question as to why the query runs slower for the last set of records wasn't clearly answered... actually it was answered in http://forum.springsource.org/archive/index.php/t-61016.html but perhaps the last poster of this thread (Rahul Babbar) had missed it?

So here's my attempt at answering it with the little knowledge that I have. This article on "Ask Tom" explains it all: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html (in particular, the section on Pagination using ROWNUM but you still have to read the whole thing to make the most sense out of it).

If you look at that query closely, you'll notice that it incorporates a top-N query (get the first :MAX_ROW_TO_FETCH rows from your query) and hence benefits from the top-N query optimization I just described. Further, it returns over the network to the client only the specific rows of interest—it removes any leading rows from the result set that are not of interest.

For the first page, your :MAX_ROW_TO_FETCH will be low (say, 20). Oracle will perform a top-N query using that value ("get me the first 20 results, sorted") then removes 0 leading rows since you're on the first page. If you're on the last set of pages, your :MAX_ROW_TO_FETCH will be much larger (say, 261159). You're only interested in displaying 20 results on your page, but Oracle still has to first perform a top-N query with the :MAX_ROW_TO_FETCH value ("get me the first 261159 results, sorted"); this takes much longer. Only afterwards will it discard the leading rows so you only end up with the 20 results to show on your page.

[Rahul Babbar]

Estelle,

Thanks for the explanation and the asktom link, Estelle.

I seem to have understood the reason now.

Thanks

[Rahul Jadaun]

I was just browsing to through the site and fell upon this post. Its informative and might be of help in my project.
Thanks everyone.