• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Hibernate Query taking long time

 
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can anyone please help me on this?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic