Hi,

I am having an issue with a query taking much longer than I think it should.

I am using Hibernate via the Java persistence API. When performing my query using EntityManager.createQuery.getResultList, it takes 35 seconds to retrieve about 400 records. I put together a raw SQL query get the same data. When using raw sql, fetching the corresponding records and creating the Java objects takes around .5 seconds.

I am fetching data for two classes. B is a subclass of A, D is a subclass of C and there is a one to many relationship between base class A and subclass D. With Hibernate, I am trying to fetch instances of class B. The query should create 70 instances of class B, each of which has a set of 5 D's. All my fetches are specified as eager.

When I turn on show_sql, I am seeing 157 queries being generated for each call to getResultList, but only 7 of the queries are unique. Class D references a couple of other classes, so 7 queries seems about right.

I ran the fetch twice using Hibernate to see if there is some kind of initialization overhead which would not be incurred on fetches after the first, but I am getting the same times and number of generated queries both times. I have another fetch which seems like it should be much more complex (13 classes/tables) which only takes 1 second to return about the same number of objects using Hibernate.

I suspect the speed issue is related to the high number of queries being generated. Any suggestions on what is going on and if there is a way to speed up the query ?

Thanks.

i think u will use criteria api with association or use select clause for specific fields or turn off lazy loading.