Inserting 100000 records to MySQL takes too much time

Question

I'm using spring boot, hibernate any MySQL for reading 100000 records from csv file and write the same to the database. I'm doing the batch insert where the data is committed at intervals equal to batch size. The data is saved properly but it takes around 3 minutes to insert all data. Is there anything I can do to improve the performance. Spring Batch is not allowed

This will separate the rows/records with missing values in cell.

private String selectTempFile(String fileName){
    try {

        List<CSVRecord> validList =new ArrayList<>();
        List<CSVRecord> invalidList =new ArrayList<>();

        Reader in = new FileReader(TEMP_LOCATION+fileName);
        Iterable<CSVRecord> record = CSVFormat.EXCEL.withFirstRecordAsHeader().withAllowMissingColumnNames().withIgnoreHeaderCase().parse(in);

        extractColumnHeader(record.iterator().next().toString());

        for(CSVRecord r:record){

            if(r.get(DATE).isEmpty() || r.get(TIMESTAMP).isEmpty() || r.get(FROM_CURRENCY).isEmpty()
                    ||r.get(TO_CURRENCY).isEmpty() || r.get(SPREAD).isEmpty()){
                invalidList.add(r);
            }else{
                validList.add(r);
            }

        }

        log.info("start time :"+new Date().toString());         
        setInvalidDeal(invalidList,fileName);
        setValidDeal(validList,fileName);

        validCSVWriter(validList, fileName);
        invalidCSVWriter(invalidList, fileName);
        log.info("end time :"+new Date().toString());
    } catch (Exception e) {
        e.printStackTrace();
    }       
    return INDEX;
}

This will set all the deals object in list and pass for saving in db

private void setValidDeal(List<CSVRecord> validList, String fileName) {
    List<ValidDeal> list=new ArrayList<>();
    ValidDeal deal = null;
    for(CSVRecord r:validList){
        deal = new ValidDeal();
        try {
            deal.setDate(new SimpleDateFormat(DATE_PATTERN).parse(r.get(DATE)));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //deal.setId(UUID.randomUUID().toString());
        deal.setTimeStamp(r.get(TIMESTAMP));
        deal.setFromCurrencyISOCode(FROM_CURRENCY);
        deal.setToCurrencyISOCode(TO_CURRENCY);
        deal.setFromCurrencyISOCodeAmount(r.get(FROM_CURRENCY));
        deal.setToCurrencyISOCodeAmount(r.get(TO_CURRENCY));
        deal.setAmount(r.get(SPREAD));
        deal.setFileName(fileName+"-valid"+new Date()+CSV_SUFFIX);
        list.add(deal);
    }
    validDealService.saveDeal(list);
}

Entity class

@Entity
public class ValidDeal {
    @Id
    @GeneratedValue
    private Long id;

    @Column(name="from_currency_iso_code")
    private String fromCurrencyISOCode;

    @Column(name="to_currency_iso_code")
    private String toCurrencyISOCode;

    @Column(name="from_currency_iso_code_amount")
    private String fromCurrencyISOCodeAmount;

    @Column(name="to_currency_iso_code_amount")
    private String toCurrencyISOCodeAmount;

    private Date date;
    private String timeStamp;

    private String amount;

    private String fileName;

    //getters and setters
}

This inserts records to Database

public void saveDeal(List<ValidDeal> validDeal) {

    int batchSize=50;
    int numberOfElements=validDeal.size();

    /*
     * This will commit at every time when the size of persist is equal to batch size but will be slow
     * Measured time 3min
     */

    Session session = sessionFactory.openSession();
    Transaction tx;

    while (numberOfElements > 0) {
      numberOfElements -= batchSize;

      //if @GeneratedValue is removed from entity, same record is saved in db after first commit
      tx = session.beginTransaction();

      for (int i = 0; i < batchSize; i++) {
        session.save(validDeal.get(i));
      }

      tx.commit();
      session.clear();
    }

    /*
     * This will commit after all the data are persisted in db, takes less time because commit will
     * happen only at the last.
     * 
     * Measured time 15sec to 20sec for 100000 records
     */

    /*
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();

    for(int i=0;i<validDeal.size();i++){
        ValidDeal deal = validDeal.get(i);
        session.save(deal);
        if(i%batchSize==0){ //same as batch size
            session.flush(); //flush the batch of insert
            session.clear(); //release memory
        }
    }

    tx.commit();
    session.close();
    */
}

Answer 1

I think it's your transaction set up. Without being able to run the code I would guess that the transaction overhead is the bottleneck.

Check out the Insert example here:

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/batch.html#batch-inserts

They are putting the transaction outside the main loop and doing a flush() / clear() based on batch size.

Something like this:

 Session session = sessionFactory.openSession();
 Transaction tx =session.beginTransaction();

    while (numberOfElements > 0) {
      numberOfElements -= batchSize;

      //if @GeneratedValue is removed from entity, same record is saved in db after first commit


      for (int i = 0; i < batchSize; i++) {
        session.save(validDeal.get(i));
      }
        session.flush();
        session.clear();


    }
   tx.commit();
   session.close();

I would also make the code more like the example using i % batchSize == 0 to trigger the flush() / clear() rather than the loop but that's up to you.

Answer 2

Using batch-inserting is one of the methods to decrease a saving time, but still not enough to handle 100k records in 5 or less seconds due to ORM internal mechanism. I suggest you to look into your MySQL DB and use its capabilities. Try to tune your DB tables engine, by default it's set to InnoDB.

InnoDB is a pretty good engine. However, it's recommended to be 'tuned'. In case of unordered PM key inserting, the engine can take a bit longer time than MyISAM. This can be easily overcome by setting a higher value for innodb_buffer_pool_size.

Anyway I would suggest to use LOAD DATA INFILE syntax so that MySQL can perfectly manage CSV files.

Answer 3

Also, you have @GeneratedValue and @Id in your POJO definition. I believe that obstructs Spring Batch. Kindly remove these annotations and try again.