Database persisting of entities is slow when using JPA EntityManager

438 views Asked by At

Currently I am having an issue where I need increase the performance of inserts to my Oracle19c database (need to insert in bulk batches as well). There are cases where I can expect 250,000 records (each representing a single entity). It is taking nearly 15 minutes to persist my data to the database with just 9000 in my count. Is there a reason it is taking so long? And how can I increase the performance around it? Below is my persistence logic and also my entity class, along with hibernate properties set to try to help with batch insert performance:

In my config class for hibernate:

Properties additionalProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.default_schema", SCHEMA);
        properties.setProperty("hibernate.dialect", "com.myproject.db.oracle.CustomAppOracleDialect");
        properties.setProperty("hibernate.jdbc.batch_size", "50");
        properties.put("hibernate.order_inserts", "true");
        return properties;
    }

Entity class:

@Entity
@Table(name = "PROMO")
public class InsertPromoData {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQUENCE1")
    @SequenceGenerator(name="SEQUENCE1", sequenceName="SEQ_PROMO", allocationSize=1)
    @Column(name="SEQ_NUM")
    long id;

    @Column(name="BATCH_ID")
    int batchID;

    @Column(name="ITEM")
    String item;
    
    @Column(name="LOC")
    String loc;
    
    @Column(name="WK_START", columnDefinition = "DATE")
    LocalDate weekStart;
    
    @Column(name="TYPE")
    int type;
    
    @Column(name="FCSTID")
    String forecastId;
    
    @Column(name="U_TIMESTAMP", columnDefinition = "TIMESTAMP")
    LocalDateTime insertTS;
    
    @Column(name="NUMBER_OF_WEEKS")
    int numberOfWeeks;
    
    @Column(name="QTY")
    double qty;
    
    //getters and setters
}

Method using EntityManager:

private static EntityManagerFactory emf;

public static void persistPromoData(List <InsertPromoData> insertData) {
        logger.debug("Execution of method insertPromoData in Dao started");
        EntityManager em = emf.createEntityManager();
        
        try {
            em.getTransaction().begin();
            int count = 0;
            for (InsertPromoData promoData : insertData) {
                em.persist(promoData);
                ++count;
                
                if (count % 1000 == 0) {
                    em.getTransaction().commit();
                    em.clear();
                    em.getTransaction().begin();
                }
            }           
            em.getTransaction().commit();
        }
        catch(Exception e) {
              e.printStackTrace();
              em.getTransaction().rollback();
        }
        finally {
            em.clear();
            em.close();
        }
}
0

There are 0 answers