title: Java SQLite JDBC "SELECT" statement very slow
body: <p>From an API i get ~9000 entries which i have to insert into a table. Detailed data for every of these entries is stored in another table with ~45000 entries</p>
  
  <p>Now i am trying to insert these 9000 entries into a new table and getting the needed information about these entries from the other table.</p>
  
  <p>The SELECT statement for the data in the table with 45000 entries takes something between 30-90ms each time.</p>
  
  <p>The code for the select is as follows (Repeated 9000 times):</p>
  
  <pre><code>                long startTime = System.nanoTime();
  
                  PreparedStatement selectDB = cStatic.prepareStatement("SELECT column1, column2, column3, column4 FROM DB WHERE ID = ?");
                  selectDB.setInt(1, rsIDs.getInt(3));
  
                  ResultSet rsDB = selectDB.executeQuery();
  
                  long endTime = System.nanoTime();
  
                  long duration = (startTime - endTime) / 1000000;
  </code></pre>
  
  <p>From the whole process (collecting information, iserting) the SELECT + ResultSet alone take 95% of the processing time, i'd like to know how i can speed this up</p>
  
  <p>Since i already searched for solutions i am already using indexes for "getInt/getString" from result set and also only call the needed columns of the table instead of the full row, but only a very minor improvement.</p>
  
  <p>Every other statement (UPDATE , INSERT, DELETE) hardly even uses 1ms, why is SELECT needing so much time?</p>
  
  <p>EDIT:</p>
  
  <p>Didn't really speed up the SELECT , but i was rebuilding the code to only use the SELECT once and store every important data into an array , now it's done in 2-3 seconds instead of 5 minutes.</p>
  
  <p>Thanks to Rahul i tried giving the columns an index and it helped a bit too:</p>
  
  <p>ALTER TABLE table_name ADD INDEX <code>idx_id</code> (<code>ID</code>);</p>