It's spelled S-Q-L

Why do so many Java developers suck at SQL? Are they just born that way? Are they brainwashed? Do they think that Hibernate will just do it all for them? What's the problem?

The reason I ask is that I'm working on a Java system where the developers obviously thought nothing of letting Hibernate lazily instantiate every relationship as they iterated through collections and navigated down relationships. Not once did they stop to consider the SQL that was being thrown across the network to the database or the performance penalty that it would incur. Just a couple weeks ago I turned on Hibernate's SQL logging just to see 631 queries go flying by in order to prepare the data for a single JSP. WTF?! 631 Queries!!?

Then today I saw another atrocious example of bad code that instantiated several collections of some very large classes from the database to just throw them all away after navigating through them to get a count. Whatever happened to count(*)? In the end I replaced hundreds of lines of convoluted Java code with about twenty lines of SQL. Yes SQL, not HQL (which rocks BTW), but just wasn't appropriate for this task.

Perhaps it's unfair of me to pick on Java developers but for one reason or another a lot of the Java developers I run into have this resistance to leveraging the relational query engine at their disposal. They don't strike a good balance between the object oriented and relational worlds that their systems stride. Use the right language for the right task. And if you can't determine when to turn to SQL, turn on SQL logging and watch what the heck is going on. When you see SQL tearing by, just pause for a moment and ask yourself if there's a better way to do what you want. And remember you have decades of querying technology just waiting to be used. Don't be afraid of writing an elegant query.

Watch that SQL!

Long before I used object relational mapping tools like Hibernate or Toplink in Java or ActiveRecord in Ruby on Rails I used to write a fair amount of SQL (some real nasty queries too!). And in a lot of the shops where I worked it was standard practice to create an explain plan just to make sure that the database could execute the SQL efficiently. If it couldn't, I would dutifully restructure the query, add indexes, or go ask for some DBA help.

But nowadays in this wonderful land of ORMs people just let the framework crank out the queries and never ever look at them. To me, that's just plain wrong. If you're using Hibernate turn on the logging. If you're using Rails just go take a look at the development.log file and go watch what's going on! You may be surprised.

I started a new job a few weeks ago and just wanted to see what Hibernate was doing. And lo and behold, a JSP page was causing 69 queries to run in order to generate a simple list of rows from a table. The main problem was that every lazily instantiated relationship for each object in the collection was being traversed after the initial fetch.

So what could have been done? Well in this case, Hibernate has several options:

1. Hibernate's query language is quite expressive and something like select e from Employee e left outer join fetch e.department d would have caused both objects to be fetched in a single query.
2. The fetch keyword I used in the first example has one limitation (for good reason too). It can only be used to fetch one collection. So take advantage of Hibernate's caching to prefetch related objects instead. Hibernate will associate any newly instantiated objects with objects that already exist in cache. Even if you're not using something like EHCache you still have some caching built into your session so don't discount this tip right away.
   
3. Use a DTO-like object and go old-school and only select the fields you need with something like: select new EmployeeListDTO( e.firstName, e.lastName, d.name) from Employee e left outer join e.department d

Now if you're using ActiveRecord you don't have the double edged sword of a cache so the second option I described above isn't available, but the other two are possibilities:

1. Employee.find :all :include => :department
2. Employee.find_by_sql "select e.first_name, e.last_name, d.name from employees e left outer join departments d on e.department_id = d.id"
   

The second option shows one distinct design difference between Hibernate and ActiveRecord. Hibernate gives you a SQL-like query language but does away with having to specify join constraints because that information is in the mapping metadata. Whereas ActiveRecord says why reinvent SQL and just let's you have at it. Oddly enough I like both approaches...

Finally, be sure to use the ad-hoc query tools at your disposal to try out your queries before you embed them in your app. If you're using Hibernate 2.x then go get a copy of Hibernate Console (part of the Hibernate Tools). It can be a bit of a pain to get it setup but I really appreciate being able to work out the query before running it as part of my app. If you're using Hibernate 3.x and Eclipse then go get the Hibernate Tools plugin. If you're using Ruby on Rails then you already have all the tools you need. Simply drop to the command line, cd to your project directory and then type script/console. Open up a second shell window (if you're running unix) and type tail -f log/development.log from your project directory and you can instantly watch what SQL Rails generates when you execute those find methods.

Now go out there and watch what you're doing for database access! Don't let all that ORM goodness make you lazy.