How can I optimize ADX query performance to find last timestamp of a specific timeseries id ?

Hi @Sander van de Velde | MVP ,

• first query that you told me is very slow as the same that I have written in the post.
  I have added the last row to check ServerTimeStamp

Telemetry | 
summarize arg_max(ServerTimeStamp, *) by TimeseriesId | 
where TimeseriesId == '1d4037bc-7c26-483e-81a1-a3379d57612c' | 
project TimeseriesId, TimeseriesName, ServerTimeStamp, ServerTimeStampString = todatetime(unixtime_seconds_todatetime(ServerTimeStamp)), Value, Quality

query time spent about 77 sec :

Stats:

{
  "ExecutionTime": 76.6577133,
  "resource_usage": {
    "cache": {
      "memory": {
        "hits": 0,
        "misses": 0,
        "total": 0
      },
      "disk": {
        "hits": 0,
        "misses": 0,
        "total": 0
      },
      "shards": {
        "hot": {
          "hitbytes": 61708581,
          "missbytes": 0,
          "retrievebytes": 0
        },
        "cold": {
          "hitbytes": 136089939,
          "missbytes": 131260770,
          "retrievebytes": 2190662214
        },
        "bypassbytes": 0
      }
    },
    "cpu": {
      "user": "00:00:03.1718750",
      "kernel": "00:00:01.8125000",
      "total cpu": "00:00:04.9843750"
    },
    "memory": {
      "peak_per_node": 8497168
    },
    "network": {
      "inter_cluster_total_bytes": 879,
      "cross_cluster_total_bytes": 0
    }
  },
  "input_dataset_statistics": {
    "extents": {
      "total": 3191,
      "scanned": 157,
      "scanned_min_datetime": "2023-07-28T10:21:12.8808364Z",
      "scanned_max_datetime": "2023-11-07T09:48:39.9717979Z"
    },
    "rows": {
      "total": 1880141637,
      "scanned": 1421236170
    },
    "rowstores": {
      "scanned_rows": 0,
      "scanned_values_size": 0
    },
    "shards": {
      "queries_generic": 314,
      "queries_specialized": 0
    }
  },
  "dataset_statistics": [
    {
      "table_row_count": 1,
      "table_size": 94
    }
  ],
  "cross_cluster_resource_usage": {}
}

• Instead the last your query proposed the time spent is much better but the result is not right because the ServerTimeStamp is not the last.

Telemetry |
order by ServerTimeStamp desc |
where TimeseriesId == '1d4037bc-7c26-483e-81a1-a3379d57612c' |
top 1 by tostring(TimeseriesId) |
project TimeseriesId, TimeseriesName,ServerTimeStamp, ServerTimeStampString = todatetime(unixtime_seconds_todatetime(ServerTimeStamp)), Value, Quality

query time spent about 9 sec :

Stats:

{
  "ExecutionTime": 9.2516559,
  "resource_usage": {
    "cache": {
      "memory": {
        "hits": 0,
        "misses": 0,
        "total": 0
      },
      "disk": {
        "hits": 0,
        "misses": 0,
        "total": 0
      },
      "shards": {
        "hot": {
          "hitbytes": 10961884,
          "missbytes": 0,
          "retrievebytes": 0
        },
        "cold": {
          "hitbytes": 19009005,
          "missbytes": 21646743,
          "retrievebytes": 241201669
        },
        "bypassbytes": 0
      }
    },
    "cpu": {
      "user": "00:00:00.3437500",
      "kernel": "00:00:00.1718750",
      "total cpu": "00:00:00.5156250"
    },
    "memory": {
      "peak_per_node": 8706288
    },
    "network": {
      "inter_cluster_total_bytes": 879,
      "cross_cluster_total_bytes": 0
    }
  },
  "input_dataset_statistics": {
    "extents": {
      "total": 3157,
      "scanned": 157,
      "scanned_min_datetime": "2023-07-28T10:21:12.8808364Z",
      "scanned_max_datetime": "2023-11-07T10:09:10.1157174Z"
    },
    "rows": {
      "total": 1880480458,
      "scanned": 1421307289
    },
    "rowstores": {
      "scanned_rows": 0,
      "scanned_values_size": 0
    },
    "shards": {
      "queries_generic": 314,
      "queries_specialized": 0
    }
  },
  "dataset_statistics": [
    {
      "table_row_count": 1,
      "table_size": 94
    }
  ],
  "cross_cluster_resource_usage": {}
}

The last query is not extracting the right result as we expected.

Can you check:

Telemetry | order by ServerTimeStamp desc | where TimeseriesId == '1d4037bc-xxxx-xxxx-xxxx-xxxxxxxxx' | top 1 by ServerTimeStamp

@Sander van de Velde | MVP
I have tried your query and now the result is right but it took a long time (about 76 sec)

From your experience if I add ServerTimestamp as partition key as well, what will pros and cons be ?

I'm going to evaluate Materialized view ( link )

As written in Microsoft doc should cover my use case

What do you think about it ?

It is worth trying.

I'm a bit confused about the poor response.

I tested this with the Trips test set which gives a once response:

@Sander van de Velde | MVP

I have tried the same query that you have done in your table Trips and the query execution time spent is very high (about 84 sec) to get one record.

look the image below:

I'm going to understand the big query execution time difference compared to your to get the result.
From your test, I can compare the followed points:

1. In your Trips table there are 1.547.471.776 records || In my Telemetry table there are 1.904.018.592 records
2. In your Trips table, pickup_datetime field is datetime type || In my Telemetry table ServerTimeStamp field is real type.
3. In your Trips table what type vendor_id field is ? || In my Telemetry table TimeseriesId field is guid type.
4. In your Trips table, partition policy is on ?? || In my Telemetry table partition policy is on TimeseriesId field and Ingestion_time as default.
5. How many nodes does your ADX cluster have ? || My Cluster only has 1 node for develop env.
6. How many days did you set in your ADX HOT cache ? || My ADX HOT cache is 90 days

Maybe one or more points listed can help me to understand the big query execution time difference.

Thanks