This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello!
I'm working with an ADX database, and currently using kusto query language to get the data. The table from where I get the data has a 30 day cache (hot) storage configured, which means that getting data from before that period will be slower, since it is going to cold storage. That's fine.
However, based on results from testing, when querying data from 60 days ago (a) and 120 days ago (b) (24h of data in each test), query (b) is significantly slower - (a) 56 seconds, 2855 records; (b) 94 seconds, 2831 records.
The table structure is defined as: Timestamp (datetime), Key (string), SubKey (string), Data (dynamic).
Both queries were defined as: (a)
TableName | where Timestamp between ( ago(60d) .. ago(59d) ) | where Key == 'key' and SubKey == 'subkey'
(b)
TableName | where Timestamp between ( ago(120d) .. ago(119d) ) | where Key == 'key' and SubKey == 'subkey'
I would like to know the reason behind this differences in query time when going further back in time.
Thanks in advance :)
P.S. Forgot to mention (and it is probably relevant) that when querying data from 240 days ago, although there are no data in that period, the query still took 129.034 seconds, for 0 results.
Hello @Gonçalo Antunes,
are you able to share the statistics?
Like this sample:
Also share the raw JSON if possible, like this example:
I had to re-execute the queries so the results are a bit different.
The statistics are the following:
Query (a)
{
"ExecutionTime": 66.5443164,
"resource_usage": {
"cache": {
"memory": {
"hits": 0,
"misses": 0,
"total": 0
},
"disk": {
"hits": 0,
"misses": 0,
"total": 0
},
"shards": {
"hot": {
"hitbytes": 2523332513,
"missbytes": 0,
"retrievebytes": 0
},
"cold": {
"hitbytes": 2030375159,
"missbytes": 246186573,
"retrievebytes": 2125311383
},
"bypassbytes": 0
}
},
"cpu": {
"user": "00:00:09.1093750",
"kernel": "00:00:04.3437500",
"total cpu": "00:00:13.4531250"
},
"memory": {
"peak_per_node": 69268704
},
"network": {
"inter_cluster_total_bytes": 3250092,
"cross_cluster_total_bytes": 0
}
},
"input_dataset_statistics": {
"extents": {
"total": 327,
"scanned": 134,
"scanned_min_datetime": "2022-12-31T20:22:22.1089132Z",
"scanned_max_datetime": "2023-03-02T17:17:18.8597258Z"
},
"rows": {
"total": 3799241613,
"scanned": 1705906728
},
"rowstores": {
"scanned_rows": 730910,
"scanned_values_size": 96437011
},
"shards": {
"queries_generic": 134,
"queries_specialized": 0
}
},
"dataset_statistics": [
{
"table_row_count": 2847,
"table_size": 2827623
}
],
"cross_cluster_resource_usage": {}
}
Query (b)
{
"ExecutionTime": 79.7597138,
"resource_usage": {
"cache": {
"memory": {
"hits": 0,
"misses": 0,
"total": 0
},
"disk": {
"hits": 0,
"misses": 0,
"total": 0
},
"shards": {
"hot": {
"hitbytes": 2523332513,
"missbytes": 0,
"retrievebytes": 0
},
"cold": {
"hitbytes": 4454461171,
"missbytes": 1456432017,
"retrievebytes": 11460750795
},
"bypassbytes": 0
}
},
"cpu": {
"user": "00:00:29.4062500",
"kernel": "00:00:13.1875000",
"total cpu": "00:00:42.5937500"
},
"memory": {
"peak_per_node": 97545056
},
"network": {
"inter_cluster_total_bytes": 5381154,
"cross_cluster_total_bytes": 0
}
},
"input_dataset_statistics": {
"extents": {
"total": 327,
"scanned": 258,
"scanned_min_datetime": "2022-11-02T02:23:26.0077932Z",
"scanned_max_datetime": "2023-03-02T17:17:18.8597258Z"
},
"rows": {
"total": 3799241613,
"scanned": 3019308496
},
"rowstores": {
"scanned_rows": 1014024,
"scanned_values_size": 134097363
},
"shards": {
"queries_generic": 258,
"queries_specialized": 0
}
},
"dataset_statistics": [
{
"table_row_count": 2869,
"table_size": 2939743
}
],
"cross_cluster_resource_usage": {}
}
Hello @Gonçalo Antunes,
Thanks for running the queries and checking the statistics.
It seems query B (79 seconds for 120 days) is slower than query A (66 seconds for 60 days) which is on par with expectations.
You have to dig deeper into the ADX documentation for detailed background information.
If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.
@Sander van de Velde thank you for your answer.
Do you think, based on the information provided, that there is something I could do on the query itself to improve the performance, i.e., how could I re-write the queries for them to provide the results faster?
Nevertheless, I will accept the answer, since the response to the first answer was given accordingly :)