I know very little about databases and even less about how to optimize them, but I have a problem which calls for a database so here I am...
I created a sqlite3 database using the following script:
sqlite3 my.db <<EOF create table bpe_lookup (id integer primary key, bpe text); create unique index idx_id on bpe_lookup(id); .separator "\t" .import my_data.tsv bpe_lookup EOF
my_data.tsv contains about 34M records and is fairly small -- approximately 11GB and the resulting DB file is 12GB. My machine has hundreds of GBs of RAM and 40 CPU cores, so it should fit comfortably in memory when indexed.
I have millions of ids corresponding to the PK in a text file that I want to throw against the database, returning
bpe if the record exists and returning "default" otherwise. My file for doing this is:
import sqlite3 def execute_query(cur, ids): res = cur.execute("select id, bpe from bpe_lookup where ids in (%s)" % ("?," * len(ids))[:-1], ids) # create mapping of found ids to bpe and print for each id, # print "default" if it wasn't in the db lookup = dict(res) for id_ in ids: print(lookup.get(id_, "default")) if __name__ == "__main__": conn = sqlite3.connect("my.db") cur = conn.cursor() # execute queries with 1k ids at a time batch_size = 1024 with open("ids.txt") as ids: lines =  for line in ids: lines.append(line) if len(lines) >= batch_size: execute_query(cur, lines) lines =  if len(lines) > 0: execute_query(cur, lines)
My thought is that batching reads will probably help, but I'm not sure. There are also very likely other optimizations that I'm missing. I'm not particularly interested in style advice unless it impacts performance. I'm only getting hundreds of KBs per second of writes from stdout so clearly I'm doing something very wrong on the read side. Please help. :-)