Why is :memory: in sqlite so slow?
I've been trying to see if there are any performance improvements gained by using in-memory sqlite vs. disk based sqlite. Basically I'd like to trade startup time and memory to get extremely rapid queries which do not hit disk during the course of the application.
However, the following benchmark gives me only a factor of 1.5X in improved speed. Here, I'm generating 1M rows of random data and loading it into both a disk and memory based version of the same table. I then run random queries on both dbs, returning sets of size approx 300k. I expected the memory based version to be considerably faster, but as mentioned I'm only getting 1.5X speedups.
I experimented with several other sizes of dbs and query sets; the advantage of :memory: does seem to go up as the number of rows in the db increases. I'm not sure why the advantage is so small, though I had a few hypotheses:
- the table used isn't big enough (in rows) to make :memory: a huge winner
- more joins/tables would make the :memory: advantage more apparent
- there is some kind of caching going on at the connection or OS level such that the previous results are accessible somehow, corrupting the benchmark
- there is some kind of hidden disk access going on that I'm not seeing (I haven't tried lsof yet, but I did turn off the PRAGMAs for journaling)
Am I doing something wrong here? Any thoughts on why :memory: isn't producing nearly instant lookups? Here's the benchmark:
==> sqlite_memory_vs_disk_benchmark.py <==
#!/usr/bin/env python
"""Attempt to see whether :memory: offers significant performance benefits.
"""
import os
import time
import sqlite3
import numpy as np
def load_mat(conn,mat):
c = conn.cursor()
#Try to avoid hitting disk, trading safety for speed.
#http://stackoverflow.com/questions/304393
c.execute('PRAGMA temp_store=MEMORY;')