Query execution on azure storage is very slow

3
votes

I am trying to query against timestamp column on a azure table storage. I have a where condition which checks if input value is greater than timestamp value. azure Timestamp column is not a partition key or row key. I came to know querying against column which is not partition or row key will decrease performance of the query execution. My application is very slow when there are more and more records. I tried to move time stamps data to partition key data but partition key is not accepting time stamp value like "4/13/2016 5:20:03 AM +00:00". What would be the best option to increase query performance.

1
azureazure-table-storage

1 Answers

5
votes

As you have mentioned already, if a query doesn't include PartitionKey and/or RowKey the queries will be slow because Table service is doing full table scan. Recommendation would be to use the date/time value as PartitionKey value.

I tried to move time stamps data to partition key data but partition key is not accepting time stamp value like "4/13/2016 5:20:03 AM +00:00"

This is because the value contains characters that are not allowed (https://msdn.microsoft.com/en-us/library/azure/dd179338.aspx --> see Characters Disallowed in Key Fields section).

There're actually a number of things you could do to solve this problem:

  • You can format your date/time value in YYYY-MM-DDThh:mm:ss format.

  • You can convert the date/time value to ticks and prepend some zeros in front and save that for PartitionKey. Something like:

    PartitionKey Value = YourDateTimeValue.Ticks.ToString("d19");

If you want the most recent entries to come on the top, you could do something like:

PartitionKey Value = (DateTime.MaxValue.Ticks - YourDateTimeValue.Ticks).ToString("d19");

You may also find this useful: https://azure.microsoft.com/en-in/documentation/articles/storage-table-design-guide/.