Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate Query taking long time

 
M Mehta
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
M Mehta
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can anyone please help me on this?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
M Mehta
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic