title: Optimize Large MySQL ResultSet
body: <p>I am running a query against a database, and it works fine, but building the data to place into a table takes a while. I am retrieving 500,000 rows of data, and I don't think that it should take very long to display that, but my Application stops responding because of how long it takes. I have tried the same query in different applications and they load the really fast. Is there anything I can do to speed mine up?</p>
  
  <pre><code>public ArrayList&lt;HashMap&lt;String, ArrayList&gt;&gt; runQueries(String query){
      ArrayList&lt;HashMap&lt;String, ArrayList&gt;&gt; arrayList = new ArrayList&lt;&gt;();
      try{
          Statement stmt = conn.createStatement();
          stmt.execute(query);
          while(true){
              HashMap&lt;String, ArrayList&gt; hm = new HashMap&lt;&gt;();
              // Get next resultset if no resultset was returned.
              // The query was either an insert, update or delete
              if(stmt.getUpdateCount() &gt; -1){
                  stmt.getMoreResults();
                  continue;
              }
              // If the resultset is null exit
              if(stmt.getResultSet() == null){
                  break;
              }
              // We have a resultset!
              // Save the columns to an array
              // We can then display them later
              ResultSet rs = stmt.getResultSet();
              int numColumns = rs.getMetaData().getColumnCount();
              ArrayList&lt;String&gt; columns = new ArrayList();
              for(int i = 0; i &lt; numColumns; i++){
                  columns.add(rs.getMetaData().getColumnName(i + 1));
              }
              // Save the columns to the hashmap
              hm.put("columns", columns);
  
              // We now need to save the rows to an array as well
              // We can then display them later as well
              ObservableList&lt;ObservableList&lt;String&gt;&gt; oblist = FXCollections.observableArrayList();
              while(rs.next()){
                  ObservableList&lt;String&gt; row = FXCollections.observableArrayList();
                  for(int i = 1; i &lt;= numColumns; i++){
                      row.add(rs.getString(i));
                  }
                  oblist.add(row);
              }
              ArrayList&lt;ObservableList&gt; rows = new ArrayList&lt;&gt;();
              rows.add(oblist);
              rs.close();
              // Save the rows to the hashmap
              hm.put("rows", rows);
              // Save the hashmap to the final array
              arrayList.add(hm);
              stmt.getMoreResults();
          }
      }catch(SQLException ex){
          Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
      }
      return arrayList;
  }
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <p>I have narrowed it down to the section that takes a while to run:</p>
  
  <pre><code>ObservableList&lt;ObservableList&lt;String&gt;&gt; oblist = FXCollections.observableArrayList();
  while(rs.next()){
      ObservableList&lt;String&gt; row = FXCollections.observableArrayList();
      for(int i = 1; i &lt;= numColumns; i++){
          row.add(rs.getString(i));
      }
      oblist.add(row);
  }
  </code></pre>