# Serious group-by performance issue with Query.jl

**URL:** <https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070>\
**Category:** Data\
**Created:** [September 23, 2019, 12:38pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070 "2019-09-23T12:38:40Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 23, 2019, 12:38pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/1 "2019-09-23T12:38:40Z")

</div>

Wanted to raise a performance issue at Query.jl but got directed here. I think Query.jl group-by is really inefficient and that’s why I never warmed to it. I use FileIO.jl but never Query.jl due to performance issues. E.g.

```julia
using Query
@time qa = a |>
  @groupby(_.Column1) |>
  @map({Count = length(_)}) |>
  DataFrame;

```

to _71 seconds_ and the same operation in DataFrames.jl is only _1.3 seconds_, see below

```julia
using DataFramesMeta

@time by(a, :Column1, :Column1 => length)

```

The dataset I used is from here.

[https://docs.rapids.ai/datasets/mortgage-data](https://docs.rapids.ai/datasets/mortgage-data)

Can I understand, if Query.jl’s group-by performance can be improved? I am asking for possibility. I wrote [this post more than 1 year ago](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313)

 ![image](https://global.discourse-cdn.com/julialang/original/3X/f/f/ffb0dae61e81ef89b9b592b9524660f2049f18ce.png)

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [September 23, 2019, 4:07pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/2 "2019-09-23T16:07:21Z")

</div>

Which dataset exactly are you using from that site?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 23, 2019, 9:40pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/3 "2019-09-23T21:40:43Z")

</div>

Any. Query.jl will have poor performance relative to DataFrames.jl on large datasets

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [September 23, 2019, 9:53pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/4 "2019-09-23T21:53:34Z")

</div>

Can you still tell me which file you tried that generated the results you showed above and on which column you tried to group? I’m happy to help you write that query in a better way (the way it is written right now is far from ideal), and also explain where we are in general with performance. But I’d like to try it out before I write a response here.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 23, 2019, 10:01pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/5 "2019-09-23T22:01:42Z")

</div>

Download the 7 year dataset. Unzip it and you will a performance folder. Inside there are many files, choose the largest one which is 2004Q3.

For a start, just download the 1 year dataset, go to the performance folder once unzipped, read any of the files in without header and with delim = ‘|’. Then run my code as presented. It’s running group-by Column1, which is the actual name of the column

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 23, 2019, 11:36pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/6 "2019-09-23T23:36:40Z")

</div>

Could you try LightQuery? The corresponding code would be something like:

```julia
using LightQuery
@name @> a |>
    named_tuple |>
    Rows |>
    Group(By(_, :Column1)) |>
    over(_, @_ (Count = length(value(_)))) |>
    make_columns

```

if pre-sorted and

```julia
using LightQuery
@name @> a |>
    named_tuple |>
    Rows |>
    order(_, :Column1)) |>
    Group(By(_, :Column1)) |>
    over(_, @_ (Count = length(value(_)))) |>
    make_columns

```

if not

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 23, 2019, 11:45pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/7 "2019-09-23T23:45:48Z")

</div>

> [@bramtayl](#):
>
> using LightQuery @name @\> a |\> named\_tuple |\> Rows |\> Group(By(_, :Column1)) |\> over(_, @\_ (Count = length(value(\_)))) |\> make\_columns

Well, it’s also very slow. And failed

 ![image](https://global.discourse-cdn.com/julialang/original/3X/e/f/ef39715df011bbbe4c45f9b5b128b33945c20609.png)

Also, I don’t get LightQuery. The syntax feels verbose, e.g. `Rows`, `make_columns`.

Also, github README is the first thing I look at. But there is no information on LightQuery.jl there. It doesn’t tell me anything about what it’s for and how to use it.

In general, I think dplyr(LINQ)-like should really focus on performance, especially group-by.

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 24, 2019, 1:29pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/8 "2019-09-24T13:29:52Z")

</div>

There’s a link to the documentation from the readme…I’ll take a look and try work out the bug

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 24, 2019, 1:39pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/9 "2019-09-24T13:39:44Z")

</div>

Sorry missing a comma. Could you try this?

```julia
using DataFrames
a = DataFrame(Column1 = [1, 1, 1, 2, 2, 3, 3], Column2 = [1, 1, 1, 2, 2, 3, 3])

using LightQuery 
@name @> a |> 
    named_tuple |> 
    Rows |> 
    Group(By(_, :Column1)) |> 
    over(_, @_ (Count = length(value(_)),)) |> 
    make_columns

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 25, 2019, 10:36am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/10 "2019-09-25T10:36:44Z")

</div>

> [@bramtayl](#):
>
> using LightQuery @name @\> a |\> named\_tuple |\> Rows |\> Group(By(_, :Column1)) |\> over(_, @\_ (Count = length(value(\_)),)) |\> make\_columns

you are on 150s, twice as slow as Query.jl

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 25, 2019, 8:47pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/11 "2019-09-25T20:47:09Z")

</div>

Ok, thanks

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 26, 2019, 7:07am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/12 "2019-09-26T07:07:28Z")

</div>

@xiaodai here’s what my benchmarks show:

```julia
using DataFrames
using Query
using LightQuery
using DataFramesMeta
using Pkg
using BenchmarkTools

Column1 = vcat(
    repeat([1], 1000000),
    repeat([2], 1000000)
)

a = DataFrame(Column1 = Column1, Column2 = Column1)

println("LightQuery")
@btime @name @> a |>
  named_tuple |>
  Rows |>
  Group(By(_, :Column1)) |>
  over(_, @_ (Count = length(value(_)),)) |>
  make_columns

println("Query")
@btime a |>
  @groupby(_.Column1) |>
  @map({Count = length(_)}) |>
  DataFrame;

println("DataFramesMeta")
@btime by(a, :Column1, :Column1 => length)

```

```julia
LightQuery
  1.329 ms (33 allocations: 1.16 KiB)
Query
  51.768 ms (134 allocations: 34.01 MiB)
DataFramesMeta
  24.013 ms (150 allocations: 61.79 MiB)

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 26, 2019, 7:14am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/13 "2019-09-26T07:14:59Z")

</div>

> [@davidanthoff](#):
>
> still

Maybe try it on real-world data? [https://docs.rapids.ai/datasets/mortgage-data](https://docs.rapids.ai/datasets/mortgage-data)

There are more columns in the DataFrame and there are many more groups. Like 1 million different groups

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 26, 2019, 10:04am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/14 "2019-09-26T10:04:03Z")

</div>

@xiaodai I checked with the real data and you’re right. Do you have any idea what’s going on? I’m surprised that LightQuery can be so much faster for my benchmarks but not in real data. I’d expect that increasing the number of columns to have no effect on performance, because the only work that needs to get done is counting the length of repeated values in the first column, but it seems to make a huge difference:

```julia
using DataFrames
using Query
using LightQuery
using DataFramesMeta
using CSV
using BenchmarkTools

cd("/home/brandon")
run(`wget http://rapidsai-data.s3-website.us-east-2.amazonaws.com/notebook-mortgage-data/mortgage_2000_1gb.tgz`)
run(`tar xzvf mortgage_2000_1gb.tgz`)
cd("perf")

result = CSV.read("Performance_2000Q1.txt_0", 
  delim = '|', 
  header = Symbol.(string.("Column", 1:31)), 
  missingstrings = ["NULL", ""],
  dateformat = "mm/dd/yyyy",
  truestrings = ["Y"],
  falsestrings = ["N"]
)

result2 = result[:, 1:1]

println("LightQuery")
@btime @name @> result2 |>
  named_tuple |>
  Rows |>
  Group(By(_, :Column1)) |>
  over(_, @_ (Count = length(value(_)),)) |>
  make_columns

println("Query")
@btime result |>
  @groupby(_.Column1) |>
  @map({Count = length(_)}) |>
  DataFrame;

println("DataFramesMeta")
@btime by(result, :Column1, :Column1 => length)

```

gives results

```julia
LightQuery
  10.740 ms (35 allocations: 3.00 MiB)
Query
  523.135 ms (1562450 allocations: 250.19 MiB)
DataFramesMeta
  273.793 ms (163 allocations: 349.35 MiB)

```

---

<div class="post-metadata">

**Author:** ![bramtayl](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bramtayl/32/3614_2.png) [@bramtayl](https://discourse.julialang.org/u/bramtayl)\
**Post date:** [September 26, 2019, 10:14am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/15 "2019-09-26T10:14:02Z")

</div>

But given that for one column LightQuery is so much faster, and I’ve made meticulous care to make sure that all column-wise operations are type-stable, it seems like Base is just not making the optimizations I’d hope it would make. Some combination of not inlining and allocating views, maybe?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 26, 2019, 10:54am UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/16 "2019-09-26T10:54:10Z")

</div>

group-by is generally quite hard. I wrote a few articles about them.

---

<div class="post-metadata">

**Author:** ![DoktorMike](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/doktormike/32/2736_2.png) [@DoktorMike](https://discourse.julialang.org/u/DoktorMike)\
**Post date:** [September 26, 2019, 3:06pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/17 "2019-09-26T15:06:01Z")

</div>

Have you tried R’s dplyr on this data? If so how does that fare?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 26, 2019, 3:13pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/18 "2019-09-26T15:13:25Z")

</div>

Good question. Just tried.

DataFrames.jl is similar to dplyr but slower than data.table which has some optimization

 ![image](https://global.discourse-cdn.com/julialang/original/3X/b/a/ba28a62940f35eca95c2b1391c18f00aca0a7384.png)

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 26, 2019, 3:13pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/19 "2019-09-26T15:13:59Z")

</div>

That’s why I’ve been saying Julia’s data ecosystem now is pretty good.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [September 26, 2019, 5:11pm UTC](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070/20 "2019-09-26T17:11:30Z")

</div>

If you are iterating named tuples, I suspect there can be two possible issues:

- too many fields and the compiler gives up
- named tuple mixed with `missing` don’t behave well at the moment

[Next page](https://discourse.julialang.org/t/serious-group-by-performance-issue-with-query-jl/29070.md?page=2)