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

[Steve Ebersole]

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