Grails: Find number of queries executed for a particular request

When response time of a page is slow, we might be interested in viewing database queries executed on that page. Enabling SQL Logging shows all queries but we are interested in queries for that particular request only.
We can get this information by just simply adding the following filter to our application(Filters in Grails)
	        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()
			}

        }

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.IntelliGrape.com

2 thoughts on “Grails: Find number of queries executed for a particular request

  1. Pingback: » Blog Archive

  2. Pingback: An Army of Solipsists » Blog Archive » This Week in Grails (2011-45)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>