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

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(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > -- steve(a)hibernate.org http://hibernate.org