如何提高使用 JPA 更新数据的性能 [英] How to improve performance of Updating data using 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();
          }
    }

这篇关于如何提高使用 JPA 更新数据的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！