[hibernate-dev] Where are the batched fetch statements generated?

[Clemens Eisserer]

Hi Guenther,

>>> Is it possible to disable prepared statement caching for batched fetching, so I end up with a single query in the < default_batch_fetch_size case only >>instead of the
>>> fixed-size batch loading hibernate does by default?

> I think the main reason for no feedback so far, is that nobody was able to understand this sentence.
> Usually 'prepared statement caching' is a synonym to 'prepared statement pooling' and is something which has to be provided by a connection-pool (or a jdbc-driver) and thus
> Hibernate does actually not implement any prepared statement cache/pooling.
> Can you please explain what you intend under 'prepared statement caching'?
> Can you also please try to better explain the second part of your sentence?

Sorry for beeing that cryptic, I will try to rephrase it:

When Hibernate does batch-fetching, it generates PreparedStatements
for certain batch sizes - for a batch_size of 50, the prepared
statements for batch-sizes will have the following sizes:
[1,2,3,4,5,6,7,8,9,10,12,25,50]. When e.g. a batch of size 13 should
be fetched, because of the fixed size of the prepared statements, 3
queries are issued for batch-fetching, although 13 <= 50. In this case
the 3 batches would be of the size 13 = 8 + 4 + 1.
In a latency bound (between db and application) environment, this
serverly hampers response time - instead of a single round-trip to do
the batched fetch, Hibernate requires 3.
(subselect can't be used in my case, because my queries are already
rather complex, and the added complexity confuses the DBs query
planner too much)

What I did in this case (only for integer PKs) is to pad up to the
next batch size with a non-existant PK.
So, for the example mentioned above, I can use the PreparedStatement
with size 25, and insert padding from 14-25, which will make the query
slightly more inefficient but avoid 2 additioan round-trips.

- Clemens