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

[Steve Ebersole]

Maybe its best to make this configurable too?

I can see 3 selections:
1) current algorithm - the current code as is
2) current size break down but with padding - the current code except 
selecting the next higher batch size and padding extras (current code 
selects next smaller size, iteratively)
3) dynamic - dynamically build the batch statement

wdyt?

On Tue 30 Oct 2012 02:28:31 PM CDT, Steve Ebersole wrote:
> Well the other option is to not pre-build/cache the statements for the
> batch sizes.  That would free up memory as well as reduce the number
> of queries to just one, with the offset being the need to build the
> SQL dynamically (although most of it could be built up front).
>
>
> On 05/08/2012 02:06 AM, Clemens Eisserer wrote:
>> 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
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>

--
steve at hibernate.org
http://hibernate.org