How to improve performance of Updating data using JPA

Question

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 ?

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.

Note : code for update is below

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

    }

}

score 2 · Accepted Answer · answered Jun 04 '15 at 02:11

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

mikeb · Answer 2 · 2015-06-02T20:31:12.273

1

Check the indexes you have on the OaOnaccount table, specifically ensure that p.status and p.documentNumber have an index on them, or even better a combined index that uses (status, documentNumber)

edited Jun 02 '15 at 20:31

answered Jun 02 '15 at 18:05

mikeb

10,578
7
62
120

Yeh your soln helped me, time reduced from 244790ms to 167525ms But any other approach bcos inseart takes 3807 ms but update is huge – Arvind Jun 02 '15 at 18:18
Well, perhaps you need to tune your database some more. What database are you using? How many rows in the table? You might need to do some memory tuning for your database. I know that with MySQL the memory settings in your my.cnf for various sort buffers can have drastic effects on your performance. – mikeb Jun 02 '15 at 18:21
I can't help you with Oracle, best to ask this question to someone who knows oracle, I don't think the problem is with Java/JPA. You could also create an empty table and try it and see if that speeds it up – mikeb Jun 02 '15 at 20:32

How to improve performance of Updating data using JPA

2 Answers2

Linked