Why is it so slow to query azure metrics tables and how can I speed it up?

Question

https://docs.microsoft.com/en-us/azure/virtual-machines/extensions/diagnostics-template#wadmetrics-tables-in-storage

I'm trying to extract data from one of these WADMETRIC tables, but even if I setup a query to simply query the last one minute of data it takes hours to run!

This happens whether I query it using the Java sdk or from logviewer on the portal.

What am I doing wrong? Should I expect to wait this long?

Any help would be greatly appreciated.

Here's my java code.

        String connectionString = String.format("DefaultEndpointsProtocol=https;AccountName=%s;AccountKey=%s", "imgoriginalblobs", "lAHAdJcjUcak48lvNwOoC2UztAcqD+hLX/YMdXq/qppQOgeuzQXTDMtYL3jx/oT+a0hxURqlF2Nv9Fza686s8g==");
        CloudStorageAccount account = CloudStorageAccount.parse(connectionString);
        CloudTableClient cloudTableClient = account.createCloudTableClient();
        Calendar myCal = Calendar.getInstance();
        Date now = myCal.getTime();
        myCal.set(Calendar.MINUTE, Calendar.getInstance().get(Calendar.HOUR) - 1);
        Date theDate = myCal.getTime();
        String queryString = TableQuery.combineFilters(
                TableQuery.combineFilters(TableQuery.generateFilterCondition("Timestamp", TableQuery.QueryComparisons.GREATER_THAN_OR_EQUAL, theDate),
                        TableQuery.Operators.AND,
                        TableQuery.generateFilterCondition("CounterName", TableQuery.QueryComparisons.EQUAL, "/builtin/filesystem/freespace")),
                TableQuery.Operators.AND,
                TableQuery.generateFilterCondition("Timestamp", TableQuery.QueryComparisons.LESS_THAN_OR_EQUAL, now));
        TableQuery<TableMetric> query = TableQuery.from(TableMetric.class).where(queryString);

        CloudTable table = cloudTableClient.getTableReference("WADMetricsPT1MP10DV2S20191017");
        System.out.println(table.exists());
        System.out.println(table.getName());

        for (TableMetric entity : table.execute(query)) {
            extractPartitionKey(entity.getPartitionKey(), entity.getLast(), entity.getCounterName());

        }

And a screenshot of me trying to query the data in the last 5 minutes, this has been hanging for over an hour.

Thiago Custodio Thiago Custodio · Accepted Answer · 2019-10-24T15:32:18

I think the problem is because you're not specifying the Partition Key. So it's performing the query over all partitions that probably are stored in multiple regions, that's the reason it's taking a long time to give you the desired output.

Why is it so slow to query azure metrics tables and how can I speed it up?

2 Answers