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 <pawan.ve...@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.r...@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.ve...@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.k...@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.g...@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.k...@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.ve...@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
