如何提高使用 JPA 更新数据的性能
我正在使用 EJB 和容器管理的 EM(我在这里创建本地测试).我有一个需求，我需要根据某些情况更新数据库，我的问题是 更新需要很长时间，如何减少它?
I am using EJB 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();
}
}
相关文章