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();
}
}