Grails: Find number of queries executed for a particular request

[groovy]
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))
log.info """
######################## 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()
}

}
[/groovy]

There are a bunch of other stats that we can get to do more analysis  If you see a large amount of queries, you can simply turn sql logging on for the particular action in this way

Gotchas:

1. Since we are getting the stats from sessionFactory, they just do not pertain to any particular request. So this will not work in a multiuser enviornment(prod/qa)
2. Also these stats may not be correct if you have any background jobs running simultaneously

This is basically one of the things that we do when we want to ‘tune’ the performance of an application.

PS: Performance tuning comprises of many more things. Peter ledbrook gave an excellent session on it in last year’s SpringOne2GX. You can find a summary here

Regards
~~Himanshu Seth~~

http://www.tothenew.com