Performance Zone is brought to you in partnership with:

Tom is a passionate Java/JEE developer working remotely from home located in Poland. He is a big fan of Apache Wicket, clean code, testing and methods with names that actually say something. In his free time he blogs, tweets and organizes Confitura, the biggest Java conference in Poland. Privately husband, father and dog owner, he likes to watch and play football (soccer if you're from USA). Tomasz is a DZone MVB and is not an employee of DZone and has posted 15 posts at DZone. You can read more from them at their website. View Full User Profile

Hibernate Smoke Detector Counts Your Queries Per Second

  • submit to reddit

There is a time in the life of every project when Database Administrator starts to look suspiciously at queries generated by our application. And then he (or she) sends us long list of queries that are not optimized, do not have proper indexes set or are executed multiple times in a very short time. And of course our role is to fix it.

Some issues are hard to solve but most of them are effect of our mistakes: N+1 problem in Hibernate is well-known (e.g. StackOverflow discussion) or checking the same condition (e.g. count something to figure visibility) multiple times for many components on the same page without caching it it any way. Of course we could turn on sql/hql logging during development process but sometimes we might not notice that here or there there is a problem with some query.


And here comes the solution. Simple parser analyzing hql logs and reporting which queries are executed too often. It’s far from being perfect or super universal but it works I called it Hibernate Smoke Detector.

How it works:

  1. Set up logger in your application to write all hql queries to a separate file
  2. Run your end-to-end tests (in our case Selenium tests) or just let testers do some clicking through your application
  3. Get sources from
  4. mvn clean install
  5. Run Hibernate Smoke Detector with your log file: java -jar hibernate-smoke-detector/target/hibernate-smoke-detector-1.0-jar-with-dependencies.jar [your_log_file] >  [your_report_file]
  6. Analyze the report and fix problems.

Hibernate Smoke Detector counts how many each query was executed in each second, then compiles report starting from queries that have the highest counter. Currently the only drawback is that parser needs specific date time format in the logs:

pattern="%d{HH:mm:ss,SSS} %-5p [%c] %s%E%n"

This part of log is indispensable so parser could put each query to the proper “date-time basket”, so if you want to use if out of the box, you should setup your logger accordingly. If you can not change date format in hql log file, you have to fork the project and change source code a little bit.

Example report might look like presented below:

Report contains all sql commands with the number of executions in each second
Number of executions: 41
Time: 15:07:44
     PROJECT.client client0
Number of executions: 34
Time: 14:29:03
     PROJECT.application applic0

So if you compare execution times from this report with time of test execution from report generated by your testing library (TestNG, JUnit, etc.) you will be able to find places and actions that fire those queries. And then maybe fix something to make application better and your DBAs  happier :)


Hibernate Smoke Detector is simple but quite useful parser that checks your application and helps to cut identical queries fired many times in a very short period of time. It generates a report showing which queries are executed most often in a one-second time window and when they fired so developer could find them in his code easily. Using this mini-application should help you to avoid some problems with your queries that often go to the production unnoticed: N+1 problems and duplicated calls to the database checking condition on the same page/view.

Source code is available on GitHub: . If you have any comments, please share them below.

Published at DZone with permission of Tomasz Dziurko, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)


Lance Semmens replied on Tue, 2012/07/10 - 8:43am

I'm surprised that you based your solution on parsing a log file. Surely you could write a proxy to java.sql.Connection or org.hibernate.Session to do the dirty work?

zim 2001 replied on Tue, 2012/07/10 - 3:10pm in response to: Lance Semmens

Like maybe ?

Joonas Javanainen replied on Wed, 2012/07/11 - 4:15am

I'm not a fan of using log files for this, because a busy system would write a huge amount of logging data. 

A subset of that information is directly available from org.hibernate.stat.Statistics. And if an external monitoring system needs that information, Hibernate supports JMX exporting out of the box.

John Lee replied on Mon, 2014/08/11 - 10:40am

 Great response. Looks like nothing to worry in case of emergency happens.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.