2

I know there have been quite a few similar questions but I cant find an answer to this one:

I'm not very familiar with hibernate but I'm working on an app which contains an extremely slow hibernate query. This query is generated from a very simple query in the code:

Criteria crit = session.createCriteria(clazz); // clazz is the class of the Entities to load
return crit.list();

There are probably a lot of possibility which could make this query extremey slow (hibernate bindings, drivers, etc, etc.) but what I dont understand is the following: I set hibernate to output the SQL statements it performs. The code above is translated into an awfully long query containing 3 inner joins and 9 left outer joins, the result being displayed in a table with around 80 columns. I'm sure, however, that only this single SQL statement is produced by hibernate and executed for this code.

I'm using H2 database.

  1. When I run the code within the application, it takes around 20 seconds to execute (result set contains approx 6000 rows)
  2. If I code within the application a call to Session.createSQLQuery(query).list() where query is the sql generated by hibernate for the code above, it takes approx 30 sec to execute.
  3. When I execute the SQL query directly using H2 shell or even browser client, it takes less than a second to execute.

What could be the reason(s) for such a huge difference between those 3 ways of executing the same query. I would expect an overhead with hibernate, but not by this amount.

remi
  • 3,914
  • 1
  • 19
  • 37
  • there is difference between the query executed by ORM tool and in SQL Client / browser. ORM tool will also load the other entities associated with the entity you are trying to load based on the loading strategy.Check your loading strategy and show your query to understand the complexity – Mani Jan 06 '14 at 16:23
  • Loading strategy is fetch join so indeed all the entities associated with the queried entities will be loaded aswell, using a single SQL query. But could this overhead cause such a difference btw execution time? Can it take such a long time to load many entities into hibernate session? – remi Jan 06 '14 at 16:46
  • If we perform this query to load READ-ONLY entities, that we are sure will not be modified after the query, can it be possible to tune something to tell hibernate not to load all those entities in it's session cache? – remi Jan 06 '14 at 16:48
  • yes, it does. when you mention that the sql script returned in a second, did you sql script returing all 6000 rows or just few records based on pagination. For exam , i used to have toad while execute the query it just fetch 500 records ( so we cant compare the time between the toad and fetching all 6000 records in multiple joins query). try to fetch only the clazz instance / add debug log to know what is happening – Mani Jan 06 '14 at 16:53
  • the SQL script did return all the results, same number as hibernate query. If I do lazy loading of all associate classes, the query is executed faster (~ 5sec), but then I get to generate thousands of SQL queries because I used information from associated entities. This is an overhead at least I understand. – remi Jan 06 '14 at 16:58
  • that is correct , that is overhead , we are taking about n+1 select problem. But do you need all records at initial stage , are you sure your gonna need all the elements and all the field in other entities ? You dont need to fire thousands query , you can do sub select ( which is m+1 when m is number of lists). Check the answer http://stackoverflow.com/questions/2593029/how-can-i-resolve-the-n1-selects-problem it may help you.. – Mani Jan 06 '14 at 17:08
  • Thx for pointing the solution. However, how comes the same SQL query triggered via hibernate createSQLQuery() function is so much slower? Does hibernate parse the query before firing it to the DB ? – remi Jan 07 '14 at 08:31
  • Hibernate Does the parse , but parsing is not costly here, when you ask any ORM to execute query , it will execute the query and pull all the related objects ( related entity) based on your strategy and join method you mentioned in query .. So it is not taking time to do just parse . it also pull the relational data – Mani Jan 08 '14 at 01:40
  • What if the query returns only scalar values, not entities? Then it should be comparable isn't it? – remi Jan 08 '14 at 13:45
  • if you are pulling only one type of entities without relation, then it is comparable against the any JDBC client (Note** if you enabled second level cache then you have to add the cost from second level cache , if not then it should be comparable) . – Mani Jan 08 '14 at 15:03

0 Answers0