# Query with lots of fields takes orders of magnitude higher in flux than influxql **URL:** \ **Category:** Fluxlang\ **Created:** [March 15, 2021, 9:52am UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888 "2021-03-15T09:52:49Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [March 15, 2021, 9:52am UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/1 "2021-03-15T09:52:49Z")
I’ve been using this query on InfluxV1 and InfluxV2(on compatibility endpoint) and it takes a second or two. ``` SELECT mean("signal") * mean("fft_0001"), mean("signal") * mean("fft_0002"), mean("signal") * mean("fft_0003"), mean("signal") * mean("fft_0004"), .... mean("signal") * mean("fft_0510"), mean("signal") * mean("fft_0511"), mean("signal") * mean("fft_0512") FROM "data" WHERE "device" = 'XY' AND $timeFilter GROUP BY time($__interval) fill(null) ``` But a similar query in FluxQL takes order of magnitude higher and simply times out if I increase the time range. Is there potential to improve this query and bring it at par with the influxql one ? ``` import "strings" filtered = from(bucket: v.defaultBucket) |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "data" and and r["device"] == "XY") signal = filtered |> filter(fn: (r) => r._field == "signal") |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) fft = filtered |> filter(fn: (r) => strings.hasPrefix(v: r._field, prefix: "fft_")) |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) join(tables:{signal:signal,fft:fft}, on:["_time"]) |> map(fn: (r) =>({ _value: float(v:r._value_signal) * float(v:r._value_fft), _time: r._time })) |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") ``` ---
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [March 29, 2021, 4:52pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/2 "2021-03-29T16:52:07Z")
I’ve tried it on a beefier machine and still the same. Tried naming all fields instead of using `prefix: "fft_"` and that takes even longer. ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [March 31, 2021, 8:08pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/3 "2021-03-31T20:08:51Z")
Hello @tintin, I’m not sure. I’m sorry you’re having a bad experience. I’m sharing your question with the Flux team directly and I hope someone can provide some insight. Thank you. ---
**Author:** ![jonathan](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jonathan/32/13_2.png) [@jonathan](https://community.influxdata.com/u/jonathan)\ **Post date:** [April 21, 2021, 8:20pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/4 "2021-04-21T20:20:57Z")
Hi, I think the reason might be that your query isn’t utilizing the push down optimizations. ```auto import "experimental" // Use a function wrapper so we don't have to duplicate this and it will be registered // in the runtime as two separate from calls instead of one. A potential future improvement // is for the flux planner to recognize this pattern and do it automatically. select = () => from(bucket: v.defaultBucket) |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "data" and and r["device"] == "XY") signal = select() |> filter(fn: (r) => r._field == "signal") |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) fft = select() // regexes are specially understood while function calls are not, possible future optimization |> filter(fn: (r) => r._field =~ /^fft_/) |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) join(tables:{signal:signal,fft:fft}, on:["_time"]) |> map(fn: (r) =>({ _value: float(v:r._value_signal) * float(v:r._value_fft), _time: r._time })) |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") ``` I think this query should perform better as it forces the push downs to be used. Please give it a try and I can try to tweak it more or create a performance improvement issue if one of the other methods is what’s causing the problem. I also suspect that the pivot at the end doesn’t do anything so you can probably safely remove it. If you can give me an idea of the output you expect, I can help you tweak that part for the correct output. ---
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [May 7, 2021, 6:29pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/5 "2021-05-07T18:29:29Z")
Hi @jonathan , I tried it and unfortunately there was no noticeable improvement. The data I have is 1Hz. (1 row per second) The following two queries run in less than a second, even if the range is as long as a month ```auto select = () => from(bucket: v.defaultBucket) |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "data" and r["device"] == "XY") signal = select() |> filter(fn: (r) => r._field == "signal") |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) |> yield() ``` ```auto select = () => from(bucket: v.defaultBucket) |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "data" and r["device"] == "XY") fft = select() |> filter(fn: (r) => r._field =~ /^fft_/) |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false) |> yield() ``` As soon as I introduce `join` the time shoots up. ```auto join(tables:{signal:signal,fft:fft}, on:["_time"]) |> yield() ``` | Range | Time | | --- | --- | | 5 minutes | 4 seconds | | 15 minutes | 8 seconds | | 1 hour | Times out ~ 60 seconds | InfluxQL times for comparison | Range | Time | | --- | --- | | 5 minutes | Subsecond | | 15 minutes | 1 second | | 1 hour | 2 seconds | | 1 month | 8 seconds | ```auto ``` ---
**Author:** ![jonathan](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jonathan/32/13_2.png) [@jonathan](https://community.influxdata.com/u/jonathan)\ **Post date:** [May 7, 2021, 7:53pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/6 "2021-05-07T19:53:41Z")
I think this might be a problem with the speed of `join()` unfortunately. I think `join` is probably the best function for what you want to do since you want to join one column with many other columns, but the amount of data seems to be causing a problem. If it’s possible, do you think you can share any of the data you are using? It would allow me to run the queries myself and analyze where we can improve the performance. Are you doing this work on cloud 2.0? If you are, I may have another idea for a query that might improve the performance, but it requires a code change. ---
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [May 8, 2021, 1:44pm UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/7 "2021-05-08T13:44:11Z")
I am using OSS v2. I will try to get you the data on Monday. ---
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [May 20, 2021, 10:33am UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/8 "2021-05-20T10:33:07Z")
Here is some data: [https://we.tl/t-OipqqiF3rz](https://we.tl/t-OipqqiF3rz) Tags: device and channel. Fields: signal and fft\_xxxx ---
**Author:** ![tintin](https://avatars.discourse-cdn.com/v4/letter/t/ed655f/32.png) [@tintin](https://community.influxdata.com/u/tintin)\ **Post date:** [January 21, 2022, 8:32am UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/9 "2022-01-21T08:32:00Z")
@Anaisdg InfluxQL still performs better than Flux for my use case. Can you confirm what the future of InfluxQL is. I see another unanswered post here regarding the same here : [InfluxQL roadmap in InfluxDB 2 (will it be deprecated?)](https://community.influxdata.com/t/influxql-roadmap-in-influxdb-2-will-it-be-deprecated/20651). ---
**Author:** ![aldas](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/aldas/32/7151_2.png) [@aldas](https://community.influxdata.com/u/aldas)\ **Post date:** [January 21, 2022, 8:46am UTC](https://community.influxdata.com/t/query-with-lots-of-fields-takes-orders-of-magnitude-higher-in-flux-than-influxql/18888/10 "2022-01-21T08:46:41Z")
Adding relevant Github issue here for history sake [Performance difference between Flux and InfluxQL · Issue #18088 · influxdata/influxdb · GitHub](https://github.com/influxdata/influxdb/issues/18088)