We need to fetch data from Terabase DB with the join of multiple tables. The query uses Subquery inside it to fetch some data for the base query. Its finally a huge query crossing 2 pages. If we execute the same query directly in the DB, its taking some fraction of seconds to produce the reply.
But if we use Hibernate, its taking more time around 5-10 mins or some time it raises memory crashes and session problems. We have proper ORM mapping between the objects and uses JPA for mapping with the tables.
The TeraDB has lakhs of records and have interrelations within the tables.
My question is, is Hibernate the right solution for executing Big and complex queries? Whoever used Hibernate with big queries might have faced this problem. Experts please suggest.

Thanks,

Have you profiled your use-case? There is a number of bottleneck reasons, for example:

* network bottleneck because of huge traffic between application and database;
* relational -> object representation processing, i.e. when query results are transformed to the application level objects;

Right approach is:

* compare hibernate-based use-case processing with plain jdbc-based one;
* profile the application in order to find out the real bottleneck;

I.e. you must exactly identify the problem before trying to solve it.