0

My project requires a service that will repeatable migration between two tables on two different databases. I have implemented this via Hibernate. So, I have a service that fetches data from the primary database table and then migrate it to the second database table, however, my primary database table has over 200000 rows and the iterator takes quite a long time to complete the task.

What should I use to speed up the process?

Here is the Service code:

@Service
public class TrmInCardClientService {

    @Autowired
    TrmInCardClientDBRepository trmInCardClientDBRepository;
    @Autowired
    TrmInCardClientUKMRepository trmInCardClientUKMRepository;
    private Logger log = Logger.getLogger(TrmInCardClientService.class.getName());

    public TrmInCardClientService() {
    }

    @Scheduled(
            fixedRate = 300000
    )
    public void updatelist() {
        this.log.info("TrmInCardClient data transfer start");
        try {
            Iterable<TrmInCardClientUKM> trmInCardClientUKMS = this.trmInCardClientUKMRepository.findByDeleted(0);
            List<TrmInCardClientUKM> trmInCardClientUKMList = new ArrayList();
            List<TrmInCardClientDB> trmInCardClientDBList = new ArrayList<>();
            Iterator var5 = trmInCardClientUKMS.iterator();

            while (var5.hasNext()) {
                TrmInCardClientUKM cardClientUKM = (TrmInCardClientUKM) var5.next();
                trmInCardClientUKMList.add(cardClientUKM);
                trmInCardClientDBList.add(new TrmInCardClientDB(cardClientUKM.getCard(), cardClientUKM.getClient(),
                        cardClientUKM.getDeleted(), cardClientUKM.getGlobal_id(), cardClientUKM.getVersion()));
            }

            this.trmInCardClientDBRepository.saveAll(trmInCardClientDBList);
            this.log.info("TrmInCardClient data transfer end");
        }
        catch (Exception e) {
            this.log.warning("Error encountered during TrmInCardClient data migration");
        }
    }
}
Kether
  • 1
  • Do this in a stored procedure in the DB instead of in your client. – tgdavies Dec 14 '22 at 04:54
  • But also: why do you put the records into a list and do `saveAll`? You might as well save them one by one. And most importantly: which part of the operation is slow? The query or the insertions? – tgdavies Dec 14 '22 at 04:56
  • @tgdavies Hello, ty for you answer. I took this service from my earlier project and edited it, in the last project the table consisted of 4000 rows, which is why the slowness was not noticeable. The slowest part is that it iterates through all the rows that satisfy the requirements SELECT query and then execute INSERT query. – Kether Dec 14 '22 at 05:28
  • So the select itself is fast? – tgdavies Dec 14 '22 at 06:15
  • I would say SELECT and INSERT are equally slow. The whole service takes about 20 minutes, where 10 minutes is SELECT and another 10 minutes is INSERT. I would like the whole service to run in 5 minutes as I will need to connect more tables of 200000 rows in the future. – Kether Dec 14 '22 at 06:21
  • I was offered to completely rewrite everything in JDBC, but I'm not sure if it will be more efficient than Hibernate. – Kether Dec 14 '22 at 06:23
  • Make sure you have the right index on the table for the select. Do it as a single JDBC statement, i.e. https://stackoverflow.com/questions/25969/insert-into-values-select-from – tgdavies Dec 14 '22 at 06:28

0 Answers0