# Speeding Up Query with a big dataset

**URL:** <https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508>\
**Category:** Data\
**Created:** [January 11, 2019, 9:37am UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508 "2019-01-11T09:37:16Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 11, 2019, 9:37am UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/1 "2019-01-11T09:37:16Z")

</div>

Hi,

I am currently processing a dataset of 31 columns and 100.000 rows. Loading the data, describe them etc… is ok however while running some Query to match some rows is very slow. 100K rows is big but still not very big (might be mistaken here).  
At first, I thought I am in Jupyter notebook and/or Jupyter lab therfore i am experimenting latency but then i run a \*.jl script and it is still the same.  
I then have started julia as julia -p 16 (in a server with 16 cores) and then run the script but still very slow.

I am currently using Query.jl to do my queries but am assuming it is going to be the same for others (need to see).

I have not played with multiprocessing on julia jet (thought it will do a bit of magic for me automatically)…

Anybody haveing a solution on how to use multiprocessing with tables/dataframes ?

My query is as follows:

@from row in df begin  
@where ((row.X\< 10 && row.Y \> 15) || (row.X \> 15 && row.Y \< 10)) && (row.O \> 10)  
@select row  
@collect DataFrame  
end

and it does not seem to be a complex one…

Elvis

---

<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:** [January 11, 2019, 11:59am UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/2 "2019-01-11T11:59:44Z")

</div>

> [@elshera](#):
>
> Hi,
> 
> I am currently processing a dataset of 31 columns and 100.000 rows. Loading the data, describe them etc… is ok however while running some Query to match some rows is very slow. 100K rows is big but still not very big (might be mistaken here).  
> At first, I thought I am in Jupyter notebook and/or Jupyter lab therfore i am experimenting latency but then i run a \*.jl script and it is still the same.  
> I then have started julia as julia -p 16 (in a server with 16 cores) and then run the script but still very slow.
> 
> I am currently using Query.jl to do my queries but am assuming it is going to be the same for others (need to see).
> 
> I have not played with multiprocessing on julia jet (thought it will do a bit of magic for me automatically)…
> 
> Anybody haveing a solution on how to use multiprocessing with tables/dataframes ?

[JuliaDB](https://juliadb.org/latest/) has support for parallel computing (see the docs) and you can use the JuliaDB “backend” via macros from [JuliaDBMeta](https://piever.github.io/JuliaDBMeta.jl/latest/). For example your query would look like:

```julia
@filter df (:X < 10 && :Y > 15) || (:X > 15 && :Y < 10) && (:O > 10)

```

Where `df` is your IndexedTable

Already on a single process this should be pretty fast (not sure why Query would be slow here to be honest, maybe the columns of the dataset are note concretely typed?).

---

<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:** [January 11, 2019, 12:49pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/3 "2019-01-11T12:49:35Z")

</div>

100,000 rows isn’t that large, I’m not sure you need multiprocessing for this. Can you quantify “slow”? Is the second run faster than the first one? The compilation cost may be significant.

---

<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:** [January 11, 2019, 4:11pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/4 "2019-01-11T16:11:58Z")

</div>

Completely agree, this seems reasonably fast on a single core as well:

```julia
julia> N = 100_000

julia> df = table((X=rand(N).*20, Y=rand(N)./20, O=rand(N).*20))
Table with 100000 rows, 3 columns:
X Y O
────────────────────────────
14.6601 0.00236458 17.2513
12.6361 0.0322326 18.9277
0.89148 0.0034154 14.4774
12.3102 0.00139073 13.7773
14.6083 0.0474375 19.7088
19.9985 0.0484838 13.7814
12.2598 0.0491725 17.5342
14.135 0.0467684 4.94136
⋮
17.374 0.00520244 2.33823
12.4243 0.0455707 8.35212
15.1256 0.0348024 19.3554
6.90056 0.0466855 17.7509
3.72646 0.0210652 7.7554
2.17125 0.0213715 10.6633

julia> @time @filter df (:X < 10 && :Y > 15) || (:X > 15 && :Y < 10) && (:O > 10) # on the second run
  0.049482 seconds (129.79 k allocations: 6.774 MiB)

```

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 11, 2019, 8:30pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/5 "2019-01-11T20:30:24Z")

</div>

I also came in the meantime to JuliaDB which seems doing the job, actually I run the same query… and it comes back within the second.  
The columns of the dataset are correctly Typed. At the moment not sure why the previous one was so slow… Hoever JuliaDB seems doing the job. Having both IndexedTable and DataFrame seem useful as DataFrame has useful functions to easily explore/summarize the data.

Thanks

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 11, 2019, 8:33pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/6 "2019-01-11T20:33:21Z")

</div>

Using JuliaDB , for this kind of dataset, which I agree is not big, was fast the same. I did not experiment a difference between laptop (single core) and server.

Slow means that I actually did not even got the answer… the Kernel was running and I had to interrupt it. I did try on multiple machines and rerun but with same result.

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 11, 2019, 8:45pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/7 "2019-01-11T20:45:53Z")

</div>

I did repeate your example and is very fast… i also did repeat again the previous one with Query.jl and it works now… I do not know what actually went wrong. 🙄

---

<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:** [January 11, 2019, 9:43pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/8 "2019-01-11T21:43:03Z")

</div>

I think until the last DataFrames version we printed the full data frame to HTML in Jupyter IIRC, so the hang may just have been due to displaying the result.

---

<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:** [January 11, 2019, 9:57pm UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/9 "2019-01-11T21:57:27Z")

</div>

> [@elshera](#):
>
> i also did repeat again the previous one with Query.jl and it works now

Does that refer back to your original query? I.e. mostly wondering whether this is a regression/performance case I should look into, or whether everything is ok for Query.jl here 🙂

---

<div class="post-metadata">

**Author:** ![elshera](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elshera/32/6638_2.png) [@elshera](https://discourse.julialang.org/u/elshera)\
**Post date:** [January 12, 2019, 9:01am UTC](https://discourse.julialang.org/t/speeding-up-query-with-a-big-dataset/19508/10 "2019-01-12T09:01:09Z")

</div>

@davidanthoff  
No this is not related to my previous post. Query.jl seems working and performing OK now. I need to reproduce the results and provide more data before an action/issue can be raised. Thank you.

@nalimilan,  
Might have been a display issue. However, i did try the experiment in my working laptop and in a comp-server and in both I had the issue.