Re: Oracle batch insert

[Fay Wang]

You might want to add the following property to your persistence.xml to enable 
batch execution:

   <property name="openjpa.jdbc.DBDictionary" value="batchLimit=20000" />

Regards,
Fay
________________________________
From: Vetal <vetall...@list.ru>
To: users@openjpa.apache.org
Sent: Mon, January 24, 2011 4:38:25 AM
Subject: Oracle batch insert

It  there  effective  way  to  persist  large  number  of  new objects
(approximately 20000 objects) to Oracle database?

Trying  to  persist in one transaction performs fine but slow (about 1
minute).

Trying  to  do  same  using  jdbc  PreparedStatement  and batch insert
performs slightly faster (about 10 seconds).

How to achieve same result using only OpenJPA?


Code sample using OpenJPA:

        long l = System.currentTimeMillis();
        // get 20000 records from Oracle database
        List<BalanceEnetData> beds = BalanceDAO.getBalanceEnetDataByPeriod(91l);
                                
        LinkedList<BalanceEnetData> newData = new LinkedList<BalanceEnetData>();
        for (BalanceEnetData bed : beds)
        {
            BalanceEnetData bed3 = (BalanceEnetData) ObjectCloner.clone(bed);
            bed3.setPeriod(2011l);
            newData.add(bed3);
        }

        EntityManager em = emf.createEntityManager();

        EntityTransaction tx = null;
                        
        try
        {
            tx = em.getTransaction();
            tx.begin();
                                
            for(BalanceEnetData bed : newData)
            {
                 em.persist(bed);
            }

            tx.commit();
        }
        catch(Throwable t)
        {
             t.printStackTrace();
                                
             if(tx != null)
             {
                 tx.rollback();
             }
        }

Code sample using JDBC:

     long l = System.currentTimeMillis();
     // get 20000 records from Oracle database
     List<BalanceEnetData> beds = BalanceDAO.getBalanceEnetDataByPeriod(91l);

     try
     {
          long l1 = System.currentTimeMillis();
          Connection conn = datasource.getConnection();
          PreparedStatement st = conn.prepareStatement("insert into 
BAL_ENET_DATA(id, period, name) values (?, ?, ?)");
          for (BalanceEnetData bed : beds)
          {
               st.clearParameters();
               st.setLong(1, bed.getId());
               st.setLong(2, 1005);
               st.setString(3, "name");
               st.addBatch();
          }
          st.executeBatch();
     }
     catch (SQLException e)
     {
          e.printStackTrace();
     }

P.S. Oracle version 10gR2, OpenJPA 2.0.1