File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Hibernate Query taking long time Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate Query taking long time" Watch "Hibernate Query taking long time" New topic
Author

Hibernate Query taking long time

M Mehta
Ranch Hand

Joined: Aug 03, 2006
Posts: 92
Hi All,

I have a hibernate criteria query running on tables which have huge amount of data around 29L rows in the largest one. But the conditions in the query are such that the number of rows in the final output are only 365.

The query is taking such a long time if run through application that the transaction gets timeout in WAS after 120 sec. When I extract the same query from logs, with show SQL true, it runs in 10 sec. The query has some outer joins and date comparisons as well.

Can anyone help me in knowing what could be the reason for such a long time running through hibernate.

Thanks,
Me
Charan kumar sarvepalli
Ranch Hand

Joined: Dec 14, 2008
Posts: 40
hi,

may be criteria is taking time to create POJO's. which will get all columns of the both tables for your joining tables and creates POJO's.
try to put lazy false on joined tables. use paging for more performance.


-- Charan
M Mehta
Ranch Hand

Joined: Aug 03, 2006
Posts: 92
Here is the query being generated.



Do you want me to post the code using Criteria query as well?

Regards,
Me
M Mehta
Ranch Hand

Joined: Aug 03, 2006
Posts: 92
can anyone please help me on this?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

When you run this SQL in whatever query analysis tool your database provides what does it recommend?

Once the query has run, what other SQL is generated? Do you use lazy initialization for all objects associated with the ones you return?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
M Mehta
Ranch Hand

Joined: Aug 03, 2006
Posts: 92
I analysed the query using Quest Analyzer. This is the best planned query.

I read somewhere that criteria queries are usually slower than the HQL queries so replaced Criteria query with an HQL. The HQL is like



The corresponding sql being generated by hibernate is


But the execution hangs when running this query Is there no way to optimise and run huge queries in hibernate. I also tried changing to lazy as false for the outer joined tables in mapping.
M Mehta
Ranch Hand

Joined: Aug 03, 2006
Posts: 92
Also running the SQL query through SQL developer returns result in 22 sec. Can it be checked why the hibernate query gets hung?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I read somewhere that criteria queries are usually slower than the HQL queries so replaced Criteria query with an HQL

This is pure speculation - I would disregard this.

22 seconds for a single query is quite a long time, unless your application is some sort of bulk data processing one? How many rows does it return? How much memory does your application consume while running this?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate Query taking long time