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.