Optimizing hundreds of millions of SQLite database inserts in Python

Question

Some background

Feel free to skip this if you're not interested.

There is a very popular bioinformatics program called tabix that permits interval-based searches for genetic variants in VCF files. To list all variants from position 100000 to 150000 on chromosome 1, one would invoke tabix db.vcf chr1:100000-150000.

I created a program named rsidx that permits similar queries, but using variant identifiers (rsIDs) instead of chromosome coordinates. rsidx index creates an index, and rsidx search performs the search. Under the hood, rsidx index simply creates an SQLite database with a mapping of rsIDs to chromosome coordinates. Once the index is constructed, rsidx search returns the coordinates associated with a variant, and then uses tabix to retrieve the variant at that coordinate.

The code

The following code is used to populate an SQLite database, mapping each rsID to a chromosome coordinate. vcfstream is a generator that scans an input file and retrieves the relevant data to populate the database.

The full code is here.

def index(dbconn, vcffh, cache_size=None, mmap_size=None, logint=1e6):
    c = dbconn.cursor()
    c.execute(
        'CREATE TABLE rsid_to_coord ('
        'rsid INTEGER PRIMARY KEY, '
        'chrom TEXT NULL DEFAULT NULL, '
        'coord INTEGER NOT NULL DEFAULT 0)'
    )
    if cache_size:
        c.execute('PRAGMA cache_size = -{:d}'.format(cache_size))
    if mmap_size:
        c.execute('PRAGMA mmap_size = {:d}'.format(mmap_size))  # bytes
    dbconn.commit()

    vcfstream = parse_vcf(vcffh, updateint=logint)
    c.executemany('INSERT OR IGNORE INTO rsid_to_coord VALUES (?,?,?)', vcfstream)
    dbconn.commit()

The problem

Often, the input files contain hundreds of millions of records. Obviously I expect it to take some time to populate the database, but sometimes the performance seems extremely slow. I've done a lot of searching found several recommendations for improving the performance of insert-intensive tasks.

• increasing the cache size
• increasing the mmap size
• disabling syncs (c.execute('PRAGMA synchronous = 0'))
• enabling write-ahead logging (c.execute('PRAGMA journal_mode = wal'))
• wrapping the inserts in BEGIN TRANSACTION; and COMMIT;

I've tried several combinations of these recommendations, but they don't seem to have much effect on overall performance. Should I chalk this up to the size of the input, or is there something I'm missing here that can substantially improve performance?

Example

If you're looking for a real-world example to test on, you can try this.

pip install git+https://github.com/bioforensics/rsidx
wget -O dbSNP.vcf.gz https://ftp.ncbi.nlm.nih.gov/snp/organisms/human_9606_b151_GRCh37p13/VCF/All_20180418.vcf.gz
rsidx index dbSNP.vcf.gz dbSNP.rsidx