influxdata / influxdb-comparisons

Code for comparison write ups of InfluxDB and other solutions
MIT License
308 stars 112 forks source link

chore: remove `GROUP BY *` from IoT light-level-8-hr #189

Closed williamhbaker closed 3 years ago

williamhbaker commented 3 years ago

As a follow-up to https://github.com/influxdata/influxdb-comparisons/pull/188, this removes the GROUP BY * statement from the InfluxQL query. This makes the InfluxQL query run a little bit faster, but still slower than the Flux query in my local tests.

Without the GROUP BY *, the list of results would look something like this:

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "light_level_room",
                    "columns": [
                        "time",
                        "level"
                    ],
                    "values": [
                        [
                            "2018-01-01T03:12:00Z",
                            9999.817330076934
                        ],
                        [
                            "2018-01-01T03:12:00Z",
                            10015.688820248402
                        ],
                        [
                            "2018-01-01T03:12:00Z",
                            9983.836965520972
                        ]
                }
            ]
        }
    ]
}

With the GROUP BY *, the results are grouped like this:

{
    "results": [
        {
            "statement_id": 0,
            "series": [
                {
                    "name": "light_level_room",
                    "tags": {
                        "home_id": "0000000000000",
                        "room_id": "1",
                        "sensor_id": "0000000000007"
                    },
                    "columns": [
                        "time",
                        "level"
                    ],
                    "values": [
                        [
                            "2018-01-01T03:12:00Z",
                            9999.817330076934
                        ]
                    ]
                },
                {
                    "name": "light_level_room",
                    "tags": {
                        "home_id": "0000000000000",
                        "room_id": "2",
                        "sensor_id": "0000000000012"
                    },
                    "columns": [
                        "time",
                        "level"
                    ],
                    "values": [
                        [
                            "2018-01-01T03:12:00Z",
                            10005.001828465722
                        ]
                    ]
                }
            ]
        }
    ]
}

This second form is perhaps more directly comparable to the raw Flux output which includes separate tables for each result, but adds additional processing to the InfluxQL query compared to the Flux query. Since this query is being created in response to a concern that the Flux query is slower than the InfluxQL query, removing the extra processing step from the InfluxQL query and having the flux query still be actually faster provides a less obtuse comparison between the two.