title: What data is cached during a "select" in sqlite3/Python, and can this be done manually from the start?
body: <p>Suppose you have a sqlite database with several thousand rows -- each of which either contains or references a sizable, unique blob -- and you want to sparsely sample this collection, pulling rows based on rowid or some equivalent primary key. I find that the first time I attempt to fetch several (500) datapoints after connecting (out of 20k rows), the call takes over 10 seconds to return; and, with every successive iteration, the calls get shorter and shorter, until converging to around 100 milliseconds after 50-100 such queries.</p>
  
  <p>Clearly, either sqlite or its python wrapper must be caching... something. If I clear out inactive memory (I'm in OS X, but I think Linux has a comparable if-not-identical "purge" command?), the behavior can be replicated exactly. The question is, what is it caching that an index doesn't address? And furthermore, is it possible to automatically pull whatever information is accelerating these queries into memory from the start? Or is there something else I've missed entirely?</p>
  
  <p>A few notes in case someone doesn't immediately know the answer... </p>
  
  <ul>
  <li><p>Each blob is around 40kB, and are a large (ha) source of the problem. I've some code below for anyone who wants to play along at home, but I've had better luck keeping separate tables for sortable information and data. This introduces an inner join, but it's generally been better than keeping it all together (although if anyone feels this is wrong, I'm keen to hear it). Without the inner join / data fetch, things start at 4 seconds and drop to 3 ms in a hurry.</p></li>
  <li><p>I feel like this might be a PRAGMA thing, but I fiddled with some settings suggested by others in the wilderness of the web and didn't really see any benefit.</p></li>
  <li><p>In-memory databases are not an option. For one, I'm trying to share across threads (which might not actually be a problem for in-mems...? not sure), but more importantly the database files are typically on the order of 17 GB. So, that's out.</p></li>
  <li><p>That being said, there's no problem caching a reasonable amount of information. After a few dozen calls, inactive memory gets somewhat bloated anyways, but I'd rather do it (1) right and (2) efficiently.</p></li>
  </ul>
  
  <p>Okay, now some code for anyone who wants to try to replicate things. You should be able to copy and paste it into a stand-alone script (that's basically what I did, save for formatting).</p>
  
  <pre><code>import sqlite3
  import numpy as np
  import time
  
  ref_uid_index = """CREATE INDEX ref_uid_idx
                     ON data(ref_uid)"""
  
  
  def populate_db_split(db_file, num_classes=10, num_points=20000, VERBOSE=False):
      def_schema_split0 = """
      CREATE TABLE main (
          uid            INTEGER PRIMARY KEY,
          name           TEXT,
          label          INTEGER,
          ignore         INTEGER default 0,
          fold           INTEGER default 0)"""
  
      def_schema_split1 = """
          CREATE TABLE data (
              uid            INTEGER PRIMARY KEY,
              ref_uid        INTEGER REFERENCES main(uid),
              data           BLOB)"""
  
      def_insert_split0 = """
          INSERT INTO main (name, label, fold)
              VALUES (?,?,?)"""
  
      def_insert_split1 = """
          INSERT INTO data (ref_uid, data)
              VALUES (?,?)"""
  
      blob_size= 5000
      k_folds = 5
      some_names = ['apple', 'banana', 'cherry', 'date']
  
      dbconn = sqlite3.connect(db_file)
      dbconn.execute(def_schema_split0)
      dbconn.execute(def_schema_split1)
  
      rng = np.random.RandomState()
      for n in range(num_points):
          if n%1000 == 0 and VERBOSE:
              print n
  
          # Make up some data
          data = buffer(rng.rand(blob_size).astype(float))
          fold = rng.randint(k_folds)
          label = rng.randint(num_classes)
          rng.shuffle(some_names)
  
          # And add it
          dbconn.execute(def_insert_split0,[some_names[0], label, fold])
          ref_uid = dbconn.execute("SELECT uid FROM main WHERE rowid=last_insert_rowid()").fetchone()[0]
          dbconn.execute(def_insert_split1,[ref_uid,data])
  
      dbconn.execute(ref_uid_index)
      dbconn.commit()
      return dbconn
  
  def timeit_join(dbconn, n_times=10, num_rows=500):
      qmarks = "?,"*(num_rows-1)+"?"
  
      q_join = """SELECT data.data, main.uid, main.label
                FROM data INNER JOIN main ON main.uid=data.ref_uid
                WHERE main.uid IN (%s)"""%qmarks
  
      row_max = dbconn.execute("SELECT MAX(rowid) from main").fetchone()[0]
  
      tstamps = []
      for n in range(n_times):
          now = time.time()
          uids = np.random.randint(low=1,high=row_max,size=num_rows).tolist()
          res = dbconn.execute(q_join, uids).fetchall()
  
          tstamps += [time.time()-now]
          print tstamps[-1]
  </code></pre>
  
  <p>Now, if you want to replicate things, do the following. On my machine, this creates an 800MB database and produces something like below.</p>
  
  <pre><code>&gt;&gt;&gt; db = populate_db_split('/some/file/path.db')
  &gt;&gt;&gt; timeit_join(db)
  12.0593519211
  5.56209111214
  3.51154184341
  2.20699000359
  1.73895692825
  1.18351387978
  1.27329611778
  0.934082984924
  0.780968904495
  0.834318161011
  </code></pre>
  
  <p>So... what say you, knowledgable sages?</p>