Easy String Concatenation Considered Harmful?

This is not about the amount of String objects generated when you do string concatenation in Java. This is about databases and JDBC.

Performance problem?

When encountering performance problems programmers have with Derby and other databases I very often find application code like

Statement s = c.createStatement();
while () {
   ....
   ResultSet rs = s.executeQuery("select * from t where i > " + j);
   ....
}

This will result in a prepare (or compilation as some will call it) of the query each time executeQuery is called, and since the query will be different each time, no statement cache mechanism can help you avoid the compilation, either. Since prepare often is much heavier than execution, this will execute pretty slow.

Preformance problem solved!

The problem is easily solved with the following pattern (which most programmers should know):

PreparedStatement ps = c.prepareStatement("select * from t where i > ?");
while () {
   ps.setInt(1,j);
   ResultSet rs = ps.executeQuery();
   ...
}

Prepare is done only once, and if the loop iterates one zillion times, this really amounts to something!

Why?

There may be several possible explanations to this and one of them could be:

It's far too easy to concatenate strings in Java!

Programmers are obviously lazy (and need to meet deadlines), and the last (and most efficient) alternative takes somewhat more energy to write. I often find myself in situations where I use the first variant because I'm in a hurry. I'm just writing a code piece for bug-finding, some sample code or I thinking might optimize it later. I'm beginning to wonder if it's a bad habit.

If you look at C code and ODBC, it's the other way round. Programmers use the prepared statements and string concatenation is a nuisance with strncat, strdup and strncpy, so in ODBC, the last variant would be the easiest one to write.

Also done by tools

I have also found the same pattern in application code generated by some tools. I have even seen this:

while () {
   PreparedStatement ps = c.prepareStatement("select * from t where i > " + j);
   ResultSet rs = ps.executeQuery();
   ...
}

which for some databases will give you an extra round-trip to the database, too! (Derby may run in embedded mode where the extra procedure call does not matter much).

Be prepared!

The lesson from this is pretty simple:

Use prepared statements! (and don't forget to reuse them.)