Spring Data JPA takes longer to persist entity with BigDecimal datatype

33 Views Asked by At

I have a spring project that uses Spring data JPA and the entity looks like below

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@Entity
public class FinancialDetail extends BaseTransactionalEntity {

    @Id
    private CustomCompositeKey key;
    private String batchId;
    private LocalDate date1;
    private LocalDate date2;
    @Enumerated(EnumType.STRING)
    private TransactionType transactionType;
    private String code1;
    private String code2;
    private String code3;
    @Enumerated(EnumType.STRING)
    private Bearer bearer;
    private boolean flag1;
    private boolean flag2;
    private boolean flag3;
    private String code4;
    private String code5;
    private String code6;
    private String code7;
    private BigDecimal value1;
    private BigDecimal value2;
    private BigDecimal value3;
    private BigDecimal value4;
    private BigDecimal value5;
    private BigDecimal value6;
    private BigDecimal value7;
    private BigDecimal value8;
}

Below is the code to save this entity.

private void persist(List<FinancialDetail> financialDetails) {
   EntityManager entityManager = entityManagerFactory.createEntityManager();
   EntityTransaction transaction = entityManager.getTransaction();
   Iterator<FinancialDetail> iterator = 
           financialDetails.iterator();
   transaction.begin();
   int count = 0;
   while (iterator.hasNext()) {
      entityManager.persist(iterator.next());
      count++;
      if (count % batchSize == 0) {
        entityManager.flush();
        entityManager.clear();
      }
   }
   transaction.commit();
   entityManager.close();
}

On saving 1000 records, this entity takes 2 seconds to save.

This is a batch insert and usually there are about 10,000 records to be saved and it gets painfully slow with the BigDecimal fields.

All BigDecimal fields are being mapped to numeric(18,3) column in Ms SQL

Database connection string: jdbc:sqlserver://IP:1433;databaseName=Database;trustServerCertificate=true;cachePrepStmts=true;useServerPrepStmts=true;rewriteBatchedStatements=true;sendStringParametersAsUnicode=false

Dependency:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>12.2.0.jre11</version>
</dependency>

Are there any conversions/validations/rounding happening behind the scenes that is consuming so much time?

As part of investigation, I changed the data type of all BigDecimal fields to String and the same number of records took just 200ms.

I receive these records from an API and during deserialization and I tried rounding them using value = value.setScale(3, RoundingMode.HALF_UP) so that it is in sync with the database column precision.

0

There are 0 best solutions below