Date range queries in Azure Table storage

Question

Hello following on from my question: Windows Azure table access latency Partition keys and row keys selection about the way I have organised data in my Azure storage account. I have a table storage scheme designed to store info about entities.

There are about 4000 - 5000 entities. There are 6 entity types and the types are roughly evenly distributed. so around 800'ish each.

ParitionKey: entityType-Date

Row key: entityId

As the question details I have been sufferring latency issues where querying for multiple partitions seems to take quite a while.

Essentially one possible solution would be to do a query of the following:

PartitionKey > 'EntityType-Date' and PartitionKey < EntityType-HighDate' and RowKey = 'EntityId'

This does not seem to work in Window Azure Storage Explorer. It doesn't seem to recognize the greater than less than, which I'd kind of expect.

My question is exactly how does less than, greater than work and would I be better splitting my entities out by table type. Thus my query would be? Or should I be pursuing a different approach?

PartitionKey > LowDate and PartitionKey < HighDate and RowKey = 'EntityId'

Answer 1

As far as querying syntax is concerned in Azure Storage Explorer, you would need to specify your query like this:

(PartitionKey gt 'EntityType-Date' and PartitionKey lt EntityType-HighDate') and (RowKey eq 'EntityId')

Please see this link for more details: http://msdn.microsoft.com/en-us/library/windowsazure/dd894031.aspx.

For deciding on PartitionKey/RowKey for efficient querying, I would strongly recommend reading up this blog from Storage team: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx.