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
> >
>
