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?

Question

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

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.

Answer 1

Sander van de Velde 22,286 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

Gonçalo Antunes 20

@Sander van de Velde

I had to re-execute the queries so the results are a bit different.

The statistics are the following:

Query (a)
User's image

{
  "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)
User's image

{
  "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": {}
}

Sander van de Velde 22,286 Reputation points MVP

2023-03-02T18:32:28.6266667+00:00

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.
Gonçalo Antunes 20 Reputation points

2023-03-03T09:10:53.1066667+00:00

@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 :)

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?

0 additional answers

Activity