4
\$\begingroup\$

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();
    */
}
\$\endgroup\$
6
  • 2
    \$\begingroup\$ Why are you inserting 100k reccords? How long does it take, when you insert the data directly using the mysql tools? How's the backend looking in terms of cpu / memory / io? Have you tried to deactivate the constraints? How's the id generated / how's the performance when you generate it yourself? \$\endgroup\$ – slowy Apr 28 '17 at 8:54
  • 1
    \$\begingroup\$ It's hard to tell. What happens when you increase the batch size? The transaction overhead for every 50 records could be the cause. Your timing method covers 4 method calls - you should do some timing on individual method execution \$\endgroup\$ – Alzoid Apr 28 '17 at 16:56
  • \$\begingroup\$ @slowy Actually there are 1 million records but I was testing with 100k records. I didn't check directly in mysql. I deactivated the constraints and manually created the primary key. I disabled the spring.jpa.show-sql. This also helped a lot. I also solved the duplicate key entry issue. \$\endgroup\$ – budthapa Apr 28 '17 at 17:42
  • \$\begingroup\$ @Alzoid, yes 50 records was the cause. I increased the batch size commit and performance was gained drastically, total time taken is reduced to 12sec. I think 12 sec is still more. \$\endgroup\$ – budthapa Apr 28 '17 at 17:43
  • \$\begingroup\$ Check my answer below - I linked to some code that could improve your batch process. \$\endgroup\$ – Alzoid Apr 28 '17 at 17:46
1
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ The above method will commit only at last but the requirement was to commit at different interval so this didn't help much. \$\endgroup\$ – budthapa Apr 28 '17 at 17:50
1
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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

\$\endgroup\$
5
  • 2
    \$\begingroup\$ Could you provide more information about why this is a bad practice? \$\endgroup\$ – pacmaninbw Apr 23 '19 at 15:43
  • \$\begingroup\$ Please read here: stackoverflow.com/questions/27697810/… \$\endgroup\$ – Nishant Apr 24 '19 at 11:37
  • \$\begingroup\$ I often quote stack overflow in my answers, I put part of the SO into my answer and provide a link to the question or answer. This would improve your answer. \$\endgroup\$ – pacmaninbw Apr 24 '19 at 11:47
  • \$\begingroup\$ Also, I am not sure why the answer has been downvoted . This is likely to be a valid reason why the batch inserts arent working. If more information is what you wanted, mentioning it in the comment should have been sufficient. Downvoting a valid answer, even if its not detailed beats the purpose \$\endgroup\$ – Nishant Apr 24 '19 at 12:13
  • \$\begingroup\$ The purpose is to review the code and to help the user write better code in the future. This isn't stack overflow and we aren't providing how to answers. I didn't down vote so I don't know the reason. See codereview.stackexchange.com/help/how-to-answer \$\endgroup\$ – pacmaninbw Apr 24 '19 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.