title: Optimising a lot of inserts with JDBC and MySQL
body: <p>I have to perform a large number of inserts (in this instance 27k) and I want to find an optimal to do this. Right now this is the code that I have. As you can see I'm using prepared statement and batches and I'm executing every 1000 ( I have also tried with a lesser number such as 10 and 100 but the time was again way to long). One thing which is omitted from the query is that there is an auto-generated ID if it is of any matter to the issue: </p>
  
  <pre><code>private void parseIndividualReads(String file, DBAccessor db) {
      BufferedReader reader;
      try {
          Connection con = db.getCon();
          PreparedStatement statement = null;
          statement = con.prepareStatement("INSERT INTO `vgsan01_process_log`.`contigs_and_large_singletons` (`seq_id` ,`length` ,`ws_id` ,`num_of_reads`) VALUES (?, ?, ?, ?)");
          long count = 0;
          reader = new BufferedReader(new FileReader(logDir + "/" + file));
          String line;
          while ((line = reader.readLine()) != null) {
              if(count != 0 &amp;&amp; count % 1000 == 0)
                  statement.executeBatch();
  
              if (line.startsWith("&gt;")) {
                  count++;
                  String res[] = parseHeader(line);
                  statement.setString(1, res[0]);
                  statement.setInt(2, Integer.parseInt(res[1]) );
                  statement.setInt(3, id);
                  statement.setInt(4, -1);
                  statement.addBatch(); 
              }
          }
  
          statement.executeBatch();
      } catch (FileNotFoundException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error opening file: " + file, ex);
      } catch (IOException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error reading from file: " + file, ex);
      } catch (SQLException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error inserting individual statistics " + file, ex);
      }
  }
  </code></pre>
  
  <p>Any other tips regarding what might be changed in order to speed up the process. I mean a single insert statement doesn't have much information - I'd say no more than 50 characters for all 4 columns</p>
  
  <p>EDIT:</p>
  
  <p>Okay following the advice given I have restructured the method as follows. The speed up is immense. You could even try and play with the 1000 value which might yield better results:</p>
  
  <pre><code>private void parseIndividualReads(String file, DBAccessor db) {
      BufferedReader reader;
      PrintWriter writer;
      try {
          Connection con = db.getCon();
          con.setAutoCommit(false);
          Statement st = con.createStatement();
          StringBuilder sb = new StringBuilder(10000);
  
          reader = new BufferedReader(new FileReader(logDir + "/" + file));
          writer = new PrintWriter(new BufferedWriter(new FileWriter(logDir + "/velvet-temp-contigs", true)), true);
          String line;
          long count = 0;
          while ((line = reader.readLine()) != null) {
  
              if (count != 0 &amp;&amp; count % 1000 == 0) {
                  sb.deleteCharAt(sb.length() - 1);
                  st.executeUpdate("INSERT INTO `vgsan01_process_log`.`contigs_and_large_singletons` (`seq_id` ,`length` ,`ws_id` ,`num_of_reads`) VALUES " + sb);
                  sb.delete(0, sb.capacity());
                  count = 0;
              }
              //we basically build a giant VALUES (),(),()... string that we use for insert
              if (line.startsWith("&gt;")) {
                  count++;
                  String res[] = parseHeader(line);
                  sb.append("('" + res[0] + "','" + res[1] + "','" + id + "','" + "-1'" + "),");
              }
          }
  
          //insert all the remaining stuff
          sb.deleteCharAt(sb.length() - 1);
          st.executeUpdate("INSERT INTO `vgsan01_process_log`.`contigs_and_large_singletons` (`seq_id` ,`length` ,`ws_id` ,`num_of_reads`) VALUES " + sb);
          con.commit();
      } catch (FileNotFoundException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error opening file: " + file, ex);
      } catch (IOException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error reading from file: " + file, ex);
      } catch (SQLException ex) {
          Logger.getLogger(VelvetStats.class.getName()).log(Level.SEVERE, "Error working with mysql", ex);
      }
  }
  </code></pre>