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.
3 comments:
I don't always fetch-join the first time I write something, but I do later when most features are in. The problem is when people don't care enough to go back and fix shortcuts. And it doesn't help that mainstream Java dictum requires that the thing that needs the objects be two ad hoc layers away from the thing loading them, separated by various interfaces that will all need to be adjusted to achieve a simple fetch-join optimization.
Re: SQL, I just used .createSQLQuery for the first time last week when I realized the rows I was querying (postal rates) would never need to be represented as objects. But I don't think that SQL avoidance is at the heart of the problem you've described.
I did mix up my examples a bit I suppose. As you described, sometimes the problem is that people don't think about lazy and eager fetching (as the 631 select example demonstrated) or bother to "go back and fix the shortcuts".
However in the case I ran into yesterday, the original developers seem to have chosen hand written manual object traversal to get the results they could have gotten with a SQL count(*). I suppose I got a bit off track with my posting as I stopped to think about some of the other goofy query related problems I've seen in this app.
But the point stands. Be aware of that SQL engine and don't be afraid to use it when it's appropriate. It seems that the designers of C# 3.0 understand that query expressions are far more appropriate than a lot of procedural code. Maybe it's about time we should all learn that lesson.
BTW: it had to be SQL because Hibernate wouldn't have been able to handle some of the complexities of that query.
PS: I repeated myself a bit. Here's another posting that describes some remedies for eager fetching.
Ha Ha, I've actually worked on that same project and it still gives me nightmares. I think I was the first on there to turn on SQL logging only to crap myself and turn it back off.
Based on the experience of that project and similarly structured Java Projects I don't think I'll ever voluntarily do anything with Hibernate. Ever.
After that experience I don't even write Java code for fun anymore. I'll write nice clean, easy OO PHP instead. If only I could get paid for it like Java, M$, Oracle and I'd do it full-time.
-My 2¢
Post a Comment