Databases
StackOverflow2013
Postsdbo
POHibernate pagination or batch processing
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate pagination or batch processing
    primarykey
    data
    text
    <p><strong>Question:</strong> How can I process (read in) batches of records 1000 at a time and ensure that only the current batch of 1000 records is in memory? Assume my primary key is called '<code>ID</code>' and my table is called <code>Customer</code>. </p> <p><strong>Background:</strong> This is not for user pagination, it is for compiling statistics about my table. I have limited memory available, therefore I want to read my records in batches of 1000 records at a time. I am only reading in records, they will not be modified. I have read that <code>StatelessSession</code> is good for this kind of thing and I've heard about people using <code>ScrollableResults</code>.</p> <p><strong>What I have tried:</strong> Currently I am working on a custom made solution where I implemented Iterable and basically did the pagination by using <code>setFirstResult</code> and <code>setMaxResults</code>. This seems to be very slow for me but it allows me to get 1000 records at a time. I would like to know how I can do this more efficiently, perhaps with something like <code>ScrollableResults</code>. I'm not yet sure why my current method is so slow; I'm ordering by ID but ID is the primary key so the table should already be indexed that way.</p> <p>As you might be able to tell, I keep reading bits and pieces about how to do this. If anyone can provide me a complete way to do this it would be greatly appreciated. I do know that you have to set <code>FORWARD_ONLY</code> on <code>ScrollableResults</code> and that calling <code>evict(entity)</code> will take an entity out of memory (unless you're doing second level caching, which I do not yet know how to check if I am or not). However I don't see any methods in the JavaDoc to read in say, 1000 records at a time. I want a balance between my lack of available memory and my slow network performance, so sending records over the network one at a time really isn't an option here. I am using Criteria API where possible. Thanks for any detailed replies.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKyleM
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHibernate pagination or batch processing
      1. USPSR
      1. VTFavorite
    2. VO
      singulars
      1. POHibernate pagination or batch processing
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHibernate pagination or batch processing
      1. This table or related slice is empty.
      1. VTUpMod
    1. COside note, if anyone can point out the appropriate terminology for 'scrolling through records X at a time' in the comments, that'd be appreciated. I thought it was called batch processing but the Hibernate docs only mention updating & deleting in their batch section...
      singulars
      1. POHibernate pagination or batch processing
      1. USKyleM
    2. COIt is a form of batch processing / pagination, it's just that it probably isn't a very common request. I had a need to do this and ended up just fetching all the PKs in memory and doing a whole lot of `where Id in (...)` queries.
      singulars
      1. POHibernate pagination or batch processing
      1. USmillimoose
    3. COAlso, just because your table is indexed by PK doesn't necessarily mean the records are internally ordered by PK. The table might use an extensible-hashing index. Even if that doesn't sound likely, B+-trees seem to be more common. Also, the query might filter using on a different index than the PK one at some point, which means it'd still need to re-sort the results afterwards. (E.g. if you're selecting records in a date range, they would be retrieved from the table ordered by date.) An EXPLAIN should clear this up.
      singulars
      1. POHibernate pagination or batch processing
      1. USmillimoose
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload