CODEWITHSUNDEEP
sqlhibernategrailslogging
aarreoskari
aarreoskari

Reputation: 931

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

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?

Upvotes: 8

Views: 3255

Answers (4)

Roberto Perez Alcolea
Roberto Perez Alcolea

Reputation: 1414

As Burt Beckwith said in his blog post "Stuff I Learned Consulting" http://burtbeckwith.com/blog/?p=1570

SQL Logging

There are two ways to view SQL output from queries; adding logSql = true in DataSource.groovy and configuring Log4j loggers. The Log4j approach is a lot more flexible since it doesn’t just dump to stdout, and can be routed to a file or other appender and conveniently enabled and disabled. But it turns out it’s easy to toggle logSql SQL console logging. Get a reference to the sessionFactory bean (e.g. using dependency injection with def sessionFactory) and turn it on with

sessionFactory.settings.sqlStatementLogger.logToStdout = true

and off with

sessionFactory.settings.sqlStatementLogger.logToStdout = false

Upvotes: 0

MBozic
MBozic

Reputation: 1192

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.

Upvotes: 9

Ken Liu
Ken Liu

Reputation: 22914

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

Upvotes: 1

solaimuruganv
solaimuruganv

Reputation: 29857

in grails use loggingSql 

dataSource {
 dbCreate = "update" // one of 'create', 'create-drop','update'
 url = "jdbc:postgresql://localhost:5432/demodb"
 loggingSql = true
}

you’ll notice that all SQL statements Grails utilize will be logged.

Upvotes: 0

Related Questions