title: Java Batch Insert into MySQL very slow
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance">JDBC batch insert performance</a>  </p>
  </blockquote>
  
  
  
  <p>I have the following method in a class:</p>
  
  <pre><code>    public void insertShingleSets(Vector&lt;ShingleSet&gt; shingleSets)
      {
          String sql = "INSERT   INTO tblPostingsShingles("+
                  "rowId, " +                    
                  "shingle) " +                     
                  "VALUES(?,?);";
  
          PreparedStatement statement = null;
          try {
              statement = conn.prepareStatement(sql);
              for (int i = 0; i &lt; shingleSets.size(); i++)
              {   String id = shingleSets.get(i).getRowId();
                  String shingle = shingleSets.get(i).getShingle();
                  statement.setInt(1, Integer.parseInt(id));
                  statement.setString(2, shingle);
                  statement.addBatch();
                  if ((i + 1) % 1000 == 0) {
                      System.out.println("doing a batch " + i);         //-------------
                      statement.executeBatch(); // Execute every 1000 items.
                      System.out.println("done batch " + i);            //-------------
                               }
              }
              statement.executeBatch();
          }
         catch (SQLException ex)
              {
                  // handle any errors
                  System.out.println("SQLException: " + ex.getMessage());
                  System.out.println("SQLState: " + ex.getSQLState());
                  System.out.println("VendorError: " + ex.getErrorCode());
              }
  
          finally {
              if (statement != null)
                  try { statement.close();
                  } catch (SQLException logOrIgnore) {}
              if (conn != null)
                  try { conn.close();
                  } catch (SQLException logOrIgnore) {}
          }
      }
  </code></pre>
  
  <p>The time between executing: System.out.println("doing a batch " + i);
  and executing: System.out.println("done batch " + i); 
  is about 30 seconds which seems rather a lot considering it's only inserting two columns into a three column table (the other column is an autonumber primary key and when starting/testing there were no rows in the table).
  The only thing I can think of is that before this method is called, another method uses a query that checks in the table tblPostingsShingles to check if certain rowIds are there. However, I would have thought that any locks would be released when that method finishes (it has the same finally clause as this method).
  Any advice would be greatly appreciated.
  Dave</p>