title: Speed up database import from an excel file via reading in by Apache POI and persisting via Hibernate and JPA
body: <p>i am trying to speed up my database import from an excel file via reading in by Apache POI and persisting via Hibernate and JPA (This is a specific requirement, Using a JYA datasource) in JBOSS 7.1. However the import is too slow at present - for 30,000 records it takes about 3 minutes, i need to reduce this to approximately 30 seconds. Im looking for help to set up batch inserts, nothing i have tried at presend works..</p>
  
  <p>My persistence.xml is as follows:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;persistence version="2.0"
     xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="
          http://java.sun.com/xml/ns/persistence
          http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"&gt;
     &lt;persistence-unit name="primary" transaction-type="JTA"&gt;
        &lt;jta-data-source&gt;java:jboss/datasources/MySqlDS&lt;/jta-data-source&gt;
        &lt;properties&gt;      
           &lt;!-- Properties for Hibernate --&gt;
           &lt;property name="hibernate.hbm2ddl.auto" value="update" /&gt;
               &lt;property name="hibernate.default_catalog" value="myDatabase"/&gt;
              &lt;property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" /&gt;
              &lt;property name="hibernate.show_sql" value="false" /&gt;
              &lt;property name="hibernate.format_sql" value="false" /&gt;      
               &lt;property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/&gt;
               &lt;property name="hibernate.order_updates" value="true"/&gt;
               &lt;property name="hibernate.order_inserts" value="true"/&gt;
               &lt;property name="hibernate.jdbc.batch_versioned_data" value="true"/&gt;
               &lt;property name="hibernate.jdbc.fetch_size" value="500"/&gt;
               &lt;property name="hibernate.jdbc.batch_size" value="500"/&gt;
               &lt;property name="hibernate.default_batch_fetch_size" value="16"/&gt;
               &lt;property name="hibernate.connection.release_mode" value="auto"/&gt;
               &lt;property name="hibernate.cache.region.jbc2.cachefactory" value="java:CacheManager"/&gt;
               &lt;property name="hibernate.cache.use_second_level_cache" value="true"/&gt;
               &lt;property name="hibernate.cache.use_query_cache" value="false"/&gt;
               &lt;property name="hibernate.cache.use_minimal_puts" value="true"/&gt;
               &lt;property name="hibernate.cache.region.jbc2.cfg.entity" value="mvcc-entity"/&gt;
               &lt;property name="hibernate.cache.region_prefix" value="services"/&gt;
               &lt;property name="hibernate.connection.driver_class" value="com.mysql"/&gt;
               &lt;property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/myDatabase"/&gt;
               &lt;property name="hibernate.connection.username" value="root"/&gt;
        &lt;/properties&gt;
     &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>
  
  <p>I have an EJB Timer class that deploys on startup in JBOSS and this looks for new excel files and if found imports them to the database - this all works fine - its just slow...
  //Listener Class
                          excelReader.loadDatabase(child.getPath());</p>
  
  <pre><code>// This all works ok            
                  }
  
              }
          }
  
      }
  </code></pre>
  
  <p>*<em>This is the class that actually persists the file via JPA *</em></p>
  
  <pre><code>@Stateless
  @LocalBean
  public class ExcelReader implements TableDao {
  
  @PersistenceContext
  private EntityManager em;
  
  private HSSFRow row = null;
  private HSSFWorkbook wb;
  private BaseDataTable baseDataTable;
  
  public void loadDatabase(String path) 
  {
      try 
      {
          FileInputStream latestExcelFile = new FileInputStream(path);
          wb = new HSSFWorkbook(latestExcelFile);
      } catch (Exception ex) {}   
  
      importTheTable();
  
  }
  
  public ExcelReader() {}
  
  public void importTheTable(){
  
      HSSFSheet baseDataTableSheet = wb.getSheetAt(0);
  
      for (int i = 1; i &lt;= baseDataTableSheet.getLastRowNum(); i++) 
      {
          row = baseDataTableSheet.getRow(i);
          baseDataTable = new BaseDataTable();            
          try 
          {               
              baseDataTable.setDateTime(row.getCell(0).getDateCellValue());
              baseDataTable.setEventId((int) row.getCell(1).getNumericCellValue());
              baseDataTable.setCauseClass(parseCauseClass(row.getCell(2).toString()));
              baseDataTable.setUeType((int) row.getCell(3).getNumericCellValue());
              baseDataTable.setMarket((int) row.getCell(4).getNumericCellValue());
              baseDataTable.setOperator((int) row.getCell(5).getNumericCellValue());
              baseDataTable.setCellId((int) row.getCell(6).getNumericCellValue());
              baseDataTable.setDuration((int) row.getCell(7).getNumericCellValue());
              baseDataTable.setCauseCode((int) row.getCell(8).getNumericCellValue());
              baseDataTable.setNeVersion(row.getCell(9).toString());
              baseDataTable.setImsi(row.getCell(10).getNumericCellValue());
              baseDataTable.setHier3Id((row.getCell(11).toString()));
              baseDataTable.setHier32Id((row.getCell(12).toString()));
              baseDataTable.setHier321Id((row.getCell(13).toString()));
  
  
              addBaseTableEntry(baseDataTable);
  
          } catch (Exception ex) { System.out.println("Error in excel file"); }
  
  
          if(i%1000 == 0)
          {
              em.flush();
              em.clear();
          }
      }
  
  }
  </code></pre>
  
  <p>** This is how the EntityManager is created **</p>
  
  <pre><code>@Stateful
  @RequestScoped
  public class Resources {
  
      @PersistenceContext(type = PersistenceContextType.EXTENDED)
      private EntityManager em;
  
      @Produces
      public EntityManager getEm() {
          return em;
      }
  }
  </code></pre>
  
  <p>This all works fine but it is just too slow - I have searched endlessly online and applied UserTransaction to try and speed up import but to no avail, Any help in the right direction would be much appreciated,</p>
  
  <p>Cheers</p>