[jira] [Closed] (OFBIZ-11789) Implement EntityBatchIterator for large data set queries

["Pawan Verma (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OFBIZ-11789?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Pawan Verma closed OFBIZ-11789.
-------------------------------
    Fix Version/s: Upcoming Branch
       Resolution: Implemented

> Implement EntityBatchIterator for large data set queries
> --------------------------------------------------------
>
>                 Key: OFBIZ-11789
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-11789
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: framework/entity
>    Affects Versions: Trunk
>            Reporter: Pawan Verma
>            Assignee: Pawan Verma
>            Priority: Minor
>             Fix For: Upcoming Branch
>
>
> Dev Mail Thread: [https://markmail.org/search/EntityBatchIterator+for+large+data+set+queries]
> 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).



--
This message was sent by Atlassian Jira
(v8.3.4#803005)