Azure Data Explorer: Why does it take longer to query 24h of data from 120 days ago than data from 60 days ago, when querying data from COLD storage? Shouldn't it take approximately the same amount of time, since both queries need to get data from cold?

Gonçalo Antunes 20 Reputation points


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'


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.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
368 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sander van de Velde 22,286 Reputation points MVP

    Hello @Gonçalo Antunes,

    are you able to share the statistics?

    Like this sample:

    User's image

    Also share the raw JSON if possible, like this example:

    User's image

0 additional answers

Sort by: Most helpful