Hibernate Issuing too many queries on MySQL

Question

I am using hibernate 3.0 in spring with Mysql 5. I have configured JNDI datasource in JBOSS and using it in application context.

My Problem is that Hibernate is issuing average 466.4 queries per second to the database with hardly any load on website.

ApplicationContext.xml snippet is

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton">
    <property name="jndiName" value="java:MyCustomDSName" />
    <property name="resourceRef" value="true" /> 
</bean>

I am using JTA transaction at java level. Any help welcome.

Answer 1

One of these should be the case

• You're getting/processing too many requests - unlikely in dev.
• You're running into an N+1 select condition - very common.

Please post your domain model, and the queries being executed.

Answer 2

This is typically caused by N+1 query issues. However, you can use a unit test assert mechanism to find all those JPA and Hibernate data access methods that cause N+1 query issues, like this JUnit assert mechanism using datasource-proxy.

Also, you are better off switching all EAGER associations to LAZY because EAGER fetching can easily generate N+1 query issues if you forget to JOIN FETCH the associations in every JPQL or Criteria API query.

LAZY loading can also cause N+1 query issues. This happens when you iterate over a collections of child entities while initializing a parent Proxy on every iteration. Again, this can be easily detected using this JUnit assert mechanism using datasource-proxy.