SQL optimization options in Java

What is this kind of optimization called?

I'm not sure if there is a "proper" term for it, but I've heard it called query batching or just plain batching.

(Is it worth doing?)

It depends on:

• whether it is worth the effort optimizing the query at all,
• the number of elements in the set; i.e. ... IN ( ... ),
• the overheads of making a JDBC request versus the costs of query compilation, etc.

But in the right circumstances this is definitely a worthwhile optimization.

How can it be implemented cleanly from a Java application?

It depends on your definition of "clean" :-)

Do existing Java ORM technologies help?

It depends on the specific ORM technology you are talking, but (for example) the Hibernate HQL language supports the constructs that would allow you to do this kind of thing.

An RDBMS can normally return the result of a query with IN in equal or less time than it takes to execute two queries.

If there is no index on column Y, then a full table scan is required. With two queries, two table scans will be performed instead of one.

If there is an index, then the single value in the WHERE clause, or the values in the IN list, are used one at a time to look up the index. When some rows are found for one of the values in the IN list, they are added to the returned result.

So it is better to use the IN predicate from the performance point of view.

When Y represents a column with unique values, then it is easy to decompose the result. Otherwise, there is slightly more work.

I honestly can't say how much of a hit (if any) you will get if you run this two Prepared queries (even using plain JDBC) over combining them with an IN statement.

If you have an array or List of values, you could manually build the prepare statement using JDBC:

// Assuming values is an int[] and conn is a java.sql.Connection
// Also uses Apache Commons StringUtils

StringBuilder query = new StringBuilder("SELECT a, b, c FROM x WHERE y IN (");

query.append(StringUtils.join(Collections.nCopies(values.length, "?"), ',');
query.append(")");

PreparedStatement stmt = conn.prepareStatement(query.toString());

for (int i = 0; i < values.length; i++) {
    stmt.setInt(i + 1, values[i]);
}

stmt.execute();
// Get results after this

Note: I haven't actually tested this. In theory, if you used this a lot, you'd generalize this and make it a method.

Note that an "in" (where blah in ( 1, 5, 10 ) ) is the same as writing "where blah = 1 OR blah = 5 OR blah = 10". This is important if you are using, say, Apache Torque which would create lovely prepared statements except in the case of an "in" clause. (That might be fixed by now.)

And the difference in performance that we found between the unprepared in clause and the prepared ORs was huge.

So a number of ORMs handle it, but not all of 'em handle it well. Be sure to examine the queries sent to the database.

And while deconstructing the combined result set from a single query might be more difficult than handling a single result, it's probably a lot easier than trying to combine two result sets from two queries. And probably significantly faster if a lot of duplicates are involved.