Search code examples
mysqlsqlperformancehibernatejdbc

The same MySQL query shows up multiple times on Hibernate console probably causing the slow response

I recently encountered by activating setShowSql(true) shows me many MySQL queries repeatedly several times(mostly 3 times) while testing on development mode where I have just one user session.And that is the main cause of my website slowdown during high traffic and by monitoring my website on new relic monitoring system, it shows me always DataSource.getConnection() holding so much time. please checkout this following screenshot..

enter image description here

I still not sure what is the main cause behind of this slowness.and Few developers suggested me to use @PostConstruct is the contract that guarantees that this method will be invoked only once in the bean lifecycle.but it doesnt work at all.

For instance here mysql query which repeats several times.

Hibernate: select count(favorite0_.id) as col_0_0_ from favorite favorite0_ left outer join User user1_ on favorite0_.user_id=user1_.id where user1_.id is null

Hibernate: select count(favorite0_.id) as col_0_0_ from favorite favorite0_ left outer join User user1_ on favorite0_.user_id=user1_.id where user1_.id is null

Hibernate: select count(favorite0_.id) as col_0_0_ from favorite favorite0_ left outer join User user1_ on favorite0_.user_id=user1_.id where user1_.id is null

Here is my connection pool configuration (Tomcat 7´s context.xml)

<Resource auth="Container"
          driverClassName="com.mysql.jdbc.Driver"
          maxActive="100"
          maxIdle="10"
          name="jdbc/fappi"
          password="xxxxxxxx"
          type="javax.sql.DataSource" 
          url="jdbc:mysql://localhost:3306/coexi useUnicode=true&amp;characterEncoding=utf-8"
          username="root"
          timeBetweenEvictionRunsMillis="600000"
          removeAbandoned="true"
          testWhileIdle="true"
          removeAbandonedTimeout="100"
          validationQuery="select 1"
          testOnBorrow="true" />

I request to all of you to please give me suggestions.

Many thanks in advance.. Tim

Solution

  • Looks like a N+1 query issue to me.

    You should use an automatic SQL statement validator to ensure that the number of SQL statements generated by a data access layer method is the expected one.