Add SQLite3 backend for performance Improvement

[DhanoHacks]

Hi team,

We (me along with @saketkc) are currently experimenting with using a SQLite3 backend instead of Pandas since it tends to be faster for very large datasets (~GBs of data). It is still at a testing stage but we have implemented the following features:

1. Convert from GTF format to SQLite3 database
2. Convert from GFF3 format to SQLite3 database
3. Convert from SQLite3 database to Pyranges object
4. Perform aggregate queries on genomic interval data: Count exons for each gene, calculate total exon length for each gene, determine the gene with the most transcripts
5. Perform interval queries on genomic interval data: Merge exons, Find overlaps between two datasets, subtract one dataset from another

Here are the results from our tests on a personal laptop and a computing cluster, on the datasets Homo_sapiens.GRCh38.112.chr.gtf, gencode.vM36.annotation.gtf, and Arabidopsis_thaliana.TAIR10.60.gff3:

PC results

Cluster results

Overall, we are seeing lower computational times across all aggregate and interval queries, at the cost of slightly more processing time for conversion from GTF/GFF3 to SQLite3 database (though that is also not a major issue since SQLite3 database is persistent on disk, and hence, file processing needs to be done only once and not every time someone wants to re-run their analysis).

Would pyranges be interested in incorporating this? If so, I am happy to make a PR.

[endrebak]

I will have time to read the code next week. I think the idea looks great.

I know I do not want to have a strict dependency on SQLite, but it could be an add-on package like we have for bam reading etc.

Are you able to do fast interval queries with SQL? I have tried that (with duckdb) and overlap operations were really slow

[endrebak]

By the way, we have a new version of pyranges out now in alpha: https://github.com/pyranges/pyranges_1.x If we do include it is going to be there.

It is going to be mostly the same except it is not a custom object but a pure pandas subclass. And the operations are the fastest of all out there, the new rust code is insanely performant. And the code quality is at the opposite end of the scale.

[endrebak]

@marco-mariotti

[endrebak]

But if you have fast algorithms for genome operations in SQL this is worthy of a paper itself I think? If you have an SQL backend for every operation in PyRanges you should really try to publish it IMO

[endrebak]

Come to think of it. I think that it would not be wise to include it in pyranges for two reasons:

• I have no way of maintaining it.
• I want to keep things separate for different backends. I've been considering adding polars support but I think that should go in an entirely different library. It would be the same for SQL really. Even if the API is the same.

But I would encourage you to write your own library - I'd link to it. I do not think the selling point would be speed (compared to pyranges 1x) but the possibility of using data too big to fit in memory.

[DhanoHacks]

To answer your question "how am I doing fast algorithms for genome operations in SQL", I read the source code for pyranges, and used the same core functionality, just that instead of applying the functions on the already stored pandas dataframes for each chromosome, I query the dataframe in real time from the SQL database (using ray for parallel computation for each chromosome)
I haven't benchmarked it against Pyranges 1x, but I think I can use the same method to rewrite the SQL functionality for Pyranges 1x as well.

[DhanoHacks]

Apologies, I was on my phone and accidentally closed the issue.