Using Hibernate’s ScrollableResults to slowly read 90 million records

by

Using setFirstResult and setMaxResults is your only option that I’m aware of.

Traditionally a scrollable resultset would only transfer rows to the client on an as required basis. Unfortunately the MySQL Connector/J actually fakes it, it executes the entire query and transports it to the client, so the driver actually has the entire result set loaded in RAM and will drip feed it to you (evidenced by your out of memory problems). You had the right idea, it’s just shortcomings in the MySQL java driver.

I found no way to get around this, so went with loading large chunks using the regular setFirst/max methods. Sorry to be the bringer of bad news.

Just make sure to use a stateless session so there’s no session level cache or dirty tracking etc.

EDIT:

Your UPDATE 2 is the best you’re going to get unless you break out of the MySQL J/Connector. Though there’s no reason you can’t up the limit on the query. Provided you have enough RAM to hold the index this should be a somewhat cheap operation. I’d modify it slightly, and grab a batch at a time, and use the highest id of that batch to grab the next batch.

Note: this will only work if other_conditions use equality (no range conditions allowed) and have the last column of the index as id. 

select * 
from person 
where id > <max_id_of_last_batch> and <other_conditions> 
order by id asc  
limit <batch_size>

More Related Contents:

  1. Hibernate 5 :- org.hibernate.MappingException: Unknown entity
  2. The server time zone value ‘AEST’ is unrecognized or represents more than one time zone
  3. Unable to get spring boot to automatically create database schema
  4. Hibernate: Automatically creating/updating the db tables based on entity classes
  5. No Dialect mapping for JDBC type: 1111
  6. @GeneratedValue polymorphic abstract superclass over MySQL
  7. Spring boot JPA insert in TABLE with uppercase name with Hibernate
  8. Can Hibernate work with MySQL’s “ON DUPLICATE KEY UPDATE” syntax?
  9. Spring, Hibernate, Blob lazy loading
  10. How can I use MySQL assign operator(:=) in hibernate native query?
  11. Enum in Hibernate, persisting as an enum
  12. When are connections returned to the connection pool with Spring JPA (Hibernate) Entity Manager?
  13. Hibernate Criteria Query to get specific columns
  14. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed
  15. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  16. Invalid syntax error “type= MyISAM” in DDL generated by Hibernate
  17. Hibernate: Create Mysql InnoDB tables instead of MyISAM
  18. No matter what, I can’t batch MySQL INSERT statements in Hibernate
  19. com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source
  20. org.hibernate.InstantiationException: No default constructor for entity: : principal.Cliente
  21. Is there a more efficient way of making pagination in Hibernate than executing select and count queries?
  22. How to setup Hibernate to read/write to different datasources?
  23. Hibernate Criteria API: get n random rows
  24. Unable to get multiple Table entities through Stored procedure using hibernate
  25. Make hibernate backquote all table / column names
  26. How do you do a limit query in JPQL or HQL?
  27. How can I log SQL statements in Spring Boot?
  28. Default fetch type for one-to-one, many-to-one and one-to-many in Hibernate
  29. How I save and retrieve an image on my server in a java webapp [duplicate]
  30. Retrieve an Image stored as BLOB on a MYSQL DB

Leave a Comment