Let me start of by saying that in general, having good azure table storage querying performance comes down to having a solid table design. Proper use of the PartitionKey and RowKey-field is the primary factor in getting the best performance out of your Table Storage.
One important thing to realise is that querying performance for Azure Table Storage is completely driven by its indexes. Azure Table Storage only has an index on the PartitionKey and RowKey columns (as a clustered index), and on the table and account name itself. Queries that do not include either of these indexes will be (a lot) slower due to the lack of an index so it is crucial to keep these indexes in mind when designing your table(s).
Sometimes however, the table design is out of your hands, take Serilog for example. Serilog lets you output your logs to many different systems and services, including Azure Table Storage. I often ran into a performance issue when using Serilog with Azure Table Storage as many of my log-queries included the timestamp in one way or another. For example, I would query the logs of the last 15 minutes, or within a certain datetime range.
A better way to query Azure Table Storage for timestamps
When looking at Serilog in the context of logging to Azure Table Storage, it uses the PartitionKey for an alternative to the already present timestamp-column (which is managed by Azure itself, so in essence it’s a duplicate value). The PartitionKey in Serilog is the current UTC datetime converted to ticks. So querying your serilogs by including the PartitionKey instead of the timestamp improves performance greatly.
Simple convert the datetime you need to ticks using the following code:
//Converting Datetime to ticks and adding a 0-prefix to match with the PartitionKey. string ticks = "0" + DateTime.Now.Ticks
If you are querying from the Azure Storage Explorer you can use an online converter, add a zero as prefix and you end up with a much faster query than if you would query on timestamp.
1 comment
Nice idea. I changed the KeyGenerator based on this idea: https://stackoverflow.com/questions/46082251/how-to-set-my-own-keygenerator-instance-in-appsettings-json/46083183#46083183
public class LogKeyGenerator : IKeyGenerator
{
public string GeneratePartitionKey(LogEvent logEvent)
{
// Partition per day.
return logEvent.Timestamp.ToString(“yyyyMMdd”);
}
public string GenerateRowKey(LogEvent logEvent, string suffix = null)
{
// Make sure all rowkey’s are unique
return logEvent.Timestamp.Ticks.ToString();
}
}