Table Storage multi-entity queries slow

Question

Hi all,

  I'm using Table Storage for my persistence layer. I have a lot of queries that look for multiple RowKeys in a single Partition. So I would specify the PartitionKey, and then have a (RowKey == "a" || RowKey == "b" || RowKey == "c") type of construct. This has been working fine for a while. In many cases the table only has one PartitionKey in it, but I still specify the PartitionKey anyway. 

  Over the last few weeks I've started seeing some serious slowdowns in these queries. My code hasn't changed significantly. I don't think the number of rows has increased significantly, though we haven't been tracking data on it so I can't be sure. 

  I have the table performance metrics feature enabled. Looking at the "user;QueryEntities" entries from Feb 7th, I'm seeing Average Server Latency of around 35-120ms. Over the last 10 days the latency has bounced around a bunch, but generally trended upwards. I'm now seeing numbers as high as 1620ms in some cases. I have not seen a similar change in the "user;QueryEntity" performance - these queries have been <= 10ms server latency the entire time.

  The metrics data leads me to believe that it's not a problem with how I'm connecting to table storage. It has to be either a problem with the service itself, or a query optimization problem. How do I diagnose this? 

  I found a potentially relevant post from the ever-knowledgable Jai implying that OR queries are just bad, but it's from 2010. Is that still the case? Even if it is the case, why have I seen such a performance change in the last 10 days?

Thanks,
BKR

Answer 1

Hi Brian - thanks for the question.  The query you describe will result in a full scan of the partition, so your performance will definitely depend on the number of entities in your partition.  If the partition has been growing recently, that could explain it.  A good workaround would be to break the query apart, so that instead of the following, resulting in a full partition scan:

PartitionKey="A" && (RowKey = "1" || RowKey == "5" || RowKey == "8")

you would have three queries, each of which is what we call a "point query", where the query is highly efficient because the entire key is specified.

PartitionKey="A" && RowKey = "1"
PartitionKey="A" && RowKey = "5"
PartitionKey="A" && RowKey = "8"

If your partition/table hasn't been growing significantly, then please go ahead and e-mail me at jirwin @ microsoft . com, and we'll be happy to help look into this with you to see if there's a solution.

---

-Jeff

Answer 2

Another thought, if you are having the same partition key for all the rows then probably its not an efficient use of the partition. Azure Table storage might split it into multiple nodes without even using appropriate partitioned as you have only one key and your query might span multiple nodes. I would suggest to try rethink the logic of utilizing the partition efficiently. One way could be to use range of row keys 1T100, 101TO200 etc.. and depedning on the row key you are looking for span parallel queries if they are across multiple partitions.

-Sachin Sancheti

Answer 3

Sachin,

  I thought that all entities with a given PartitionKey were guaranteed to be on the same storage server? Is that not the case?

  Putting all the entities on one server was part of the reason we created some tables that way. We anticipated they'd be relatively small, and we figured that OR queries would be quicker if the entities were all on the same server. We were also guessing that queries on non-key columns might be somewhat less painful. 

Thanks,
BKR

  

Answer 4

Brian,

I don't think it is guranteed to be on the same node, my understanding is it ight get spanned across multiple physical nodes under logical partition.
Need to get it confirmed Jeff Irwin could you please confirm this.

It is highly possible that Azure storage might be rearranging the partition too to support the high scale, replication. Also, if we put everything on one node throttling and scaling would be another issue.
I would suggest to break it into logical partitions for scale and performance.
BTW, You are handling continuation token in your code right?

Answer 5

Sachin,

   Perhaps it's changed, but I know in the past each PartitionKey was served by the same server. eg, the standby "Getting the Most Out Of Windows Azure Tables" post says "All entities with same PartitionKey value are clustered together and they are served from a single server node"

  Certainly that has performance implications, particularly for large tables. I had not expected it to be a significant problem with smallish tables of 10-20k entities. 

 

  We do our queries using the CloudTableQuery<> object, which I believe handles continuations internally. Though we mostly try to avoid doing queries across partitions to avoid the need. 

Answer 6

Hi Brian - after our offline investigation, this is what we've determined:

First, yes, the blog post you're referencing is correct.  The entities in a single partition will be stored on the same physical node.

Second, Regarding continuation tokens, please note that you can get continuation tokens even when you do queries within a single partition, if the query takes long enough or returns enough entities.

Finally, regarding performance, we've confirmed the performance results you're seeing, and at this time suggest that you follow the best practice of using point queries (which you've already done).  If your row keys were located close together, you could just specify a range query and filter on the client, but if the row keys are random, that might not help.  We have recorded the request to make queries like this one faster/more efficient, and will consider that as a future improvement to the service.  At this time, that type of query results in a scan of the partition, which is more efficient than a table scan because they are on the same server, but still not nearly as efficient as a point query.

Thanks so much for the question, let us know if there's anything else we can help with!

---

-Jeff

Answer 7

Thanks for all the help, Jeff. 

It would be nice if "OR" queries were optimized, at least if they also include the PartitionKey. Intuitively, it seems like it should be fast, so I wouldn't be surprised if a lot of people make this mistake. 

Thanks again,
BKR