How can I count how many SQL queries hibernate does in one Grails request?

Question

I need to debug a Grails application with one really slow request. I have SQL logging but would like to see the amount of SQL-queries without counting them manually.

debug    'org.hibernate.SQL'
trace    'org.hibernate.type'

For eaxmple to have following line after each request (where x is the amount of all queries made to SQL server):

[2012-10-04 13:41:45,049][LoggingFilters] INFO - Request finished in 8296 ms and made x SQL statements

After some googling this doesn't seem to be possible with Grails so maybe MySQL could provide the information?

Answer 1

You can do it by using Filters and Hibernate statistics. Create class ExampleFilters.groovy in conf folder. This is the content of the class:

import org.hibernate.stat.Statistics
class ExampleFilters {

    def sessionFactory


    def filters = {
    // your filters here

        logHibernateStats(controller: '*', action: '*') {
            before = {
            Statistics stats = sessionFactory.statistics;
            if(!stats.statisticsEnabled) {stats.setStatisticsEnabled(true)}
                   }

        afterView = {
            Statistics stats = sessionFactory.getStatistics()
            double queryCacheHitCount  = stats.getQueryCacheHitCount();
            double queryCacheMissCount = stats.getQueryCacheMissCount();
            double queryCacheHitRatio = (queryCacheHitCount / ((queryCacheHitCount + queryCacheMissCount) ?: 1))
            println """
######################## Hibernate Stats ##############################################
Transaction Count:${stats.transactionCount}
Flush Count:${stats.flushCount}
Total Collections Fetched:${stats.collectionFetchCount}
Total Collections Loaded:${stats.collectionLoadCount}
Total Entities Fetched:${stats.entityFetchCount}
Total Entities Loaded:${stats.entityFetchCount}
Total Queries:${stats.queryExecutionCount}
queryCacheHitCount:${queryCacheHitCount}
queryCacheMissCount:${queryCacheMissCount}
queryCacheHitRatio:${queryCacheHitRatio}
######################## Hibernate Stats ##############################################
"""
            stats.clear()
        }

    }

    }

}

For reading more about various Hibernate statistics read this article: http://www.javalobby.org/java/forums/t19807.html

Also note that there is a performance impact when using this, so it should really be used only in development environment.

Answer 2

Have you considered some low tech solutions like running the output through wc -l?