How Can I Efficiently Fetch 90 Million Records from MySQL Using Hibernate Without Running Out of Memory?

Optimization Strategies for Reading Large Result Sets with Hibernate

This article addresses the challenge of reading 90 million records from a MySQL database using Hibernate without overloading RAM.

ScrollableResults Limitations

Initially, the user attempted to utilize Hibernate's ScrollableResults to stream results incrementally. However, the MySQL Connector/J library does not provide true scrolling capabilities and loads the entire result set into memory, leading to an OutOfMemoryError exception.

setFirstResult/setMaxResults vs. ScrollableResults

As a workaround, the user considered using setFirstResult and setMaxResults to iteratively fetch batches of results. However, this approach can be inefficient, especially for large result sets, due to the overhead of initializing a new query for each batch.

Optimized Batch Retrieval using Native SQL

The most optimal solution suggested by the respondent was to break out of the MySQL J/Connector and use a batching query:

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

This query grabs a batch of records based on a previously determined maximum ID from the previous batch and orders them by ID in ascending order. The batch_size parameter determines the number of records to retrieve at a time. This approach ensures that only a manageable number of records are processed in memory, preventing memory exhaustion.

The above is the detailed content of How Can I Efficiently Fetch 90 Million Records from MySQL Using Hibernate Without Running Out of Memory?. For more information, please follow other related articles on the PHP Chinese website!