title: SQLite: Difference between java/jdbc-sqlite and python/sqlite3
body: <p>I'm currently working on desktop application implemented in java(7) that, among other things, manages a large number of data records in a sql-database. There are only a few tables, but they contain a large number of records. I need to do complex queries on a single table but no complex join operations.</p>
  
  <p>So far, I have been working with postgres. But since it's a desktop single-user application, I also though about using sqlite (needless to say, that would also decrease the complexity of the setup). So I wrote a simple python script in order make some performance tests. What surprised me is, first, how well sqlite actually performed and, second, that in python the query response time was much smaller than in java.</p>
  
  <p>A common scenario is the selection of a batch of records according to a list of ids. In python, I used the following code to test the response time:</p>
  
  <pre><code>rand_selection = ','.join([str(int(random.random()* MAX_INDEX )) for i in xrange(PAGE_SIZE)])
  start = time.time();
  c = db.cursor();
  res = c.execute("SELECT * FROM bigtable WHERE id in ("+rand_selection+")");
  reslist = [str(t) for t in res]; c.close();
  print( time.time() - start );
  </code></pre>
  
  <p>This gives me a delta of about 5 ms for MAX_INDEX=111000 and PAGE_SIZE=100.</p>
  
  <p>Well, great. Now, let's move to java: I use the <a href="https://bitbucket.org/xerial/sqlite-jdbc" rel="nofollow">jdbc-sqlite</a> driver. I performed the exact same query on the exact same table and the query time is always around 200 ms which is unacceptable for my use case.</p>
  
  <p>Am I missing out on something?</p>
  
  <p>I know that this is a very general question. But maybe someone has some experience with jdbc-sqlite and knows from experience what's going on …</p>
  
  <p><strong>[Edit]</strong>: Using the timit.default_timer() as suggested (thanks, Martijn Pieters) gives me similar results.</p>
  
  <p><strong>[Edit2]</strong>: Following CL's suggestion, I wrote a simplified version of the java code. Using this code I could <em>not</em> verify the results and the response times were about the same as for the python code. However, I tested this is on a different machine, using a different jdk (openjdk7 vs. oracle jdk7). Admittedly, my other test code most probably has some issues.</p>
  
  <p><strong>[Edit 2013-08-16]</strong>: I now performed the same test using the original setup. I also compared it to postgres.</p>
  
  <pre><code>Model Name:    MacBook Pro
  Model Identifier: MacBookPro5,5
  Processor Name:   Intel Core 2 Duo
  Processor Speed:  2.53 GHz
  Memory: 8GB
  OS-Version: 10.8.4
  Java:
  Java(TM) SE Runtime Environment (build 1.7.0_21-b12)
  Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)
  </code></pre>
  
  <p>The test code (please excuse the sloppy coding …):</p>
  
  <pre><code>package ch.dsd;
  
  import java.sql.*;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Properties;
  
  public class Main {
  
      private static int COL_COUNT = 20;
      private static int TESTRUNS = 20;
      private static int INDEX_COUNT = 64;
      /*
      CREATE TABLE bigtable ( id INTEGER PRIMARY KEY ASC, prop0 real, prop1 real, ... , prop19 real );
       */
      static class Entity {
          private long id;
          private ArrayList&lt;Double&gt; properties = new ArrayList&lt;Double&gt;(COL_COUNT);
  
          public Entity() {
              for( int i = 0; i &lt; COL_COUNT; i++) {
                  properties.add(0.0);
              }
          }
  
          public long getId() {
              return id;
          }
  
          public void setId(long id) {
              this.id = id;
          }
  
          public void setProperty(int idx, double prop) {
              properties.set(idx, prop);
          }
  
          public double getProperty(int idx) {
              return properties.get(idx);
          }
  
          @Override
          public String toString() {
              StringBuilder sb = new StringBuilder();
              for( double prop: properties ) {
                  sb.append(prop);
                  sb.append(",");
              }
              sb.delete(sb.length()-1, sb.length());
              return sb.toString();
          }
      }
  
      private static String placeholders( int n ) {
          StringBuilder sb = new StringBuilder();
          if( n &gt; 0 ) {
              sb.append("?");
              for( int i = 1; i &lt; n; i++ )
                  sb.append(",?");
              return sb.toString();
          }
          return "";
      }
  
      private static void setRandomIdcs( PreparedStatement ps, int start, int stop, int max ) throws SQLException {
          for( int i = start; i &lt;= stop; i++ ) {
              ps.setLong(i, (long) ((double) max * Math.random()));
          }
      }
  
      private static void setRandomValues( PreparedStatement ps, int start, int stop ) throws SQLException {
          for( int i = start; i &lt;= stop; i++ ) {
              ps.setDouble(i, Math.random());
          }
      }
  
  
      private static void readFromResultSet( ResultSet rs, List&lt;Entity&gt; lst ) throws SQLException {
          while(rs.next()) {
              final Entity e = new Entity();
              e.setId(rs.getLong(1));
              for( int i = 0; i &lt; COL_COUNT; i++ )
                  e.setProperty(i, rs.getDouble(i+2));
              lst.add(e);
          }
      }
  
      public static void performTest(Connection c) throws SQLException {
          final PreparedStatement ps = c.prepareStatement("SELECT * FROM bigtable WHERE id in ("+placeholders(INDEX_COUNT)+")");
          ArrayList&lt;Entity&gt; entities = new ArrayList&lt;Entity&gt;();
          for( int i = 0; i &lt; TESTRUNS; i++ ) {
              setRandomIdcs( ps, 1, INDEX_COUNT, 1000000 ); // there are one million entries stored in the test table
              long start = System.currentTimeMillis();
              final ResultSet rs = ps.executeQuery();
              readFromResultSet(rs, entities);
              // System.out.println(entities.get(INDEX_COUNT-1));
              System.out.println("Time used:" + (System.currentTimeMillis() - start));
              System.out.println("Items read:" + entities.size());
              rs.close();
              entities.clear();
          }
          ps.close();
      }
  
      public static void createPSQLTable(Connection c) throws SQLException {
          final String create_stmt = "CREATE TABLE IF NOT EXISTS bigtable (id SERIAL PRIMARY KEY, " +
                  "prop0 double precision,prop1 double precision,prop2 double precision,prop3 double precision,prop4 double precision,prop5 double precision,prop6 double precision,prop7 double precision,prop8 double precision,prop9 double precision,prop10 double precision,prop11 double precision,prop12 double precision,prop13 double precision,prop14 double precision,prop15 double precision,prop16 double precision,prop17 double precision,prop18 double precision,prop19 double precision)";
          final PreparedStatement ps = c.prepareStatement(create_stmt);
          ps.executeUpdate();
          ps.close();
      }
  
      public static void loadPSQLTable( Connection c ) throws SQLException {
          final String insert_stmt = "INSERT INTO bigtable VALUES (default, " + placeholders(20) + ")";
          final PreparedStatement ps = c.prepareStatement(insert_stmt);
          for( int i = 0; i &lt; 1000000; i++ ) {
              setRandomValues(ps, 1, 20);
              ps.executeUpdate();
          }
          c.commit();
      }
  
      public static void main(String[] args) {
          Connection c = null;
          try {
              Class.forName("org.sqlite.JDBC");
              c = DriverManager.getConnection("jdbc:sqlite:/Users/dsd/tmp/sqlitetest/testdb.db");
              c.setAutoCommit(false);
              performTest(c);
              c.close();
              System.out.println("POSTGRES");
              System.out.println("========");
              final Properties props = new Properties();
              props.setProperty("user", "dsd");
              c = DriverManager.getConnection("jdbc:postgresql:testdb", props);
              c.setAutoCommit(false);
              createPSQLTable(c);
              // loadPSQLTable(c);
              performTest(c);
              c.close();
          } catch ( Exception e ) {
              System.err.println( e.getClass().getName() + ": " + e.getMessage() );
              System.exit(0);
          }
      }
  }
  </code></pre>
  
  <p>Results: </p>
  
  <pre><code>Time used:348
  Items read:64
  Time used:407
  Items read:64
  Time used:259
  Items read:64
  Time used:341
  Items read:64
  Time used:325
  Items read:64
  Time used:145
  Items read:64
  Time used:70
  Items read:64
  Time used:98
  Items read:64
  Time used:91
  Items read:64
  Time used:134
  Items read:64
  Time used:68
  Items read:64
  Time used:51
  Items read:64
  Time used:51
  Items read:64
  Time used:51
  Items read:64
  Time used:55
  Items read:64
  Time used:67
  Items read:64
  Time used:56
  Items read:64
  Time used:90
  Items read:64
  Time used:56
  Items read:64
  Time used:51
  Items read:64
  POSTGRES
  ========
  Time used:75
  Items read:64
  Time used:58
  Items read:64
  Time used:31
  Items read:64
  Time used:26
  Items read:64
  Time used:34
  Items read:64
  Time used:6
  Items read:64
  Time used:5
  Items read:64
  Time used:4
  Items read:64
  Time used:5
  Items read:64
  Time used:6
  Items read:64
  Time used:5
  Items read:64
  Time used:6
  Items read:64
  Time used:4
  Items read:64
  Time used:28
  Items read:64
  Time used:3
  Items read:64
  Time used:4
  Items read:64
  Time used:4
  Items read:64
  Time used:4
  Items read:64
  Time used:3
  Items read:64
  Time used:5
  Items read:64
  </code></pre>