Fetching and processing a large amount of objects
Hi list, my task is to fetch and process a large amount of objects. Reading the documentation my best choice seemed to do a dataContext.performIteratedQuery(query). The problem is that I get a Java Heap Space error, on executing this statement. Now it seems to me the problem is that performIteratedQuery loads all data into a ResultSet. My question is how to handle such cases, is there any best practice? Would it be ok to use a paginated query. Then unregistering processed objects from the DataContext they belong to and also removing them from the array that resulted from the paginated query. Regards Simon
Re: Fetching and processing a large amount of objects
Hi Simon, some questions: 1) How many records are you talking about? 2) Are you updating your object with a flag/etc you can query on again later (to exclude objects you've already processed)? 3) What version of Cayenne are you using and what database? 4) When you convert your Map (from the iterated query) into a DataObject, are you creating a new DataContext or using the old one over and over again? For #4, if you are using the same DataContext repeatedly, try changing your logic to something more like: while (iterator.hasNextRow()) { DataContext context = DataContext.createDataContext(); Map row = (Map) iterator.nextRow(); CayenneObject object = (CayenneObject) context.objectFromDataRow(CayenneObject, row); ... object.doStuff(); ... context.commitChanges(); } This way you won't build up a ton of objects in a single DataContext and possibly run out of memory. mrg On Wed, Dec 12, 2012 at 8:18 AM, Simon Schneider sschnei...@mackoy.de wrote: Hi list, my task is to fetch and process a large amount of objects. Reading the documentation my best choice seemed to do a dataContext.performIteratedQuery(query). The problem is that I get a Java Heap Space error, on executing this statement. Now it seems to me the problem is that performIteratedQuery loads all data into a ResultSet. My question is how to handle such cases, is there any best practice? Would it be ok to use a paginated query. Then unregistering processed objects from the DataContext they belong to and also removing them from the array that resulted from the paginated query. Regards Simon
Re: Problem in fetching the data from multiple tables with relationships.
I'm not really sure what your question is. Cayenne will fetch all of the fields that are modeled and turn them into objects. And you can add prefetching paths to your SelectQuery to pre-fetch the related objects, otherwise they will be loaded when accessed. If you want to write your own SQL and get a HashMap of the data you can use SQLTemplate instead of SelectQuery. John
Re: Fetching and processing a large amount of objects
On 13/12/12 12:18am, Simon Schneider wrote: Hi list, my task is to fetch and process a large amount of objects. Reading the documentation my best choice seemed to do a dataContext.performIteratedQuery(query). The problem is that I get a Java Heap Space error, on executing this statement. Now it seems to me the problem is that performIteratedQuery loads all data into a ResultSet. My question is how to handle such cases, is there any best practice? Would it be ok to use a paginated query. Then unregistering processed objects from the DataContext they belong to and also removing them from the array that resulted from the paginated query. By the time you step through to the end of the results, even with a paginated query you will have read every object into RAM. However paginated queries are a really convenient way to fetch even millions of rows where you don't necessarily want to access every object (eg. when you want to display a scrollable list of results and you only need to resolve the objects you can see in the current viewport). As Michael says, an option is to read in the data in batches into different Contexts. Ari -- -- Aristedes Maniatis GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
RE: Problem in fetching the data from multiple tables with relationships.
Hi John, Thanks for the reply. For the below query, can you provide a sample using SQLTemplate? 1. how to use the SQLTemplate to execute the below query? SELECT um.ID, um.FIRST_NAME, um.LAST_NAME, ua.ROLE_ID, tt.TASK_ASSIGNED_DATE, tt.TASK_CLOSED_DATE, tt.TASK_MASTER_ID FROM t_user_master um, t_user_allocation_details ua, t_task_assignment_detail ta, t_task_transaction_detail tt WHERE um.ID = ua.USER_ID AND ta.USER_ID = um.ID AND ta.TASK_TRANSACTION_ID = tt.ID AND um.ID = 112; 2. how to read the data from the result set or List? Appreciate your help. Thanks, Sampath Uppula -Original Message- From: John Huss [mailto:johnth...@gmail.com] Sent: Thursday, December 13, 2012 2:50 AM To: user@cayenne.apache.org Subject: Re: Problem in fetching the data from multiple tables with relationships. I'm not really sure what your question is. Cayenne will fetch all of the fields that are modeled and turn them into objects. And you can add prefetching paths to your SelectQuery to pre-fetch the related objects, otherwise they will be loaded when accessed. If you want to write your own SQL and get a HashMap of the data you can use SQLTemplate instead of SelectQuery. John Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at a href=http://www.techmahindra.com/Disclaimer.html;http://www.techmahindra.com/Disclaimer.html/a externally and a href=http://tim.techmahindra.com/tim/disclaimer.html;http://tim.techmahindra.com/tim/disclaimer.html/a internally within Tech Mahindra.
Re: Problem in fetching the data from multiple tables with relationships.
If you really want to use Cayenne for the power it gives you, rather than writing SQL, I recommend you work your way through the tutorials. Yes, you can use SQLTemplate. No, that's probably not what you want to do here. At its heart, your query is just um.ID = 112. Start by making that work in Cayenne using a simple select query. Just fetch one um object. And then follow the relations from that object to the other objects and properties you need. resultUM.getUA().getRole() And remember, that rarely in Cayenne will you want to fetch or query on primary keys. Yes, it will sometimes be needed, but mostly you let Cayenne handle that for you behind the scenes. Ari On 13/12/12 6:14pm, Sampath Uppula wrote: Hi John, Thanks for the reply. For the below query, can you provide a sample using SQLTemplate? 1. how to use the SQLTemplate to execute the below query? SELECT um.ID, um.FIRST_NAME, um.LAST_NAME, ua.ROLE_ID, tt.TASK_ASSIGNED_DATE, tt.TASK_CLOSED_DATE, tt.TASK_MASTER_ID FROM t_user_master um, t_user_allocation_details ua, t_task_assignment_detail ta, t_task_transaction_detail tt WHERE um.ID = ua.USER_ID AND ta.USER_ID = um.ID AND ta.TASK_TRANSACTION_ID = tt.ID AND um.ID = 112; 2. how to read the data from the result set or List? Appreciate your help. Thanks, Sampath Uppula -Original Message- From: John Huss [mailto:johnth...@gmail.com] Sent: Thursday, December 13, 2012 2:50 AM To: user@cayenne.apache.org Subject: Re: Problem in fetching the data from multiple tables with relationships. I'm not really sure what your question is. Cayenne will fetch all of the fields that are modeled and turn them into objects. And you can add prefetching paths to your SelectQuery to pre-fetch the related objects, otherwise they will be loaded when accessed. If you want to write your own SQL and get a HashMap of the data you can use SQLTemplate instead of SelectQuery. John Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at a href=http://www.techmahindra.com/Disclaimer.html;http://www.techmahindra.com/Disclaimer.html/a externally and a href=http://tim.techmahindra.com/tim/disclaimer.html;http://tim.techmahindra.com/tim/disclaimer.html/a internally within Tech Mahindra. -- -- Aristedes Maniatis GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A