{"id":4461,"date":"2011-11-07T13:08:22","date_gmt":"2011-11-07T07:38:22","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=4461"},"modified":"2016-12-19T15:19:23","modified_gmt":"2016-12-19T09:49:23","slug":"grails-find-number-of-queries-executed-for-a-particular-request","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/grails-find-number-of-queries-executed-for-a-particular-request\/","title":{"rendered":"Grails: Find number of queries executed for a particular request"},"content":{"rendered":"
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.<\/div>\n
We can get this information by just simply adding the following filter to our application(Filters in Grails)<\/div>\n

[groovy]
\n\t logHibernateStats(controller: ‘*’, action: ‘*’) {
\n\t\t before = {
\n\t\t\t\tStatistics stats = sessionFactory.statistics;
\n\t\t\t\tif(!stats.statisticsEnabled) {stats.setStatisticsEnabled(true)}
\n }<\/p>\n

\t\t\tafterView = {
\n\t\t\t\tStatistics stats = sessionFactory.getStatistics()
\n\t\t\t\tdouble queryCacheHitCount = stats.getQueryCacheHitCount();
\n\t\t\t\tdouble queryCacheMissCount = stats.getQueryCacheMissCount();
\n\t\t\t\tdouble queryCacheHitRatio = (queryCacheHitCount \/ ((queryCacheHitCount + queryCacheMissCount) ?: 1))
\n\t\t\t\tlog.info &quot;&quot;&quot;
\n######################## Hibernate Stats ##############################################
\nTransaction Count:${stats.transactionCount}
\nFlush Count:${stats.flushCount}
\nTotal Collections Fetched:${stats.collectionFetchCount}
\nTotal Collections Loaded:${stats.collectionLoadCount}
\nTotal Entities Fetched:${stats.entityFetchCount}
\nTotal Entities Loaded:${stats.entityFetchCount}
\nTotal Queries:${stats.queryExecutionCount}
\nqueryCacheHitCount:${queryCacheHitCount}
\nqueryCacheMissCount:${queryCacheMissCount}
\nqueryCacheHitRatio:${queryCacheHitRatio}
\n######################## Hibernate Stats ##############################################
\n&quot;&quot;&quot;
\n\t\t\t\tstats.clear()
\n\t\t\t}<\/p>\n

}
\n[\/groovy]<\/p>\n

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

Gotchas:<\/p>\n

    \n
  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)<\/li>\n
  2. Also these stats may not be correct if you have any background jobs running simultaneously<\/li>\n<\/ol>\n

    This is basically one of the things that we do when we want to ‘tune’ the performance of an application.<\/p>\n

    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<\/a><\/p>\n

    Regards
    \n~~Himanshu Seth~~<\/p>\n

    http:\/\/www.tothenew.com<\/p>\n","protected":false},"excerpt":{"rendered":"

    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) [groovy] […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":4},"categories":[7],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/4461"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=4461"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/4461\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=4461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=4461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=4461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}