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()
        '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

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

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?


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
  • \$\begingroup\$ I have a number of questions about your code at a higher level. What's an Rsid, and why might they be duplicated? Also, why can multiple rsids live at the same chromosome/position? What are the chances of this kind of duplication occurring? \$\endgroup\$
    – aghast
    Apr 22, 2020 at 22:07
  • \$\begingroup\$ An rsID is intended to be a unique identifier for a genetic variant. Due to a variety of historical/technical/practical reasons, sometimes the record for a particular variant will have multiple rsIDs assigned, separated by semicolons. Although forbidden by the VCF specification, sometimes it's difficult to represent a genetic variant in a single record, and thus a variant and its rsID will appear in multiple records. This typically doesn't lead to any problems for rsidx, since the duplicated records all have the same coordinate on the chromosome. \$\endgroup\$ Apr 23, 2020 at 0:58
  • \$\begingroup\$ What happens if you open a database in ":memory:" instead of a file? Does the speed improve any? (IOW: is the problem IO-related?) \$\endgroup\$
    – aghast
    Apr 23, 2020 at 1:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.