Dirty Dirty Hacks : Cheapo SQL Tuning for Hibernate Generated Queries

Today I've had a nightmare trying to figure out why a particular hibernate app was performing like a dog. After an hour of just looking at the logs and finding all the obvious points where the app was struggling it occurred to me that I needed something more exhaustive. Fortunately, having insisted on a high level of coverage for the use cases in the unit tests I'm confident that our unit tests exercise the SQL fully. Hmm interesting. What follows is representative of the pure joy of the unix operating system (Without the dodgy 70's black and white photos!).

So, turn on hibernate SQL logging, you get a mix of logging and SQL, so (We use maven as configuration managemnt)

mvn clean package | grep "Hibernate: select" 

Just pulls out the select SQL. Cool, 8.5k bits of SQL from the unit tests, thats a lot.

mvn clean package | grep "Hibernate: select" | sort -u

ooh sort and only output unique lines, down to ~350 statements

Right, the statements have bind variables in them and I want to pipe the statements into MySQL's explain feature, time for a quick script:

#!

# For each statement

while read line; do

  # Chop off the start of the line, replace all the ?'s with "" (Because explain plan doesn't care)

  sql=`echo $line | sed 's/^Hibernate: //' | sed 's/\?/""/g'`

  # For my sanity

  echo evaluating SQL:  $sql

mysql -u USER -pPASS DB <  explain $sql

!!!

  echo

done

Cool, take each statement and run it through MySQL Explain Plan. Final phase

mvn clean package | grep "Hibernate: select" | sort -u | perf_script.sh > analysys.txt

Leaves me with a file like

evaluating SQL: select authorityh0_.ID as ID162_, authorityh0_.DESCRIPTION as DESCRIPT2_162_, authorityh0_.TITLE as TITLE162_, authorityh0_.URL as URL162_ from IN_AUTHORITY authorityh0_ where authorityh0_.TITLE="0"
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE authorityh0_ ALL NULL NULL NULL NULL 2 Using where

OK, it doesn't automate the hard part, looking at the plan, but at least it makes the job a damn sight easier. Well, it does for me at least.

*peace* e.