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

[Steve Ebersole]

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