SQL optimization options in Java

Question

Let's say I have a basic query like:

SELECT a, b, c FROM x WHERE y=[Z]

In this query, [Z] is a "variable" with different values injected into the query.

Now consider a situation where we want to do the same query with 2 known different values of [Z], say Z1 and Z2. We can make two separate queries:

SELECT a, b, c FROM x WHERE y=Z1

SELECT a, b, c FROM x WHERE y=Z2

Or perhaps we can programmatically craft a different query like:

SELECT a, b, c FROM x WHERE y in (Z1, Z2)

Now we only have one query (1 < 2), but the query construction and result set deconstruction becomes slightly more complicated, since we're no longer doing straightforward simple queries.

Questions:

• What is this kind of optimization called? (Is it worth doing?)
• How can it be implemented cleanly from a Java application?
  • Do existing Java ORM technologies help?

Answer 1

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.