title: Best practices for loading extremely large datasets into a relational database via Java
body: <p>While I have worked with large datasets in the past, never with one quite so huge. Here's the breakdown:</p>
  
  <ul>
  <li>Every month I will receive a 5GB flat file containing 1 database record per row</li>
  <li>This file contains approximately 10 million rows of data, with each subsequent month containing more data than the previous</li>
  <li>The data may have exact duplicates which need to be ignored</li>
  <li>The data may also have inexact duplicates, which need to be analyzed and only one of the rows needs to be inserted, the rest discarded</li>
  </ul>
  
  <p>I need to setup an initial load for 10 years worth of data, which is approximately 1.5 Billion rows. I have no problem with the database handling this, but I am having difficulty loading this data quickly.</p>
  
  <p>I am currently inserting the rows one at a time, allowing Primary Key contraints to let me know if I have a row that needs to be reviewed, those go in a separate table. </p>
  
  <p>Loading a 5GB file takes approximately 10 hours, and this is the fastest I can get it. I have it setup to preprocess about 25,000 rows at a time, then I loop through them via a single statement like so:</p>
  
  <blockquote>
  <pre><code>public void saveBatchSql(String[] sql) {
      try {
          Statement stmt = dbCon.createStatement();
  
          for (String query : sql) {
              try {
                  stmt.execute(query);
              }
              catch (Exception ee) {
                  query = query.replace("component_detail", "component_detail_duplicate");
                  stmt.execute(query);
              }
          }
  
          stmt.close();
      }
      catch (Exception e) {
          e.printStackTrace();
      }
  
  }
  </code></pre>
  </blockquote>
  
  <p>So far, this is the best I can come up with. Does anyone have any ideas on how I can get this to work faster? I can't process all the records in memory as far as I can tell, but maybe there is some other way? </p>
  
  <p>By the way, the database I am loading to is Sybase version 16.</p>