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

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

fwiw... I have a lot of this implemented locally. At the moment it is "swappable", but I have not yet exposed this as a setting. Also, I only have (1) and (2) implemented. (2) is implemented by padding with the main identifier that triggered the batch load. Plan is to work on (3) tomorrow, if I do not get to it today. https://hibernate.onjira.com/browse/HHH-7746 is the issue I am working for this. On Thu 01 Nov 2012 10:04:03 AM CDT, Steve Ebersole wrote: > > Just to clarify... (3) is not building the entire statement. Most of > the statement can be pre-built. It just needs to append the proper > IN-condition > > On Thu 01 Nov 2012 09:30:41 AM CDT, Steve Ebersole wrote: >> >> 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 > > > -- > steve(a)hibernate.org > http://hibernate.org -- steve(a)hibernate.org http://hibernate.org

Hi Steve, Sorry I missed the original series of emails, as unbelieveable as it sounds, gmail was acting strange recently... Ok, this is pushed to master. All 3 styles are implemented. See > issue for details. > > On Thu, Nov 1, 2012 at 2:43 PM, Steve Ebersole <steve(a)hibernate.org&gt; > wrote: > > fwiw... I have a lot of this implemented locally. At the moment it is > > "swappable", but I have not yet exposed this as a setting. Also, I only > > have (1) and (2) implemented. (2) is implemented by padding with the > main > > identifier that triggered the batch load. > > > > Plan is to work on (3) tomorrow, if I do not get to it today. > This is really great news, it actually allows me to drop my hacked version of hibernate-3.6 and finally update to 4.x. I guess dynamically generating the statement is what I am really looking for. I'll give it a try soon, thanks again =) - Clemens