Re: [eclipselink-dev] MemoryLeak by CursoredStream??

[Christopher Delahunt <christopher.delahunt@xxxxxxxxxx>]

The MySQLPlatform class has support for LIMIT which is used for pagination. I don't know how this fits with your use case, if the generated SQL is just missing the OFFSET statement or not; if it is, you can write your own MySQLPlatform subclass and override the printSQLSelectStatement method to behave appropriately, using the one from the base MySQLPlatform class as the template.  If you go this route, please file an enhancement request showing what you've done so it can be used by others.

If you are reading into an EntityManager, through cursor or any query object, everything read in will be kept in memory; the EntityManager is required to track changes to all objects it manages, and so must have links to them. To release these links, you will need to call em.clear().   Generally, applications will process a number of records and then call em.flush(); em.clear(); when appropriate and continue with the transaction.

Best Regards,

Chris

On Apr 4, 2019, at 1:12 AM, 南 温夫 <minamia@xxxxxxxxxxxxxxxxx> wrote:

Hello,

I use eclipselink and it's very nice. Our backend DB is MSSQL. I have to run pagination to millions of records. It seems eclipselink doesn't support setFirstResult/setMaxRows Pagination about MSSQL.
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.eclipse.org_EclipseLink_Examples_JPA_Pagination&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=YgvTjlOUpgVRIsshc_l5SSHl_vyqEDoN074cZcsMErk&e=
Using above method, performance wasn't good because MSSQL pagination query(FETCH OFFSET) isn't used.

So, I tried to use CursoredStream as below.
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.eclipse.org_Using-5FAdvanced-5FQuery-5FAPI-5F-28ELUG-29-23Handling-5FCursor-5Fand-5FStream-5FQuery-5FResults&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=tObV-_v6NvZSX7W9NSwH6UqL2PpgT-G4PTrA1cYcZzA&e=
The problem is even if cursor.clear() is called, all record objects remain in UnitOfWork object as DescriptorEvent. I have to update records so "READ_ONLY" query hint can't be used.
===============================================
int max = 1000;
query.setHint(QueryHints.CURSOR, HintValues.TRUE)
.setHint(QueryHints.JDBC_FETCH_SIZE, max);
CursoredStream cursor = (CursoredStream)query.getSingleResult();
while (cursor.hasNext()) {
list = cursor.next(max);
for (Object recordObj : list) {
RecordEntity record = (RecordEntity)recordObj;
doSomething(record)
record.setSomeValue(someValue); // the record has to be updated
entityManager.merge(record);
}
cursor.clear(); //cursor.releasePrevious() doesn't work as well
}
cursor.close();
===============================================
Is it possible paging records by CursoredStream without getting all records in memory? If anyone could tell me, it's really helpful.

Thank you,
Atsuo
_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
To change your delivery options, retrieve your password, or unsubscribe from this list, visit
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.eclipse.org_mailman_listinfo_eclipselink-2Ddev&d=DwICAg&c=RoP1YumCXCgaWHvlZYR8PZh8Bv7qIrMUB65eapI_JnE&r=aeYmvFwRsCWfafng1rj-AcoD-cr8mkJQBEoVD5OszU0&m=H21Cpo0Xb8YWaFTSbpQKftGrjbPGO1lcCEbVN23JJic&s=qkckS1sgK6lh2wPJzCkJuWLO_MAJSb_jyCi8k_IFp_k&e=