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.

What are those queries? How many hits do the web server do you have per second? — JB Nizet
– JB Nizet, Commented Mar 26, 2011 at 11:41
466 queries per second. Those queries are HQL queries. But i think I have got the problem. Perhaps it is Lazy initialization which is firing queries . — Manish Mudgal
– Manish Mudgal, Commented Mar 26, 2011 at 14:28
You might want to enable SQL logging to see what's actually going on. Without this, you're just shooting in the dark. — mindas
– mindas, Commented Mar 26, 2011 at 22:48
@ManishMudgal if you found a solution please put it as an answer and accept — Reddy
– Reddy, Commented Feb 21, 2012 at 6:56

rahul · Accepted Answer · 2011-03-31 23:31:50Z

3

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.

answered Mar 31, 2011 at 23:31

rahul

1,3002 gold badges12 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlad Mihalcea · Accepted Answer · 2019-11-12 05:29:55Z

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.

edited Nov 12, 2019 at 5:29

answered Oct 27, 2016 at 19:22

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Comments

Dave · Accepted Answer · 2011-04-03 03:12:35Z

0

How are you using JTA transactions? If every Java method is marked to require a new transaction, this could explain part of your problem. Also, your Hibernate object relationships should be reviewed. If you have complex relationships, a lot of eagerly-loaded objects defined in your model relationships, or circular relationships, you could have harder problems to solve.

answered Apr 3, 2011 at 3:12

Dave

7976 silver badges10 bronze badges

Collectives™ on Stack Overflow

Hibernate Issuing too many queries on MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related