Alexandru Alexandru - 1 month ago 5
C# Question

Improving performance of this Azure table row count query

I have the following query on an Azure table:

var count = table.ExecuteQuery(new TableQuery<MessageEntity>()).Count();


This is the
MessageEntity
:

public class MessageEntity : TableEntity
{
public MessageEntity() { }

public string Message { get; set; }
}


This query is really slow. It takes about 15 seconds to count a grand total of 85,000 records on my development machine. I am using the Azure Storage Emulator with
UseDevelopmentStorage=true
as my connection string. What can I do to make it more performant? All I am trying to do is count the number of records, surely there must be a faster way?

Answer

As you may already know that Azure Tables have limited LINQ support and Count is currently not supported (List of supported LINQ operators).

var count = table.ExecuteQuery(new TableQuery<MessageEntity>()).Count();

The code above is fetching all entities from the table (up to 1000 entities at a time) and because you have 20000 entities in your table, it is making at least 20 requests to Azure Tables. Since each request is an HTTP request to Azure Tables REST API, this is why you're seeing it is taking so much time. This will get even worse when you try to get the count for the entities in a table from a real storage account.

One way you could reduce time is by reducing the data that is transferred over the network (especially the response data). Currently every property of the entity is returned as part of response data. Since you're only interested in the total count, you can make use Query Projection and get only one property (say PartitionKey or RowKey) back in response. That way your response data will be a lot smaller than what you currently have and that should reduce the time somewhat.

If knowing count is really important to you, another thing you could do is calculate the count through some background process and update it in a separate table.

Comments