You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Pawan Verma <pa...@hotwaxsystems.com> on 2020/07/01 10:35:53 UTC
Re: EntityBatchIterator for large data set queries
Hi Chandan, Jacques
Thanks, for your feedback.
Yes, To solve the problem of heavy entity operations which consumes all the
system memory, we have implemented EntityBatchIterator. Originally designed
for the heavy entity operations.
--
Thanks & Regards
Pawan Verma
Technical Consultant
*HotWax Systems*
*Enterprise open source experts*
http://www.hotwaxsystems.com
On Sun, Jun 28, 2020 at 10:16 PM Jacques Le Roux <
jacques.le.roux@les7arts.com> wrote:
> Hi,
>
> I have not looked into any details but Chandan's advice sounds like a wise
> one to me
>
> Jacques
>
> Le 27/06/2020 à 13:43, Chandan Khandelwal a écrit :
> > Hello Pawan,
> >
> > Approach looks good, my only suggestion is to use batch processing only
> > when we are dealing with large data set, as this method takes a longer
> time
> > compared to the normal method specially on a distributed environment,
> which
> > may negatively impact the performance.
> >
> > Kind Regards,
> > Chandan Khandelwal
> > Senior Manager, Enterprise Software Development
> >
> > *HotWax Systems*
> > *Enterprise open source experts*
> > cell: +91-98934-81076
> > office: 0731-409-3684
> > http://www.hotwaxsystems.com
> >
> >
> > On Fri, Jun 5, 2020 at 4:07 PM Pawan Verma <
> pawan.verma@hotwaxsystems.com>
> > wrote:
> >
> >> Thanks, Pritam and Scott for the discussion.
> >>
> >> I've created Jira OFBIZ-11789 for this improvement and also created a PR
> >> with the proposed changes.
> >>
> >> I request everyone to review the PR and suggest your thought on this.
> >> Thanks!
> >> --
> >> Thanks & Regards
> >> Pawan Verma
> >> Technical Consultant
> >> *HotWax Systems*
> >> *Enterprise open source experts*
> >> http://www.hotwaxsystems.com
> >>
> >>
> >> On Mon, Jun 1, 2020 at 12:36 PM Pritam Kute <
> pritam.kute@hotwaxsystems.com
> >> wrote:
> >>
> >>> Thanks Scott for your detailed explanation.
> >>>
> >>> The solution looks good to me too. My confusion was with why we are
> going
> >>> to implement new method if we can achieve that using the current
> >>> EntityQuery methods.
> >>>
> >>> +1 for adding queryBatchIterator() to EntityQuery.
> >>>
> >>> Kind Regards,
> >>> --
> >>> Pritam Kute
> >>>
> >>>
> >>> On Thu, May 28, 2020 at 6:32 AM Scott Gray <
> scott.gray@hotwaxsystems.com
> >>>
> >>> wrote:
> >>>
> >>>> Hi Pritam,
> >>>>
> >>>> I'm not sure about PostgreSQL or Derby but I know with MySQL that
> >> using a
> >>>> cursor doesn't really work. You have to set the result set to
> >>>> TYPE_FORWARD_ONLY and CONCUR_READ_ONLY and also set the fetch size to
> >>>> INTEGER.MIN_VALUE. Only then will the driver stream the results and
> >> even
> >>>> then, you may not execute any other SQL commands on the connection
> >> until
> >>>> you have fully read or closed the resultset.
> >>>>
> >>>> So if an EntityListIterator doesn't really conserve memory, then you
> >> need
> >>>> to take a paging query approach such as this:
> >>>> EntityQuery query =
> >>> EntityQuery.use(delegator).from("SomeTable").limit(100)
> >>>> List results = null
> >>>> while (!(results = query.queryList()).isEmpty()) {
> >>>> for (value : results) {
> >>>> // do something with each value
> >>>> }
> >>>> query.offset(query.getOffset() + query.getLimit())
> >>>> }
> >>>>
> >>>> Or with the proposed EntityBatchIterator:
> >>>> Iterator query =
> >>>>
> >>>>
> >>
> EntityQuery.use(delegator).from("SomeTable").limit(100).queryBatchIterator()
> >>>> while (iterator.hasNext()) {
> >>>> result = iterator.next()
> >>>> // do something with each value
> >>>> }
> >>>>
> >>>> I guess an alternative approach would be to implement something
> similar
> >>>> within the EntityListIterator and perhaps a flag to turn it off or on
> >>>> depending on which database is being used and how well it supports
> >>>> iterating over results without loading the entire resultset into
> >> memory.
> >>>> Regards
> >>>> Scott
> >>>>
> >>>>
> >>>>
> >>>> On Sat, 23 May 2020 at 20:59, Pritam Kute <
> >> pritam.kute@hotwaxsystems.com
> >>>> wrote:
> >>>>
> >>>>> Hello Pawan,
> >>>>>
> >>>>> I just had a look into the EntityQuery.queryIterator() method and
> >> looks
> >>>>> like we can achieve that by using fetchSize(), fowardOnly(),
> >>>>> cursorScrollInsensitive(), cursorScrollSensitive() and offset()
> >> methods
> >>>> in
> >>>>> EntityQuery class. Let me know if I am missing anything.
> >>>>>
> >>>>> It will be good if you can post a pseudo code or something here so
> >> that
> >>>> we
> >>>>> could get an understanding of the exact design which you have in your
> >>>> mind.
> >>>>> Kind Regards,
> >>>>> --
> >>>>> Pritam Kute
> >>>>>
> >>>>>
> >>>>> On Thu, May 21, 2020 at 7:41 PM Pawan Verma <
> >>>> pawan.verma@hotwaxsystems.com
> >>>>> wrote:
> >>>>>
> >>>>>> Hello Devs,
> >>>>>>
> >>>>>> While working on the large database we have figured out that very
> >>> large
> >>>>>> queries consume all memory and crash ofbiz(because queryIterator()
> >>>>> doesn't
> >>>>>> really work, it's no different from queryList())
> >>>>>>
> >>>>>> The EntityListIterator attempts to use a cursor to iterate over
> >> large
> >>>>>> result sets but in reality most databases do not give us a cursor
> >>>> unless
> >>>>> we
> >>>>>> ask for it in a very specific way, and instead you get back the
> >> full
> >>>>> result
> >>>>>> set and potentially consume a large amount of memory. For example,
> >>> the
> >>>>>> MySql details are here (ResultSet section):
> >>>>>>
> >>>>>>
> >>
> https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html
> >>>>>> To work around this we've built the EntityBatchIterator which
> >> instead
> >>>>>> basically just pages through results like you would do on a webpage
> >>>> that
> >>>>>> uses pagination. The iterator takes an EntityQuery and when next()
> >>> is
> >>>>>> called it grabs the first 500 records and returns the first record,
> >>>> call
> >>>>>> next again and you get the second record, once you've called next
> >> 500
> >>>>> times
> >>>>>> it runs the query again with an offset and grabs the next 500 and
> >> so
> >>> on
> >>>>>> until there are no more records.
> >>>>>>
> >>>>>> The main downsides to this approach are:
> >>>>>> 1. Same as when using limit/offset you want to be sure your results
> >>>> will
> >>>>>> come back in a consistent order to avoid accidentally skipping some
> >>>> rows
> >>>>>> and seeing other rows twice.
> >>>>>> 2. Because the results are a combination of many queries rather
> >> than
> >>> a
> >>>>>> single query, some of the data may change while you are paging
> >>> through
> >>>>> it.
> >>>>>> i.e. if you were to sort by lastUpdatedTxStamp you may see some
> >> rows
> >>>>> twice
> >>>>>> as they are updated by other transactions (this might be a good
> >> thing
> >>>> in
> >>>>>> some cases).
> >>>>>>
> >>>>>> So, the main proposal is to introduce a new EntityBatchIterator for
> >>>> large
> >>>>>> queries. If we agree on the proposal I'll create a Jira and
> >> provide
> >>> a
> >>>>>> patch for the community review. Thanks!
> >>>>>>
> >>>>>> --
> >>>>>> Thanks & Regards
> >>>>>> Pawan Verma
> >>>>>> Technical Consultant
> >>>>>> *HotWax Systems*
> >>>>>> *Enterprise open source experts*
> >>>>>> http://www.hotwaxsystems.com
> >>>>>>
>
Re: EntityBatchIterator for large data set queries
Posted by Pawan Verma <pa...@hotwaxsystems.com>.
Hi Prakhar,
Glad to know that this implementation helps you. Thanks for sharing details
:)
--
Thanks & Regards
Pawan Verma
Technical Consultant
*HotWax Systems*
*Enterprise open source experts*
http://www.hotwaxsystems.com
On Mon, Jul 20, 2020 at 1:42 PM Prakhar Kumar <
prakhar.kumar@hotwaxsystems.com> wrote:
> Hello Pawan,
>
> We were getting a hard time dealing with large datasets in our client
> project. We were streaming data from MySQL using the FetchSize and
> EntityListIterator, which helped us up to some point, but ultimately
> struggled with the further increase in data. This is where the batch
> processing implementation came to rescue. We incorporated it into our
> project and were able to process the data with ease. This implementation
> seems to be quite scalable and faster in performance as compared to
> streaming. Batch processing was the need of the hour and there we have it
> in OFBiz. Thanks, Pawan for your valuable contribution.
>
> On Wed, Jul 1, 2020 at 4:06 PM Pawan Verma <pa...@hotwaxsystems.com>
> wrote:
>
> > Hi Chandan, Jacques
> >
> > Thanks, for your feedback.
> >
> > Yes, To solve the problem of heavy entity operations which consumes all
> the
> > system memory, we have implemented EntityBatchIterator. Originally
> designed
> > for the heavy entity operations.
> > --
> > Thanks & Regards
> > Pawan Verma
> > Technical Consultant
> > *HotWax Systems*
> > *Enterprise open source experts*
> > http://www.hotwaxsystems.com
> >
> >
> > On Sun, Jun 28, 2020 at 10:16 PM Jacques Le Roux <
> > jacques.le.roux@les7arts.com> wrote:
> >
> > > Hi,
> > >
> > > I have not looked into any details but Chandan's advice sounds like a
> > wise
> > > one to me
> > >
> > > Jacques
> > >
> > > Le 27/06/2020 à 13:43, Chandan Khandelwal a écrit :
> > > > Hello Pawan,
> > > >
> > > > Approach looks good, my only suggestion is to use batch processing
> only
> > > > when we are dealing with large data set, as this method takes a
> longer
> > > time
> > > > compared to the normal method specially on a distributed environment,
> > > which
> > > > may negatively impact the performance.
> > > >
> > > > Kind Regards,
> > > > Chandan Khandelwal
> > > > Senior Manager, Enterprise Software Development
> > > >
> > > > *HotWax Systems*
> > > > *Enterprise open source experts*
> > > > cell: +91-98934-81076
> > > > office: 0731-409-3684
> > > > http://www.hotwaxsystems.com
> > > >
> > > >
> > > > On Fri, Jun 5, 2020 at 4:07 PM Pawan Verma <
> > > pawan.verma@hotwaxsystems.com>
> > > > wrote:
> > > >
> > > >> Thanks, Pritam and Scott for the discussion.
> > > >>
> > > >> I've created Jira OFBIZ-11789 for this improvement and also created
> a
> > PR
> > > >> with the proposed changes.
> > > >>
> > > >> I request everyone to review the PR and suggest your thought on
> this.
> > > >> Thanks!
> > > >> --
> > > >> Thanks & Regards
> > > >> Pawan Verma
> > > >> Technical Consultant
> > > >> *HotWax Systems*
> > > >> *Enterprise open source experts*
> > > >> http://www.hotwaxsystems.com
> > > >>
> > > >>
> > > >> On Mon, Jun 1, 2020 at 12:36 PM Pritam Kute <
> > > pritam.kute@hotwaxsystems.com
> > > >> wrote:
> > > >>
> > > >>> Thanks Scott for your detailed explanation.
> > > >>>
> > > >>> The solution looks good to me too. My confusion was with why we are
> > > going
> > > >>> to implement new method if we can achieve that using the current
> > > >>> EntityQuery methods.
> > > >>>
> > > >>> +1 for adding queryBatchIterator() to EntityQuery.
> > > >>>
> > > >>> Kind Regards,
> > > >>> --
> > > >>> Pritam Kute
> > > >>>
> > > >>>
> > > >>> On Thu, May 28, 2020 at 6:32 AM Scott Gray <
> > > scott.gray@hotwaxsystems.com
> > > >>>
> > > >>> wrote:
> > > >>>
> > > >>>> Hi Pritam,
> > > >>>>
> > > >>>> I'm not sure about PostgreSQL or Derby but I know with MySQL that
> > > >> using a
> > > >>>> cursor doesn't really work. You have to set the result set to
> > > >>>> TYPE_FORWARD_ONLY and CONCUR_READ_ONLY and also set the fetch size
> > to
> > > >>>> INTEGER.MIN_VALUE. Only then will the driver stream the results
> and
> > > >> even
> > > >>>> then, you may not execute any other SQL commands on the connection
> > > >> until
> > > >>>> you have fully read or closed the resultset.
> > > >>>>
> > > >>>> So if an EntityListIterator doesn't really conserve memory, then
> you
> > > >> need
> > > >>>> to take a paging query approach such as this:
> > > >>>> EntityQuery query =
> > > >>> EntityQuery.use(delegator).from("SomeTable").limit(100)
> > > >>>> List results = null
> > > >>>> while (!(results = query.queryList()).isEmpty()) {
> > > >>>> for (value : results) {
> > > >>>> // do something with each value
> > > >>>> }
> > > >>>> query.offset(query.getOffset() + query.getLimit())
> > > >>>> }
> > > >>>>
> > > >>>> Or with the proposed EntityBatchIterator:
> > > >>>> Iterator query =
> > > >>>>
> > > >>>>
> > > >>
> > >
> >
> EntityQuery.use(delegator).from("SomeTable").limit(100).queryBatchIterator()
> > > >>>> while (iterator.hasNext()) {
> > > >>>> result = iterator.next()
> > > >>>> // do something with each value
> > > >>>> }
> > > >>>>
> > > >>>> I guess an alternative approach would be to implement something
> > > similar
> > > >>>> within the EntityListIterator and perhaps a flag to turn it off or
> > on
> > > >>>> depending on which database is being used and how well it supports
> > > >>>> iterating over results without loading the entire resultset into
> > > >> memory.
> > > >>>> Regards
> > > >>>> Scott
> > > >>>>
> > > >>>>
> > > >>>>
> > > >>>> On Sat, 23 May 2020 at 20:59, Pritam Kute <
> > > >> pritam.kute@hotwaxsystems.com
> > > >>>> wrote:
> > > >>>>
> > > >>>>> Hello Pawan,
> > > >>>>>
> > > >>>>> I just had a look into the EntityQuery.queryIterator() method and
> > > >> looks
> > > >>>>> like we can achieve that by using fetchSize(), fowardOnly(),
> > > >>>>> cursorScrollInsensitive(), cursorScrollSensitive() and offset()
> > > >> methods
> > > >>>> in
> > > >>>>> EntityQuery class. Let me know if I am missing anything.
> > > >>>>>
> > > >>>>> It will be good if you can post a pseudo code or something here
> so
> > > >> that
> > > >>>> we
> > > >>>>> could get an understanding of the exact design which you have in
> > your
> > > >>>> mind.
> > > >>>>> Kind Regards,
> > > >>>>> --
> > > >>>>> Pritam Kute
> > > >>>>>
> > > >>>>>
> > > >>>>> On Thu, May 21, 2020 at 7:41 PM Pawan Verma <
> > > >>>> pawan.verma@hotwaxsystems.com
> > > >>>>> wrote:
> > > >>>>>
> > > >>>>>> Hello Devs,
> > > >>>>>>
> > > >>>>>> While working on the large database we have figured out that
> very
> > > >>> large
> > > >>>>>> queries consume all memory and crash ofbiz(because
> queryIterator()
> > > >>>>> doesn't
> > > >>>>>> really work, it's no different from queryList())
> > > >>>>>>
> > > >>>>>> The EntityListIterator attempts to use a cursor to iterate over
> > > >> large
> > > >>>>>> result sets but in reality most databases do not give us a
> cursor
> > > >>>> unless
> > > >>>>> we
> > > >>>>>> ask for it in a very specific way, and instead you get back the
> > > >> full
> > > >>>>> result
> > > >>>>>> set and potentially consume a large amount of memory. For
> > example,
> > > >>> the
> > > >>>>>> MySql details are here (ResultSet section):
> > > >>>>>>
> > > >>>>>>
> > > >>
> > >
> >
> https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html
> > > >>>>>> To work around this we've built the EntityBatchIterator which
> > > >> instead
> > > >>>>>> basically just pages through results like you would do on a
> > webpage
> > > >>>> that
> > > >>>>>> uses pagination. The iterator takes an EntityQuery and when
> > next()
> > > >>> is
> > > >>>>>> called it grabs the first 500 records and returns the first
> > record,
> > > >>>> call
> > > >>>>>> next again and you get the second record, once you've called
> next
> > > >> 500
> > > >>>>> times
> > > >>>>>> it runs the query again with an offset and grabs the next 500
> and
> > > >> so
> > > >>> on
> > > >>>>>> until there are no more records.
> > > >>>>>>
> > > >>>>>> The main downsides to this approach are:
> > > >>>>>> 1. Same as when using limit/offset you want to be sure your
> > results
> > > >>>> will
> > > >>>>>> come back in a consistent order to avoid accidentally skipping
> > some
> > > >>>> rows
> > > >>>>>> and seeing other rows twice.
> > > >>>>>> 2. Because the results are a combination of many queries rather
> > > >> than
> > > >>> a
> > > >>>>>> single query, some of the data may change while you are paging
> > > >>> through
> > > >>>>> it.
> > > >>>>>> i.e. if you were to sort by lastUpdatedTxStamp you may see some
> > > >> rows
> > > >>>>> twice
> > > >>>>>> as they are updated by other transactions (this might be a good
> > > >> thing
> > > >>>> in
> > > >>>>>> some cases).
> > > >>>>>>
> > > >>>>>> So, the main proposal is to introduce a new EntityBatchIterator
> > for
> > > >>>> large
> > > >>>>>> queries. If we agree on the proposal I'll create a Jira and
> > > >> provide
> > > >>> a
> > > >>>>>> patch for the community review. Thanks!
> > > >>>>>>
> > > >>>>>> --
> > > >>>>>> Thanks & Regards
> > > >>>>>> Pawan Verma
> > > >>>>>> Technical Consultant
> > > >>>>>> *HotWax Systems*
> > > >>>>>> *Enterprise open source experts*
> > > >>>>>> http://www.hotwaxsystems.com
> > > >>>>>>
> > >
> >
>
>
> --
> Kind Regards,
> Prakhar Kumar
> Sr. Enterprise Software Engineer
>
> *HotWax Systems*
> *Enterprise open source experts*
> cell: +91-89628-81820
> office: 0731-409-3684
> http://www.hotwaxsystems.com
>
Re: EntityBatchIterator for large data set queries
Posted by Jacques Le Roux <ja...@les7arts.com>.
Thanks Prakhar for this information.
Jacques
Le 20/07/2020 à 10:12, Prakhar Kumar a écrit :
> Hello Pawan,
>
> We were getting a hard time dealing with large datasets in our client
> project. We were streaming data from MySQL using the FetchSize and
> EntityListIterator, which helped us up to some point, but ultimately
> struggled with the further increase in data. This is where the batch
> processing implementation came to rescue. We incorporated it into our
> project and were able to process the data with ease. This implementation
> seems to be quite scalable and faster in performance as compared to
> streaming. Batch processing was the need of the hour and there we have it
> in OFBiz. Thanks, Pawan for your valuable contribution.
>
> On Wed, Jul 1, 2020 at 4:06 PM Pawan Verma <pa...@hotwaxsystems.com>
> wrote:
>
>> Hi Chandan, Jacques
>>
>> Thanks, for your feedback.
>>
>> Yes, To solve the problem of heavy entity operations which consumes all the
>> system memory, we have implemented EntityBatchIterator. Originally designed
>> for the heavy entity operations.
>> --
>> Thanks & Regards
>> Pawan Verma
>> Technical Consultant
>> *HotWax Systems*
>> *Enterprise open source experts*
>> http://www.hotwaxsystems.com
>>
>>
>> On Sun, Jun 28, 2020 at 10:16 PM Jacques Le Roux <
>> jacques.le.roux@les7arts.com> wrote:
>>
>>> Hi,
>>>
>>> I have not looked into any details but Chandan's advice sounds like a
>> wise
>>> one to me
>>>
>>> Jacques
>>>
>>> Le 27/06/2020 à 13:43, Chandan Khandelwal a écrit :
>>>> Hello Pawan,
>>>>
>>>> Approach looks good, my only suggestion is to use batch processing only
>>>> when we are dealing with large data set, as this method takes a longer
>>> time
>>>> compared to the normal method specially on a distributed environment,
>>> which
>>>> may negatively impact the performance.
>>>>
>>>> Kind Regards,
>>>> Chandan Khandelwal
>>>> Senior Manager, Enterprise Software Development
>>>>
>>>> *HotWax Systems*
>>>> *Enterprise open source experts*
>>>> cell: +91-98934-81076
>>>> office: 0731-409-3684
>>>> http://www.hotwaxsystems.com
>>>>
>>>>
>>>> On Fri, Jun 5, 2020 at 4:07 PM Pawan Verma <
>>> pawan.verma@hotwaxsystems.com>
>>>> wrote:
>>>>
>>>>> Thanks, Pritam and Scott for the discussion.
>>>>>
>>>>> I've created Jira OFBIZ-11789 for this improvement and also created a
>> PR
>>>>> with the proposed changes.
>>>>>
>>>>> I request everyone to review the PR and suggest your thought on this.
>>>>> Thanks!
>>>>> --
>>>>> Thanks & Regards
>>>>> Pawan Verma
>>>>> Technical Consultant
>>>>> *HotWax Systems*
>>>>> *Enterprise open source experts*
>>>>> http://www.hotwaxsystems.com
>>>>>
>>>>>
>>>>> On Mon, Jun 1, 2020 at 12:36 PM Pritam Kute <
>>> pritam.kute@hotwaxsystems.com
>>>>> wrote:
>>>>>
>>>>>> Thanks Scott for your detailed explanation.
>>>>>>
>>>>>> The solution looks good to me too. My confusion was with why we are
>>> going
>>>>>> to implement new method if we can achieve that using the current
>>>>>> EntityQuery methods.
>>>>>>
>>>>>> +1 for adding queryBatchIterator() to EntityQuery.
>>>>>>
>>>>>> Kind Regards,
>>>>>> --
>>>>>> Pritam Kute
>>>>>>
>>>>>>
>>>>>> On Thu, May 28, 2020 at 6:32 AM Scott Gray <
>>> scott.gray@hotwaxsystems.com
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Pritam,
>>>>>>>
>>>>>>> I'm not sure about PostgreSQL or Derby but I know with MySQL that
>>>>> using a
>>>>>>> cursor doesn't really work. You have to set the result set to
>>>>>>> TYPE_FORWARD_ONLY and CONCUR_READ_ONLY and also set the fetch size
>> to
>>>>>>> INTEGER.MIN_VALUE. Only then will the driver stream the results and
>>>>> even
>>>>>>> then, you may not execute any other SQL commands on the connection
>>>>> until
>>>>>>> you have fully read or closed the resultset.
>>>>>>>
>>>>>>> So if an EntityListIterator doesn't really conserve memory, then you
>>>>> need
>>>>>>> to take a paging query approach such as this:
>>>>>>> EntityQuery query =
>>>>>> EntityQuery.use(delegator).from("SomeTable").limit(100)
>>>>>>> List results = null
>>>>>>> while (!(results = query.queryList()).isEmpty()) {
>>>>>>> for (value : results) {
>>>>>>> // do something with each value
>>>>>>> }
>>>>>>> query.offset(query.getOffset() + query.getLimit())
>>>>>>> }
>>>>>>>
>>>>>>> Or with the proposed EntityBatchIterator:
>>>>>>> Iterator query =
>>>>>>>
>>>>>>>
>> EntityQuery.use(delegator).from("SomeTable").limit(100).queryBatchIterator()
>>>>>>> while (iterator.hasNext()) {
>>>>>>> result = iterator.next()
>>>>>>> // do something with each value
>>>>>>> }
>>>>>>>
>>>>>>> I guess an alternative approach would be to implement something
>>> similar
>>>>>>> within the EntityListIterator and perhaps a flag to turn it off or
>> on
>>>>>>> depending on which database is being used and how well it supports
>>>>>>> iterating over results without loading the entire resultset into
>>>>> memory.
>>>>>>> Regards
>>>>>>> Scott
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Sat, 23 May 2020 at 20:59, Pritam Kute <
>>>>> pritam.kute@hotwaxsystems.com
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hello Pawan,
>>>>>>>>
>>>>>>>> I just had a look into the EntityQuery.queryIterator() method and
>>>>> looks
>>>>>>>> like we can achieve that by using fetchSize(), fowardOnly(),
>>>>>>>> cursorScrollInsensitive(), cursorScrollSensitive() and offset()
>>>>> methods
>>>>>>> in
>>>>>>>> EntityQuery class. Let me know if I am missing anything.
>>>>>>>>
>>>>>>>> It will be good if you can post a pseudo code or something here so
>>>>> that
>>>>>>> we
>>>>>>>> could get an understanding of the exact design which you have in
>> your
>>>>>>> mind.
>>>>>>>> Kind Regards,
>>>>>>>> --
>>>>>>>> Pritam Kute
>>>>>>>>
>>>>>>>>
>>>>>>>> On Thu, May 21, 2020 at 7:41 PM Pawan Verma <
>>>>>>> pawan.verma@hotwaxsystems.com
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hello Devs,
>>>>>>>>>
>>>>>>>>> While working on the large database we have figured out that very
>>>>>> large
>>>>>>>>> queries consume all memory and crash ofbiz(because queryIterator()
>>>>>>>> doesn't
>>>>>>>>> really work, it's no different from queryList())
>>>>>>>>>
>>>>>>>>> The EntityListIterator attempts to use a cursor to iterate over
>>>>> large
>>>>>>>>> result sets but in reality most databases do not give us a cursor
>>>>>>> unless
>>>>>>>> we
>>>>>>>>> ask for it in a very specific way, and instead you get back the
>>>>> full
>>>>>>>> result
>>>>>>>>> set and potentially consume a large amount of memory. For
>> example,
>>>>>> the
>>>>>>>>> MySql details are here (ResultSet section):
>>>>>>>>>
>>>>>>>>>
>> https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html
>>>>>>>>> To work around this we've built the EntityBatchIterator which
>>>>> instead
>>>>>>>>> basically just pages through results like you would do on a
>> webpage
>>>>>>> that
>>>>>>>>> uses pagination. The iterator takes an EntityQuery and when
>> next()
>>>>>> is
>>>>>>>>> called it grabs the first 500 records and returns the first
>> record,
>>>>>>> call
>>>>>>>>> next again and you get the second record, once you've called next
>>>>> 500
>>>>>>>> times
>>>>>>>>> it runs the query again with an offset and grabs the next 500 and
>>>>> so
>>>>>> on
>>>>>>>>> until there are no more records.
>>>>>>>>>
>>>>>>>>> The main downsides to this approach are:
>>>>>>>>> 1. Same as when using limit/offset you want to be sure your
>> results
>>>>>>> will
>>>>>>>>> come back in a consistent order to avoid accidentally skipping
>> some
>>>>>>> rows
>>>>>>>>> and seeing other rows twice.
>>>>>>>>> 2. Because the results are a combination of many queries rather
>>>>> than
>>>>>> a
>>>>>>>>> single query, some of the data may change while you are paging
>>>>>> through
>>>>>>>> it.
>>>>>>>>> i.e. if you were to sort by lastUpdatedTxStamp you may see some
>>>>> rows
>>>>>>>> twice
>>>>>>>>> as they are updated by other transactions (this might be a good
>>>>> thing
>>>>>>> in
>>>>>>>>> some cases).
>>>>>>>>>
>>>>>>>>> So, the main proposal is to introduce a new EntityBatchIterator
>> for
>>>>>>> large
>>>>>>>>> queries. If we agree on the proposal I'll create a Jira and
>>>>> provide
>>>>>> a
>>>>>>>>> patch for the community review. Thanks!
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks & Regards
>>>>>>>>> Pawan Verma
>>>>>>>>> Technical Consultant
>>>>>>>>> *HotWax Systems*
>>>>>>>>> *Enterprise open source experts*
>>>>>>>>> http://www.hotwaxsystems.com
>>>>>>>>>
>
Re: EntityBatchIterator for large data set queries
Posted by Prakhar Kumar <pr...@hotwaxsystems.com>.
Hello Pawan,
We were getting a hard time dealing with large datasets in our client
project. We were streaming data from MySQL using the FetchSize and
EntityListIterator, which helped us up to some point, but ultimately
struggled with the further increase in data. This is where the batch
processing implementation came to rescue. We incorporated it into our
project and were able to process the data with ease. This implementation
seems to be quite scalable and faster in performance as compared to
streaming. Batch processing was the need of the hour and there we have it
in OFBiz. Thanks, Pawan for your valuable contribution.
On Wed, Jul 1, 2020 at 4:06 PM Pawan Verma <pa...@hotwaxsystems.com>
wrote:
> Hi Chandan, Jacques
>
> Thanks, for your feedback.
>
> Yes, To solve the problem of heavy entity operations which consumes all the
> system memory, we have implemented EntityBatchIterator. Originally designed
> for the heavy entity operations.
> --
> Thanks & Regards
> Pawan Verma
> Technical Consultant
> *HotWax Systems*
> *Enterprise open source experts*
> http://www.hotwaxsystems.com
>
>
> On Sun, Jun 28, 2020 at 10:16 PM Jacques Le Roux <
> jacques.le.roux@les7arts.com> wrote:
>
> > Hi,
> >
> > I have not looked into any details but Chandan's advice sounds like a
> wise
> > one to me
> >
> > Jacques
> >
> > Le 27/06/2020 à 13:43, Chandan Khandelwal a écrit :
> > > Hello Pawan,
> > >
> > > Approach looks good, my only suggestion is to use batch processing only
> > > when we are dealing with large data set, as this method takes a longer
> > time
> > > compared to the normal method specially on a distributed environment,
> > which
> > > may negatively impact the performance.
> > >
> > > Kind Regards,
> > > Chandan Khandelwal
> > > Senior Manager, Enterprise Software Development
> > >
> > > *HotWax Systems*
> > > *Enterprise open source experts*
> > > cell: +91-98934-81076
> > > office: 0731-409-3684
> > > http://www.hotwaxsystems.com
> > >
> > >
> > > On Fri, Jun 5, 2020 at 4:07 PM Pawan Verma <
> > pawan.verma@hotwaxsystems.com>
> > > wrote:
> > >
> > >> Thanks, Pritam and Scott for the discussion.
> > >>
> > >> I've created Jira OFBIZ-11789 for this improvement and also created a
> PR
> > >> with the proposed changes.
> > >>
> > >> I request everyone to review the PR and suggest your thought on this.
> > >> Thanks!
> > >> --
> > >> Thanks & Regards
> > >> Pawan Verma
> > >> Technical Consultant
> > >> *HotWax Systems*
> > >> *Enterprise open source experts*
> > >> http://www.hotwaxsystems.com
> > >>
> > >>
> > >> On Mon, Jun 1, 2020 at 12:36 PM Pritam Kute <
> > pritam.kute@hotwaxsystems.com
> > >> wrote:
> > >>
> > >>> Thanks Scott for your detailed explanation.
> > >>>
> > >>> The solution looks good to me too. My confusion was with why we are
> > going
> > >>> to implement new method if we can achieve that using the current
> > >>> EntityQuery methods.
> > >>>
> > >>> +1 for adding queryBatchIterator() to EntityQuery.
> > >>>
> > >>> Kind Regards,
> > >>> --
> > >>> Pritam Kute
> > >>>
> > >>>
> > >>> On Thu, May 28, 2020 at 6:32 AM Scott Gray <
> > scott.gray@hotwaxsystems.com
> > >>>
> > >>> wrote:
> > >>>
> > >>>> Hi Pritam,
> > >>>>
> > >>>> I'm not sure about PostgreSQL or Derby but I know with MySQL that
> > >> using a
> > >>>> cursor doesn't really work. You have to set the result set to
> > >>>> TYPE_FORWARD_ONLY and CONCUR_READ_ONLY and also set the fetch size
> to
> > >>>> INTEGER.MIN_VALUE. Only then will the driver stream the results and
> > >> even
> > >>>> then, you may not execute any other SQL commands on the connection
> > >> until
> > >>>> you have fully read or closed the resultset.
> > >>>>
> > >>>> So if an EntityListIterator doesn't really conserve memory, then you
> > >> need
> > >>>> to take a paging query approach such as this:
> > >>>> EntityQuery query =
> > >>> EntityQuery.use(delegator).from("SomeTable").limit(100)
> > >>>> List results = null
> > >>>> while (!(results = query.queryList()).isEmpty()) {
> > >>>> for (value : results) {
> > >>>> // do something with each value
> > >>>> }
> > >>>> query.offset(query.getOffset() + query.getLimit())
> > >>>> }
> > >>>>
> > >>>> Or with the proposed EntityBatchIterator:
> > >>>> Iterator query =
> > >>>>
> > >>>>
> > >>
> >
> EntityQuery.use(delegator).from("SomeTable").limit(100).queryBatchIterator()
> > >>>> while (iterator.hasNext()) {
> > >>>> result = iterator.next()
> > >>>> // do something with each value
> > >>>> }
> > >>>>
> > >>>> I guess an alternative approach would be to implement something
> > similar
> > >>>> within the EntityListIterator and perhaps a flag to turn it off or
> on
> > >>>> depending on which database is being used and how well it supports
> > >>>> iterating over results without loading the entire resultset into
> > >> memory.
> > >>>> Regards
> > >>>> Scott
> > >>>>
> > >>>>
> > >>>>
> > >>>> On Sat, 23 May 2020 at 20:59, Pritam Kute <
> > >> pritam.kute@hotwaxsystems.com
> > >>>> wrote:
> > >>>>
> > >>>>> Hello Pawan,
> > >>>>>
> > >>>>> I just had a look into the EntityQuery.queryIterator() method and
> > >> looks
> > >>>>> like we can achieve that by using fetchSize(), fowardOnly(),
> > >>>>> cursorScrollInsensitive(), cursorScrollSensitive() and offset()
> > >> methods
> > >>>> in
> > >>>>> EntityQuery class. Let me know if I am missing anything.
> > >>>>>
> > >>>>> It will be good if you can post a pseudo code or something here so
> > >> that
> > >>>> we
> > >>>>> could get an understanding of the exact design which you have in
> your
> > >>>> mind.
> > >>>>> Kind Regards,
> > >>>>> --
> > >>>>> Pritam Kute
> > >>>>>
> > >>>>>
> > >>>>> On Thu, May 21, 2020 at 7:41 PM Pawan Verma <
> > >>>> pawan.verma@hotwaxsystems.com
> > >>>>> wrote:
> > >>>>>
> > >>>>>> Hello Devs,
> > >>>>>>
> > >>>>>> While working on the large database we have figured out that very
> > >>> large
> > >>>>>> queries consume all memory and crash ofbiz(because queryIterator()
> > >>>>> doesn't
> > >>>>>> really work, it's no different from queryList())
> > >>>>>>
> > >>>>>> The EntityListIterator attempts to use a cursor to iterate over
> > >> large
> > >>>>>> result sets but in reality most databases do not give us a cursor
> > >>>> unless
> > >>>>> we
> > >>>>>> ask for it in a very specific way, and instead you get back the
> > >> full
> > >>>>> result
> > >>>>>> set and potentially consume a large amount of memory. For
> example,
> > >>> the
> > >>>>>> MySql details are here (ResultSet section):
> > >>>>>>
> > >>>>>>
> > >>
> >
> https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html
> > >>>>>> To work around this we've built the EntityBatchIterator which
> > >> instead
> > >>>>>> basically just pages through results like you would do on a
> webpage
> > >>>> that
> > >>>>>> uses pagination. The iterator takes an EntityQuery and when
> next()
> > >>> is
> > >>>>>> called it grabs the first 500 records and returns the first
> record,
> > >>>> call
> > >>>>>> next again and you get the second record, once you've called next
> > >> 500
> > >>>>> times
> > >>>>>> it runs the query again with an offset and grabs the next 500 and
> > >> so
> > >>> on
> > >>>>>> until there are no more records.
> > >>>>>>
> > >>>>>> The main downsides to this approach are:
> > >>>>>> 1. Same as when using limit/offset you want to be sure your
> results
> > >>>> will
> > >>>>>> come back in a consistent order to avoid accidentally skipping
> some
> > >>>> rows
> > >>>>>> and seeing other rows twice.
> > >>>>>> 2. Because the results are a combination of many queries rather
> > >> than
> > >>> a
> > >>>>>> single query, some of the data may change while you are paging
> > >>> through
> > >>>>> it.
> > >>>>>> i.e. if you were to sort by lastUpdatedTxStamp you may see some
> > >> rows
> > >>>>> twice
> > >>>>>> as they are updated by other transactions (this might be a good
> > >> thing
> > >>>> in
> > >>>>>> some cases).
> > >>>>>>
> > >>>>>> So, the main proposal is to introduce a new EntityBatchIterator
> for
> > >>>> large
> > >>>>>> queries. If we agree on the proposal I'll create a Jira and
> > >> provide
> > >>> a
> > >>>>>> patch for the community review. Thanks!
> > >>>>>>
> > >>>>>> --
> > >>>>>> Thanks & Regards
> > >>>>>> Pawan Verma
> > >>>>>> Technical Consultant
> > >>>>>> *HotWax Systems*
> > >>>>>> *Enterprise open source experts*
> > >>>>>> http://www.hotwaxsystems.com
> > >>>>>>
> >
>
--
Kind Regards,
Prakhar Kumar
Sr. Enterprise Software Engineer
*HotWax Systems*
*Enterprise open source experts*
cell: +91-89628-81820
office: 0731-409-3684
http://www.hotwaxsystems.com