Can anyone help me figure out why Hibernate is generating thousands of queries when I expect to to only generate a handful of queries?

We are building a web application using JSF, Hibernate, Spring and several other frameworks. We are using the Java Annotations for Hibernate and although we are getting the correct results the performance of our system is dismal. Hibernate issues thousands of queries for what something that I could do with one short SQL query.

Can you give me some suggestions of things to look for?

Anyone have any insight of what may be happening?

Is there some kind of a profiling or debugging mode that may help us figure out what is going on?


Thanks,
Steve

In order to evaluate how many queries are generated, you don't need any special profiler tool . Just log (using log4j) any output at DEBUG level coming from the package org.hibernate.SQL. That will allow you to log all SQL statements to a different file, or to the console if you want.

You can reduce the amount of generated SQL by changing your fetching strategy. Your abundance of SQL queries is most likely caused by child collections performing one query per collection element.
Try adding the property fetch="join" to the mapping files of your child collections.

If you are using Hibernate queries or HQL, try adding the "fetch" keyword to your queries. This approach is preferable to modifying the mapping file directly, because it is something you can do (or not) on a case-by-case basis.

Thanks for the quick response! I noticed the problem looking at the server logs from MySQL and I think that your correct about it performing one query per collection element.

I'm going to try adding the fetch="join" attribute and see if that fixes the problem.

Thanks,
Steve

I found a resource on the web that describes hibernate's loading strategies:

http://www.hibernate.org/hib_docs/annot ... ntity.html
http://www.hibernate.org/315.html

The first document provides an annotation:
@[One|Many]ToOne](fetch=FetchType.EAGER) @LazyToOne(FALSE) @Fetch(JOIN)

i.e. For the join cols you can add an annotation like:
@ManyToOne(fetch=FetchType.EAGER)



Many of the join cols in my application already have this annotation. Is there something else that I should be adding?

I also noticed that our our application performed a number of HQL queries and I added the fetch keyword to the query but didn't notice any improvements. There are still hundreds or thousands of queried being issued.

FYI. Here's a doc that discusses using fetch in HQL queries.
http://www.hibernate.org/hib_docs/refer ... ryhql.html

I'm still having problems.

I changed to use fetch=join and it only had a minimal impact on the number of queries issued.

Does anyone have any other suggestions?