CODEWITHSUNDEEP
springhibernatejpaspring-data
gene b.
gene b.

Reputation: 12044

Hibernate saveAndFlush() takes a long time for 10K By-Row Inserts

I am a Hibernate novice. I have the following code which persists a large number (say 10K) of rows from a List<String>:

@Override
@Transactional(readOnly = false)
public void createParticipantsAccounts(long studyId, List<String> subjectIds) throws Exception {
    StudyT study = studyDAO.getStudyByStudyId(studyId);
    Authentication auth = SecurityContextHolder.getContext().getAuthentication();
    for(String subjectId: subjectIds) {  // LOOP with saveAndFlush() for each
        // ...
        user.setRoleTypeId(4);
        user.setActiveFlag("Y");
        user.setCreatedBy(auth.getPrincipal().toString().toLowerCase());
        user.setCreatedDate(new Date());
        List<StudyParticipantsT> participants = new ArrayList<StudyParticipantsT>();
        StudyParticipantsT sp = new StudyParticipantsT();
        sp.setStudyT(study);
        sp.setUsersT(user);
        sp.setSubjectId(subjectId);
        sp.setLocked("N");
        sp.setCreatedBy(auth.getPrincipal().toString().toLowerCase());
        sp.setCreatedDate(new Date());
        participants.add(sp);
        user.setStudyParticipantsTs(participants);
        userDAO.saveAndFlush(user);
    }
   }
}

But this operation takes too long, about 5-10 min. for 10K rows. What is the proper way to improve this? Do I really need to rewrite the whole thing with a Batch Insert, or is there something simple I can tweak?

NOTE I also tried userDAO.save() without the Flush, and userDAO.flush() at the end outside the for-loop. But this didn't help, same bad performance.

Upvotes: 0

Views: 1552

Answers (1)

gene b.
gene b.

Reputation: 12044

We solved it. Batch-Inserts are done with saveAll. We define a batch size, say 1000, and saveAll the list and then reset. If at the end (an edge condition) we also save. This dramatically sped up all the inserts.

    int batchSize = 1000;

    // List for Batch-Inserts
    List<UsersT> batchInsertUsers = new ArrayList<UsersT>();

    for(int i = 0; i < subjectIds.size(); i++) {

        String subjectId = subjectIds.get(i);   

        UsersT user = new UsersT();
        // Fill out the object here...
        // ...

        // Add to Batch-Insert List; if list size ready for batch-insert, or if at the end of all subjectIds, do Batch-Insert saveAll() and clear the list
        batchInsertUsers.add(user);
        if (batchInsertUsers.size() == maxBatchSize || i == subjectIds.size() - 1) {
            userDAO.saveAll(batchInsertUsers);
            // Reset list
            batchInsertUsers.clear();
        }

    }

Upvotes: 2

Related Questions