# Optimise querying code

**URL:** <https://discuss.hail.is/t/optimise-querying-code/1656>\
**Category:** Hail Query & hailctl\
**Created:** [September 14, 2020, 11:28am UTC](https://discuss.hail.is/t/optimise-querying-code/1656 "2020-09-14T11:28:19Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![nonchev](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nonchev](https://discuss.hail.is/u/nonchev)\
**Post date:** [September 14, 2020, 11:28am UTC](https://discuss.hail.is/t/optimise-querying-code/1656/1 "2020-09-14T11:28:19Z")

</div>

Hello,

I tried several different approaches for my project firstly with MatrixTable, but the performance was bad.  
Now I am trying to work only with Tables. My pipeline has 2 steps.

step 1: injest vcf, i.e import vcf files into Tables and store them in a folder(table per vcf approach)  
step 2: querying Tables to get GT, GQ or DP fields for given set of variants and samples:

[code.txt](https://discuss.hail.is/uploads/short-url/ktccqNZ72V4H7pYSfcZyE1ZpJLR.txt) (1.6 KB)  
or

 ![Screenshot 2020-09-13 at 01.22.37](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/3c3522ee5a95e00e8c9c5c0128659c521773a2d5.jpeg)

Can you look into this code snippet and give me an advice/suggestion how can I improve/optimise the code, because I am disappointed by its performance?  
note: I tried to add checkpoints or .persist() but the different wasn’t significant, but maybe they were on the wrong place…  
Thanks in advance!

Cheers,

---

<div class="post-metadata">

**Author:** ![johnc1231](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/johnc1231/32/286_2.png) [@johnc1231](https://discuss.hail.is/u/johnc1231)\
**Post date:** [September 14, 2020, 1:56pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/2 "2020-09-14T13:56:47Z")

</div>

I don’t understand what you’re doing yet. You make a variant table from pandas, then a sample table from pandas, then you join them together? Are either of these tables keyed? If not, I’m pretty sure `join` is just going to do a cartesian product, which doesn’t seem like what you’d want?

---

<div class="post-metadata">

**Author:** ![tpoterba](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/tpoterba/32/61_2.png) [@tpoterba](https://discuss.hail.is/u/tpoterba)\
**Post date:** [September 14, 2020, 2:02pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/3 "2020-09-14T14:02:22Z")

</div>

Knowing the input here would help a lot. What is var\_df?

I think one of the core problems, though, is that this pipeline scales with the square of the number of samples. If you find yourself using Python for loops to iteratively filter and collect pieces of a Hail table or matrix table, this is a red flag. the line

```auto
res_np.append(np.array(res_table.filter(res_table.s==sample)[field].collect()).reshape(-1, 1))

```

Requires a full pass over the table to filter to rows that have the sample of interest. You’re doing this once per sample, hence quadratic scaling.

The way you’re iterating to build a union table is also concerning, I think – there may be a better way. What are these ht\_vcf inputs? These are from VCFs that represent the same samples, but different variants?

---

<div class="post-metadata">

**Author:** ![nonchev](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nonchev](https://discuss.hail.is/u/nonchev)\
**Post date:** [September 14, 2020, 2:19pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/4 "2020-09-14T14:19:32Z")

</div>

var\_df is the input from the user: dataframe with variants  
 ![Screenshot 2020-09-14 at 17.06.18](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/f1009d9a5a9146dbae86cba7b8d1868806b99c88.png)

samples:  
 ![Screenshot 2020-09-14 at 17.07.52](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/8c339b6858fa09872c18da35ef5b64bceb468e4c.png)

here I am building the cartesian product between between samples and variants:

 ![Screenshot 2020-09-14 at 17.10.02](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/de6fb1999df62cd081b9dbe6c3520a8c697b3edc.png)

ht\_vcf - a vcf file which can contain variants for single or multiple samples. ht\_paths are the different vcf files which contain information for my samples of interest.

 ![Screenshot 2020-09-14 at 17.12.45](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/ea447049a88e53c702fcf53dac333fec8cc2b0f9.png)

ht\_n :

 ![Screenshot 2020-09-14 at 17.17.02](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/3fe15b92aba9c98a7ef6259ed91d5ee08862c537.png)

---

<div class="post-metadata">

**Author:** ![nonchev](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nonchev](https://discuss.hail.is/u/nonchev)\
**Post date:** [September 14, 2020, 2:25pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/5 "2020-09-14T14:25:44Z")

</div>

Story:  
A user gives his variants and samples of interest, and I want to query my vcf files(stored as tables) and return the GT,DP or GQ field (0 if the variant is not present for this sample) (get\_geneotype calls \_get(…, ‘GT’))

 ![Screenshot 2020-09-14 at 17.24.36](https://canada1.discourse-cdn.com/flex036/uploads/hail/original/1X/1540be6d0d1ffe1e952691e41aa14c484ffaa813.png)

---

<div class="post-metadata">

**Author:** ![danking](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/danking/32/43_2.png) [@danking](https://discuss.hail.is/u/danking)\
**Post date:** [September 14, 2020, 2:40pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/6 "2020-09-14T14:40:00Z")

</div>

Computing the Cartesian product of samples and variants will not perform well. It looks like you’re trying to produce a numpy array of genotypes. You can do that with this:

```auto
import hail as hl
import numpy as np

mt = hl.import_vcf(...)
mt = mt.filter_cols(hl.is_defined(ht_samples[mt.s]))
ht = ht.key_by(locus=hl.locus(ht.chrom, ht.pos), alleles=[ht.ref, ht.alt])
mt = mt.filter_rows(hl.is_defined(ht[mt.row_key]))

mt = mt.select_entries(GT=mt.GT.n_alt_alleles())
mt_as_list = mt.localize_entries('entries_as_hail_array').entries_as_hail_array.GT.collect()
numpy_gts = np.array(mt_as_list)

```

You usually can’t combine multiple VCFs unless they’re gVCFs.

---

<div class="post-metadata">

**Author:** ![johnc1231](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/johnc1231/32/286_2.png) [@johnc1231](https://discuss.hail.is/u/johnc1231)\
**Post date:** [September 14, 2020, 2:57pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/7 "2020-09-14T14:57:26Z")

</div>

One more thing though: If you are going to be querying these same files a lot, you’re likely going to want to store them as a `MatrixTable.` `hl.read_matrix_table` is faster than `import_vcf`.

---

<div class="post-metadata">

**Author:** ![nonchev](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nonchev](https://discuss.hail.is/u/nonchev)\
**Post date:** [September 14, 2020, 3:57pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/8 "2020-09-14T15:57:11Z")

</div>

So in the end of the day your point is that in my case where I want to query multiple vcfs(They are not gvcfa) in order to combine them and return the GT field, hail is not the optimal solution?

Hails conception is to work and gain maximal performance with a single vcf file or gvcf?

---

<div class="post-metadata">

**Author:** ![tpoterba](https://yyz2.discourse-cdn.com/flex036/user_avatar/discuss.hail.is/tpoterba/32/61_2.png) [@tpoterba](https://discuss.hail.is/u/tpoterba)\
**Post date:** [September 14, 2020, 4:06pm UTC](https://discuss.hail.is/t/optimise-querying-code/1656/9 "2020-09-14T16:06:27Z")

</div>

Hail is not designed to have good performance in the particular case your code implements. I think it’s possible to design a fast solution here, however, by combining the VCFs first, and querying a matrix table that. contains all samples.