Benchmark

Benchs goals

We did some benchmarks to choose the best technologies that fit to our needs:

• strategy for the database: SQL vs No-SQL
• database technology: PostgreSQL, MongoDB, SQLLite...
• database connector: SQLAlchemy, Mongoengine, pgxx...
• data model for the database
• vcf file parser: pySAM, pyVCF, vcflib...

Note that this project is free and open source. Only free technology have been tested (sorry SQL Server and others...).

Benchs conclusion

• strategy for the database: To be decided
• database technology: To be decided
• database connector: To be decided
• data model for the database: To be decided
• vcf file parser: pySAM is the faster parser we tried. It also very simple to use and use Python.

Doing these benchs helped us to choose the best technologies that fit to our needs but also highlighted that our code that wraps the parsing of the vcf file done by pySAM is also strategic. In Python, some operations are more costly than others. And as we are manipulating heavy files (some VCF files will be very heavy (> 1Go)), it's important to keep these optimisations in mind.

• Python Speed
• Python Performance Tips

---

VCF file Parser

Name	Techno	Speed	Friendly
pyVCF	Python	⭐⭐	⭐⭐⭐
pySAM	Python	⭐⭐⭐	⭐⭐⭐
vcflib	C++	⭐	⭐⭐

---

Database

Techno	Strategy
PostgreSQL 9.5	Link (SQL) and/or NoSql (JSON and ARRAY fields)
MongoDB	NoSql

---

ORM / DAL

Techno	Speed	Friendly
SQLAlchemy (Postgres/Python) (ORM)	⭐⭐	⭐
SQLAlchemy (Postgres/Python) (raw)	⭐⭐⭐	⭐⭐⭐
Mongoengine (MongoDB/Python)(ORM)	⭐⭐	⭐⭐⭐
pgxx (Postgres/C++) (raw)	⭐⭐⭐	⭐⭐⭐

---

Performance by config (Sample/Variant model)

4 different ways to store data have been tested.

• Config 1️⃣ (poc pg 006)
  • Model: Sample & Variant
  • SQLAlchemy using raw sql query
  • Parsing with PySam on main thread
  • SQL query exec asynch on multithread
• Config 2️⃣ (poc pg 007)
  • Model: Sample & Variant & SampleVariant (id on chr, pos, ref, alt)
  • SQLAlchemy using raw sql query
  • Parsing with PySam on main thread
  • SQL query exec asynch on multithread
• Config 3️⃣ (poc pg 008)
  • Model: Sample & Variant & SampleVariant (id on PK id)
  • SQLAlchemy using raw sql query
  • Parsing with PySam on main thread
  • SQL query exec asynch on multithread
• Config 4️⃣ (poc mongo)
  • Model: Sample & Variant
  • SQLAlchemy using raw sql query
  • Parsing with PySam on main thread
  • SQL query exec asynch on multithread

On this 4 config we monitored the execution of the following tasks :

• Import: The import of heavy vcf file (whole genome in vcf.gz format ~ 1Go by file)
• DB size/entries: the evolution of the size of the database after each import
• The execution of several (big) queries:
  • Req1: Select all distinct variant
  • Req2: Count variant by sample
  • Req3: Select variant of sample 1, on the chromosome 5 and with an alt="A"
  • Req4: How many sample having same variant
  • Req5: By sample, compute ratio transition/transversion

	Import	DB size/entries	Req1	Req2	Req3	Req4	Req5
1️⃣	⭐⭐⭐	⭐⭐⭐	💩	⭐	⭐⭐⭐	💩	⭐
2️⃣	⭐⭐	⭐⭐	⭐⭐⭐	⭐	⭐⭐⭐	💩💩	💩💩
3️⃣	⭐⭐	⭐⭐	⭐⭐⭐	⭐	⭐⭐	💩💩	💩
4️⃣	_	⭐⭐⭐	_	_	_	_	_
(see digital values in the Benchs statistics section below)

---

Performance by config (Variant/Annotation model)

We also tested 3 ways to do a simple "variant annotation": for the 10000 first variants of the sample 1, retrieve the list of impacted genes (via refGene)

• Req1: Join done by comparing pos with txstart and txend
• Req2: Join done by using a precomputed range position field in refGene table
• Req3: Annotation already precomputed in the variant table (Note that there is no test for the config 4️⃣ as there is no need to "join" with No-SQL database like mongo)

Config	Req1	Req2	Req3
1️⃣	⭐⭐	⭐⭐	⭐⭐⭐
2️⃣	⭐⭐	⭐⭐	⭐⭐⭐
3️⃣	⭐	⭐⭐	⭐⭐⭐
(see digital values in the Benchs statistics section below)

---

Benchs statistics

Config 1️⃣

vcf	Import (s)	DB size (MB)	DB entries	Req1 (s)	Req2 (s)	Req3 (s)	Req4 (s)	Req5 (s)
WG1.vcf.gz 878.7Mo	747	2735	20676686	210	5	0.265	21	6
WG2.vcf.gz 936.6Mo	773	5665	42710407	498	10	0.389	41	12
WG3.vcf.gz 879.7Mo	748	8542	63368679	806	15	0.577	373	18
WG4.vcf.gz 865.2Mo	744	11256	83871824	1133	20	0.723	511	24
WG5.vcf.gz 881.2Mo	782	14024	104450307	1494	25	2.268	641	30

Config 2️⃣

vcf	Import (s)	DB size (MB)	DB entries	Req1 (s)	Req2 (s)	Req3 (s)	Req4 (s)	Req5 (s)
WG1.vcf.gz 878.7Mo	798	3932	27084510	0.5	4	0.17	101	100
WG2.vcf.gz 936.6Mo	853	7916	52291841	0.8	8	0.154	225	225
WG3.vcf.gz 879.7Mo	777	11326	74787990	0.9	12	0.323	327	341
WG4.vcf.gz 865.2Mo	789	14672	96656378	1.0	15	0.167	447	209
WG5.vcf.gz 881.2Mo	780	17309	118361298	1.1	19	0.49	564	262

Config 3️⃣

vcf	Import (s)	DB size (MB)	DB entries	Req1 (s)	Req2 (s)	Req3 (s)	Req4 (s)	Req5 (s)
WG1.vcf.gz 878.7Mo	1028	3327	27084510	0.5	4	3.5	135	14
WG2.vcf.gz 936.6Mo	943	6174	52291841	0.9	8	3.4	284	27
WG3.vcf.gz 879.7Mo	1155	8661	74787990	1.0	11	3.4	432	40
WG4.vcf.gz 865.2Mo	844	11059	96656378	2.3	16	26.4	579	48
WG5.vcf.gz 881.2Mo	853	13435	118361298	2.1	19	27.9	729	60

Config 4️⃣

vcf	Import (s)	DB size (MB)	DB entries	Req1 (s)	Req2 (s)	Req3 (s)	Req4 (s)	Req5 (s)
WG1.vcf.gz 878.7Mo	_	_	_	_	_	_	_	_
WG2.vcf.gz 936.6Mo	_	_	_	_	_	_	_	_
WG3.vcf.gz 879.7Mo	_	_	_	_	_	_	_	_
WG4.vcf.gz 865.2Mo	_	_	_	_	_	_	_	_
WG5.vcf.gz 881.2Mo	_	_	_	_	_	_	_	_

---

Join strategies performances for annotations

Config	Req1 (s)	Req2 (s)	Req3 (s)
1️⃣ - 10000	16.2	10.6	0.2
1️⃣ - 1000	1.1	0.8	0.05
2️⃣ - 10000	16.3	10.8	0.2
2️⃣ - 1000	1.0	0.7	0.05
3️⃣ - 10000	22.6	9.5	0.3
3️⃣ - 1000	1.6	0.7	0.05