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.
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.
Joined: Aug 03, 2006
Here is the query being generated.
Do you want me to post the code using Criteria query as well?
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?