This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Hibernate/Display tag Pagination (Poor/slow performance for the last set of pages)/ Oracle 10 G Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate/Display tag Pagination (Poor/slow performance for the last set of pages)/ Oracle 10 G" Watch "Hibernate/Display tag Pagination (Poor/slow performance for the last set of pages)/ Oracle 10 G" New topic
Author

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

Santosh Ramachandrula
Ranch Hand

Joined: Apr 04, 2004
Posts: 252
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)



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



Thanks,
Santosh
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

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
Greenhorn

Joined: May 01, 2009
Posts: 22
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.


http://www.ontruenorth.com
Follow us on Twitter: http://twitter.com/truenorth_buzz
Santosh Ramachandrula
Ranch Hand

Joined: Apr 04, 2004
Posts: 252
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
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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).


Rahul Babbar
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


) 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).


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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



for the 5th set of rows
and



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
Ranch Hand

Joined: Apr 04, 2004
Posts: 252
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
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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
Greenhorn

Joined: Dec 14, 2008
Posts: 4
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
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
Estelle,

Thanks for the explanation and the asktom link, Estelle.

I seem to have understood the reason now.

Thanks
Rahul Jadaun
Ranch Hand

Joined: Oct 05, 2006
Posts: 36
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate/Display tag Pagination (Poor/slow performance for the last set of pages)/ Oracle 10 G
 
Similar Threads
Pagination
Pagination
Help required with DB pagination
Hibernate pagination performance problem
Problem while displaying the LIst in JSP