3
\$\begingroup\$

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. :-)

\$\endgroup\$
7
  • \$\begingroup\$ Is the extra s in where ids in a typo in the original code, or a copy/paste error in the question? \$\endgroup\$ Jul 31 at 7:52
  • \$\begingroup\$ Oops, copy paste -- sorry. Should just be "id" \$\endgroup\$
    – erip
    Jul 31 at 10:56
  • \$\begingroup\$ I believe the where id in (...) construct does not scale well to long lists. If doing this in C, I would start by using a prepared statement of the form where id = ? and just run it repeatedly for each id. \$\endgroup\$
    – TripeHound
    Jul 31 at 11:17
  • \$\begingroup\$ Please do not edit the question, especially the code, after an answer has been posted. Changing the question may cause answer invalidation. Everyone needs to be able to see what the reviewer was referring to. What to do after the question has been answered. \$\endgroup\$
    – pacmaninbw
    Jul 31 at 12:22
  • \$\begingroup\$ The edit was fixing a fat-finger typo that results in a syntax error as posed... I think semantics-preserving edits should be fairly permissible... especially when current reviews do not refer to the specifics. \$\endgroup\$
    – erip
    Jul 31 at 13:39

1 Answer 1

3
\$\begingroup\$

At this scale I would not trust sqlite to perform reasonably, and would be motivated to switch to PostgreSQL. But in the more immediate term:

Rather than load your IDs from a file into Python's memory and run a post-query filtration step, I would sooner load those IDs into a temporary table where they will be in SQLite's memory and then do a join. This is what joins are for, and the database should be better at it than Python is.

\$\endgroup\$
1
  • \$\begingroup\$ Thanks very much for this advice! I had this same idea as I was walking to clear my mind... \$\endgroup\$
    – erip
    Jul 31 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.