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:
TableName | where Timestamp between ( ago(60d) .. ago(59d) ) | where Key == 'key' and SubKey == 'subkey'
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.