问题描述
我正在使用 E** 和容器管理的 EM(我在这里创建本地测试).我有一个需求,我需要根据某些情况更新数据库,我的问题是 更新需要很长时间,如何减少它?
I am using E** and Container managed EM ( for local testing I am creating em here). I have a requirement where I need to update database based on some condition, My issue is Update is taking very long time, how to reduce it ?
我尝试了两种方法1> 更新查询2> 实体更新
I tried two approach 1> Update Query 2> Update in entity
如果我犯了任何错误,或者存在任何其他方法,请告诉我.
Please let me know if I am doing any mistake, or any other approach exist.
注意:更新代码如下
public class Test { private static final int OaOnaccount = 0; private static final int ArrayList = 0; private static EntityManagerFactory emf; private static EntityManager em; static int TEST_SIZE = 20000/4; public static void main(String[] args) { // createBulk(); createUpdateQuery(); // update(); } private static void createUpdateQuery() { long st = System.currentTimeMillis(); emf = Persistence.createEntityManagerFactory("Jpa"); em = emf.createEntityManager(); System.out.println("---- createUpdateQuery ---"); EntityTransaction tx = em.getTransaction(); Query query = em.createQuery("SELECT p FROM OaOnaccount p"); tx.begin(); java.util.Vector<OaOnaccount> list = (java.util.Vector<OaOnaccount>) query.getResultList(); for (int i = 0; i < list.size(); i++) { String m = 1000000 + (i / 20) + ""; query = em .createQuery("UPDATE OaOnaccount p SET p.status='COMPLETED', p.billingDoc='12112ABCS' WHERE p.crDrIndicator='H' AND p.status ='OPEN' AND p.documentNumber="+ m); query.executeUpdate(); } em.flush(); tx.commit(); long et = System.currentTimeMillis(); System.out.println("Test.createUpdateQuery() Time " + (et - st)); } private static void update() { long st = System.currentTimeMillis(); emf = Persistence.createEntityManagerFactory("Jpa"); em = emf.createEntityManager(); System.out.println("---- update ---"); EntityTransaction tx = em.getTransaction(); Query query = em.createQuery("SELECT p FROM OaOnaccount p"); tx.begin(); java.util.Vector<OaOnaccount> list = (java.util.Vector<OaOnaccount>) query .getResultList(); for (int i = 0; i < list.size(); i++) { String m = 1000000 + (i / 20) + ""; query = em .createQuery("SELECT p FROM OaOnaccount p WHERE p.crDrIndicator='H' AND p.status ='OPEN' AND p.documentNumber=" + m); java.util.Vector<OaOnaccount> listEn = (java.util.Vector<OaOnaccount>) query .getResultList(); for (int j = 0; j < listEn.size(); j++) { listEn.get(j).setBillingDoc("12112ABCS"); listEn.get(j).setStatus("COMPLETED"); } } em.flush(); tx.commit(); long et = System.currentTimeMillis(); System.out.println("Test.Update() Time " + (et - st)); } public static void createBulk() { long st = System.currentTimeMillis(); emf = Persistence.createEntityManagerFactory("Jpa"); em = emf.createEntityManager(); System.out.println("-------"); EntityTransaction tx = em.getTransaction(); tx.begin(); for (int i = 0; i < TEST_SIZE; i++) { OaOnaccount entity = new OaOnaccount(); entity.setId("ID-" + i); entity.setCrDrIndicator(i % 2 == 0 ? "H" : "S"); entity.setDocumentNumber(1000000 + (i / 20) + ""); entity.setAssignment(89000000 + (i / 27) + ""); entity.setStatus("OPEN"); em.persist(entity); } em.flush(); tx.commit(); long et = System.currentTimeMillis(); System.out.println("Test.createBulk() Time " + (et - st)); } }
推荐答案
你应该为每 n 次迭代执行 em.flush().例如,如果 n- 数据库交互的数量太少,因此执行代码的速度会变慢.如果 n- 太高,太多的对象驻留在内存中,所以更多的交换因此执行代码的速度变慢.请适度选择n值并应用.我尝试更新 240 万条记录,我遇到了同样的问题.
you should execute em.flush() for every n- number of iterations. for example if n- too low more number of db interactions hence slow in executing code . If n- is too high too many objects resides in memory so more swappings hence slow in executing code . Please choose n value moderately and apply it. I tried update 2.4 million records, I faced same problem.
for (int i = 0; i < list.size(); i++) { String m = 1000000 + (i / 20) + ""; query = em .createQuery("UPDATE OaOnaccount p SET p.status='COMPLETED', p.billingDoc='12112ABCS' WHERE p.crDrIndicator='H' AND p.status ='OPEN' AND p.documentNumber="+ m); query.executeUpdate(); if(i%100==0){// 100 to just to show example-- % operation is costly. you can use better logic to flush. frequent flushing is necessary em.flush(); } }