Bulk Updation/Insertion of Database Tables in Java using Hibernate — Optimized Way

  • Using Hibernate’s Query.list() method.
  • Using ScrollableResults with FORWARD_ONLY scroll mode.
  • Using ScrollableResults with FORWARD_ONLY scroll mode in a StatelessSession.

Using Hibernate’s Query.list() method.

Code Executed :

List rows;
Session session = getSession();
Transaction transaction = session.beginTransaction();
try {
Query query = session.createQuery("FROM PersonEntity WHERE id > :maxId ORDER BY id").setParameter("maxId",
MAX_ID_VALUE);
query.setMaxResults(1000);
rows = query.list();
int count = 0;
for (Object row : rows) {
PersonEntity personEntity = (PersonEntity) row;
personEntity.setName(randomAlphaNumeric(30));
session.saveOrUpdate(personEntity);
//Always flush and clear the session after updating 50(jdbc_batch_size specified in hibernate.properties) rows
if (++count % 50 == 0) {
session.flush();
session.clear();
}
}
} finally {
if (session != null && session.isOpen()) {
transaction.commit();
session.close();
}
}

Tests Results :

  • Time taken:- 360s to 400s
  • Heap Pattern:- gradually increased from 13m to 51m(from jconsole).

Using ScrollableResults with FORWARD_ONLY scroll mode.

Code Executed :

Session session = getSession();
Transaction transaction = session.beginTransaction();
ScrollableResults scrollableResults = session
.createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
.setMaxResults(1000).scroll(ScrollMode.FORWARD_ONLY);
int count = 0;
try {
while (scrollableResults.next()) {
PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
personEntity.setName(randomAlphaNumeric(30));
session.saveOrUpdate(personEntity);
if (++count % 50 == 0) {
session.flush();
session.clear();
}
}
} finally {
if (session != null && session.isOpen()) {
transaction.commit();
session.close();
}
}

Tests Results :

  • Time taken:- 185s to 200s
  • Heap Pattern:- gradually increased from 13mb to 41mb (measured same using jconsole)

Using ScrollableResults with FORWARD_ONLY scroll mode in a StatelessSession.

Code Executed :

StatelessSession session = getStatelessSession();
Transaction transaction = session.beginTransaction();
ScrollableResults scrollableResults = session
.createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
.setMaxResults(TRANSACTION_BATCH_SIZE).scroll(ScrollMode.FORWARD_ONLY);
try {
while (scrollableResults.next()) {
PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
personEntity.setName(randomAlphaNumeric(20));
session.update(personEntity);
}
} finally {
if (session != null && session.isOpen()) {
transaction.commit();
session.close();
}
}

Tests Results :

  • Time taken:- 185s to 200s
  • Heap Pattern:- gradually increased from 13mb to 39mb

I also performed the same tests with 2000 rows and the results obtained were as follows:-

Results:-

  • Using list():- time taken:- approx 750s, heap pattern:- gradually increased from 13mb to 74 mb
  • Using ScrollableResultSet:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 46mb
  • Using Stateless:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 43mb

Blocker Problem with all above approaches Tried

Solution :

  • we need to select and update data in different transactions.
  • And updation of these types should be done in Batches

Results using Batch Processing

  • Using list():- time taken:- approx 400s, heap pattern:- gradually increased from 13mb to 61 mb
  • Using ScrollableResultSet:- time taken:- approx 380s, heap pattern:- gradually increased from 13mb to 51mb
  • Using Stateless:- time taken:- approx 190s, heap pattern:- gradually increased from 13mb to 44mb

Observation:- This temporal performance of ScrollableResults dropped down to become almost equal to Query.list(), but performance of Stateless remained almost same.

Summary and Conclusion

  • Use ScrollableResults in a Stateless Session.
  • Perform selection and updation in different transactions in batches of 20 to 50 (Batch Processing) (Note -*- Batch size can depend on the case to case basis)

Sample Code with the best approach

StatelessSession session = getStatelessSession();
Transaction transaction = session.beginTransaction();
ScrollableResults scrollableResults = session
.createQuery("FROM PersonEntity WHERE id > " + MAX_ID_VALUE + " ORDER BY id")
.setMaxResults(TRANSACTION_BATCH_SIZE).scroll(ScrollMode.FORWARD_ONLY);
int count = 0;
try {
StatelessSession updateSession = getStatelessSession();
Transaction updateTransaction = updateSession.beginTransaction();
while (scrollableResults.next()) {
PersonEntity personEntity = (PersonEntity) scrollableResults.get(0);
personEntity.setName(randomAlphaNumeric(5));
updateSession.update(personEntity);
if (++count % 50 == 0) {
updateTransaction.commit();
updateTransaction = updateSession.beginTransaction();
}
}
updateSession.close();
} finally {
if (session != null && session.isOpen()) {
transaction.commit();
session.close();
}
}

--

--

SSE3 at Ameyo

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store