# Julia performs poorly on group-by benchmarks

**URL:** <https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476>\
**Category:** Data\
**Tags:** performance\
**Created:** [October 18, 2018, 1:29am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476 "2018-10-18T01:29:08Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 9, 2018, 8:11pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/21 "2018-12-09T20:11:38Z")

</div>

These shouldn’t have any effect, anyway the time taken for both the first and second run are reported.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [December 9, 2018, 8:14pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/22 "2018-12-09T20:14:22Z")

</div>

> [@nalimilan](#):
>
> the time taken for both the first and second run are reported

Yes, but only on plots. The number reported is maximum of the two. We could consider convincing Jan Górecki to report the minimum 😄.

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [December 9, 2018, 11:42pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/23 "2018-12-09T23:42:58Z")

</div>

Unfortunately, CategoricalArrays come with a bunch of features that are not always welcome (for instance no natural ordering). I don’t think users should have to work with CategoricalArray if they only want to have efficient grouping. I hope DataFrames support for PooledArrays gets better in the future!

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 11, 2018, 4:51am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/24 "2018-12-11T04:51:34Z")

</div>

Hello,  
I just published results after using `categoricals=0.05`, exact differences to previous run `categoricals=false` can be examined with following R query:

```r
fs = function(x) factor(x, levels=unique(x))
d = data.table::fread("https://h2oai.github.io/db-benchmark/time.csv")
d[task=="groupby" & solution=="juliadf"
  ][batch==1544072228, batch:=1
    ][batch%in%c(1544286223, 1544333533), batch:=2
      ][batch%in%c(1,2)
        ][order(batch), tail(.SD, 2L), by=.(data=fs(data), question=fs(question), run), .SDcols=c("batch","time_sec")
          ][, data.table::dcast(.SD, data+question+run ~ batch, value.var="time_sec", )
            ][, diff:=sprintf("%2.2f%%", ((`2`-`1`)/`1`)*100)
              ][, print(.SD, topn=1e3)] -> nul

```

here is head the results. for first dataset, negative diff means speed up:

```julia
               data question run 1 2 diff
  1: G1_1e7_1e2_0_0 sum v1 by id1 1 4.542 4.274 -5.90%
  2: G1_1e7_1e2_0_0 sum v1 by id1 2 0.791 0.278 -64.85%
  3: G1_1e7_1e2_0_0 sum v1 by id1:id2 1 1.854 2.777 49.78%
  4: G1_1e7_1e2_0_0 sum v1 by id1:id2 2 1.776 2.900 63.29%
  5: G1_1e7_1e2_0_0 sum v1 mean v3 by id3 1 3.811 4.019 5.46%
  6: G1_1e7_1e2_0_0 sum v1 mean v3 by id3 2 3.234 3.254 0.62%
  7: G1_1e7_1e2_0_0 mean v1:v3 by id4 1 1.807 1.857 2.77%
  8: G1_1e7_1e2_0_0 mean v1:v3 by id4 2 1.000 1.004 0.40%
  9: G1_1e7_1e2_0_0 sum v1:v3 by id6 1 3.301 3.455 4.67%
 10: G1_1e7_1e2_0_0 sum v1:v3 by id6 2 2.934 3.078 4.91%

```

Three "Benchplot"s you can see on the report page are not everything we have there. Please scroll down, below the plot there is a table (and links to more benchplots). You should be able to get some useful information from there also. For example when data have k factor equal to 10, question3 and question5 are terribly slow. This cannot be easily noticed when looking at default benchplot that presents k=100. Please note that table shows only first run timing, so it may suffer due to compiling query. You can get second run timings using raw timings data from [https://h2oai.github.io/db-benchmark/time.csv](https://h2oai.github.io/db-benchmark/time.csv)

> [@ValdarT](#):
>
> And the results are in. Not too good on the performance side but the new syntax is lovely and it’s nice to see that it could also handle the 50GB file (unlike, for example, Pandas)

Unless you look at k factor equals to 2. Julia fails with `ERROR: LoadError: OutOfMemoryError()` when attempting to execute first question, another time julia process is killed by OS when doing second question. This case is pretty complex, dplyr OOM on question3. `data.table` manage to finish successfully all questions, but R’s system calls to `ps -o RSS` to measure memory are OOM after q3.

> [@bkamins](#):
>
> The number reported is maximum of the two. We could consider convincing Jan Górecki to report the minimum 😄.

If we would put minimum value then we need to align that value on X axis using different value, otherwise it will overlap on the longer bar.

> [@matthieu](#):
>
> I don’t think users should have to work with CategoricalArray if they only want to have efficient grouping. I hope DataFrames support for PooledArrays gets better in the future!

Of course strings could be handled more efficently behind the scene, but we want to present fastest way so categoricals are preferred, even if it is an overhead in preparing data. Additionally as I mentioned in [group on factor column(s) #20](https://github.com/h2oai/db-benchmark/issues/20).

> As we are unable to guarantee that a solution won’t use categoricals internally for character variables the best seems to use fastest approach for each solution. Which will probably be categorical type.

I hope categoricals will be fixed soon, and comparison will be fair, because now as pointed out by @nalimilan other tools uses categoricals and julia (at least partially) strings. Additionally adding [user defined functions #57](https://github.com/h2oai/db-benchmark/issues/57) will highlight advantages of Julia over other solutions.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 11, 2018, 9:08am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/25 "2018-12-11T09:08:19Z")

</div>

Thanks @jangorecki. To sum up the results, I see one large improvement and two large regressions. The regressions are for grouping on two columns, where we don’t use the optimized method yet (or only in part). I wouldn’t expect a regression, though, so I’ll have to investigate.

BTW, looking at the benchmarks locally, a large part (or even the majority) of the time is spent computing the sum/mean for each group. I suspect we need to add a special method for `sum`/`mean` to match the performance of other languages.

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 11, 2018, 12:02pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/26 "2018-12-11T12:02:48Z")

</div>

Is it possible to pre-compile those `by()` expressions not touching input data, but for examples knowing data types of input? If so, we should do it to avoid compile penalty during benchmark. The input of benchmark are data, not their schema.

---

<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:** [December 11, 2018, 12:20pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/27 "2018-12-11T12:20:40Z")

</div>

A hacky way is to pass in just one row of data so that it compiles.

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 11, 2018, 12:30pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/28 "2018-12-11T12:30:09Z")

</div>

Using a row of input data is not acceptable, but maybe we can build single row (or zero rows even better) dataframe of the same types.  
If there is an open issue for functionality to pre-compile function call please link, so I can subscribe and track status.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 11, 2018, 1:03pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/29 "2018-12-11T13:03:05Z")

</div>

You can call `precompile` with the type of arguments, but I’m not sure how to specify keyword arguments. More importantly, if some code paths cannot be inferred statically (which is the case for some calls I think, since we don’t care about type stability in the wrapper, only in the inner loop), `precompile` will miss them AFAICT. In general the simplest approach is to use a toy data frame as @xiaodai suggests (one row is enough, but if it’s empty not all code will be precompiled).

That said, I’m not sure it’s completely fair to exclude the compilation time from benchmark results at this point, given that we don’t store generated code for future sessions (except if you include it in the sysimage, but that’s not the typical situation). As long as the timings for both the first and the second run are shown, I think it’s fine.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [December 11, 2018, 1:04pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/30 "2018-12-11T13:04:46Z")

</div>

> [@nalimilan](#):
>
> As long as the timings for both the first and the second run are shown, I think it’s fine.

This is exactly what I think is best - to show both timings as bars **and as numbers**.

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 11, 2018, 4:20pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/31 "2018-12-11T16:20:02Z")

</div>

> [@bkamins](#):
>
> show both timings as bars **and as numbers**.

Added to [Include timings at the end of each bar #31](https://github.com/h2oai/db-benchmark/issues/31).

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [December 11, 2018, 11:38pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/32 "2018-12-11T23:38:59Z")

</div>

How could the mean and sum computation be optimized? Does not Julia have a very optimized mean / sum already? Maybe related, an extract from data.table FAQ (I am not sure what Julia does):

> Base R’s mean accumulates in long double precision where the machine supports it (and most do) and then does a second pass through the entire vector to adjust mean(x) by typically a very small amount to ensure that sum(x-mean(x)) is minimized. What do these packages do?  
> Both data.table and dplyr currently do a single pass sum(x)/length(x) where the sum is done in long double precision. They match base in terms of long double but don’t do the second pass, currently. Feedback welcome.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 12, 2018, 7:11am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/33 "2018-12-12T07:11:36Z")

</div>

Julia’s `sum` is fast, but the most efficient way of computing by-group sums isn’t to call `sum` on the subset of rows for each group repeatedly as we currently do. Instead, we should directly allocate a vector with one entry per group, and add values to the corresponding entry. That way we can do a single pass over the data with an optimal memory access pattern.

---

<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:** [December 12, 2018, 7:23am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/34 "2018-12-12T07:23:18Z")

</div>

> [@nalimilan](#):
>
> one entry per group, and add values to the corresponding entry

Most operations fall into this paradigm which I call the reduction paradigm e.g. max, min, sum, mean can all fall into this. But things like median do not.

---

<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:** [December 12, 2018, 10:00am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/35 "2018-12-12T10:00:31Z")

</div>

It could actually be worth to expose this functionality to the user if it gets implemented to speed up `sum`: in JuliaDB it’s called `groupreduce` and supports operations from OnlineStats as well.

---

<div class="post-metadata">

**Author:** ![Juan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan/32/7657_2.png) [@Juan](https://discourse.julialang.org/u/Juan)\
**Post date:** [December 12, 2018, 10:43am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/36 "2018-12-12T10:43:33Z")

</div>

> [@nalimilan](#):
>
> Julia’s sum is fast, but the most efficient way of computing by-group sums isn’t to call sum on the subset of rows for each group repeatedly as we currently do. Instead, we should directly allocate a vector with one entry per group, and add values to the corresponding entry. That way we can do a single pass over the data with an optimal memory access pattern.

There are two options:

- One entry per group.
- As many entries per group as the original number of elements, that is, replicate the computed value.

Both things are useful. R’s datatable can do both. If you append (:=) the value to the original table as a new column you get the second approach.

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 12, 2018, 11:39am UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/37 "2018-12-12T11:39:38Z")

</div>

> [@Juan](#):
>
> There are two options:

What you are describing is the way how to return already computed aggregates. What nalimilan describes is the way to compute aggregates.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [December 12, 2018, 1:37pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/38 "2018-12-12T13:37:17Z")

</div>

DataFramesMeta does this with `transform` on a grouped DataFrame. But a fast syntax for this with DataFrames would be nice.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 12, 2018, 1:41pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/39 "2018-12-12T13:41:07Z")

</div>

Yes, this applies to all reductions, and it would make sense to expose this to users in some way (in addition to special-casing `sum` and other common reductions).

---

<div class="post-metadata">

**Author:** ![jangorecki](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jangorecki/32/6388_2.png) [@jangorecki](https://discourse.julialang.org/u/jangorecki)\
**Post date:** [December 15, 2018, 3:49pm UTC](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476/40 "2018-12-15T15:49:57Z")

</div>

Any suggestions for DataFrames off-memory storage for fast reading? I need to reduce timings and re-reading from csv is sub-optimal. Full script of reading data and queries for N=1e9 and K=10 takes 21 hours (swapping might be involved).  
Tried JLD2 and Feather but none works for this size. Serialize and deserialize is not recommended, but will it work over time if I will keep updating julia packages but not julia itself?

[Previous page](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476.md?page=1)

[Next page](https://discourse.julialang.org/t/julia-performs-poorly-on-group-by-benchmarks/16476.md?page=3)