locked
Combining Multiple Point-Queries RRS feed

  • Question

  • Is this answer about selecting different unrelated records from 2011 still valid for the current version of Azure?

    If you want several non contiguous row keys, then issuing separate but parallel individual queries of below form will perform better. When you specify both PartitionKey and RowKey the query is considered a "PointQuery" which is the most performant option.

    a)PartitionKey == “MyPK” && RowKey == “FirstRK”

    b)PartitionKey == “MyPK” && RowKey == “SecondRK"


    If I need to make several point queries can I do that in one request?

    -chris

    Wednesday, September 18, 2013 7:25 PM

Answers

  • Hi,

    From my experience, as long as we put both partition key and row key in the query, a high performance can be expected, both keys are indexed. Based on my understanding, a single request can actually increase performance if the individual requests are too small. But please do not include too many filters in a single query, this makes it harder to serialize/deserialize the filters, and makes the response too large. If you want to use a single query, then you can write:
    (PartitionKey == "p1" && RowKey == "r1) || (PartitionKey == "p2" && RowKey == "r2")

    It is needed to use "or", table storage does not stand by "in". The CombineFilters method (pointed out by Jambor) allows us to programmatically create a large "or" filter without explicitly writing it.

    In addition, you can refer to http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx for general guidelines on table storage performance.

    Best Regards,

    Ming Xu


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 19, 2013 8:25 AM
  • Hi Chris,

    According to my knowledge, the query is considered as a single transaction and performance can decrease if the partition is too large [with billions of entities (and thus the index is too large)]. But we can expect the performance in most cases.

    Best Regards,

    Ming Xu


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 25, 2013 5:00 PM

All replies

  • Hi,

    From my experience, in the same azure table storage we can save different entities. if we use the same entity, we can try as following.

    string condition1 = TableQuery.CombineFilters(
                           TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, " MyPK"),
                           TableOperators.And,
                        TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, " FirstRK"));
    
                string condition2 = TableQuery.CombineFilters(
                            TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, " MyPK"),
                            TableOperators.And,
                         TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, " SecondRK"));
    
                string finalFilter = TableQuery.CombineFilters(condition1, TableOperators.And, condition2);
    
                TableQuery<CustomerEntity> query = new TableQuery<CustomerEntity>().Where(
                     TableQuery.CombineFilters(condition1, TableOperators.Or, condition2)
                    );
    
    Hope this helps

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 19, 2013 5:37 AM
  • Hi,

    From my experience, as long as we put both partition key and row key in the query, a high performance can be expected, both keys are indexed. Based on my understanding, a single request can actually increase performance if the individual requests are too small. But please do not include too many filters in a single query, this makes it harder to serialize/deserialize the filters, and makes the response too large. If you want to use a single query, then you can write:
    (PartitionKey == "p1" && RowKey == "r1) || (PartitionKey == "p2" && RowKey == "r2")

    It is needed to use "or", table storage does not stand by "in". The CombineFilters method (pointed out by Jambor) allows us to programmatically create a large "or" filter without explicitly writing it.

    In addition, you can refer to http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx for general guidelines on table storage performance.

    Best Regards,

    Ming Xu


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 19, 2013 8:25 AM
  • Hi,

    From my experience, as long as we put both partition key and row key in the query, a high performance can be expected, both keys are indexed. Based on my understanding, a single request can actually increase performance if the individual requests are too small. But please do not include too many filters in a single query, this makes it harder to serialize/deserialize the filters, and makes the response too large. If you want to use a single query, then you can write:
    (PartitionKey == "p1" && RowKey == "r1) || (PartitionKey == "p2" && RowKey == "r2")

    It is needed to use "or", table storage does not stand by "in". The CombineFilters method (pointed out by Jambor) allows us to programmatically create a large "or" filter without explicitly writing it.

    Are you sure this is correct? A user on stackoverflow reports very slow performance when using this type of query:

    http://stackoverflow.com/questions/11966450/very-slow-on-azure-table-storage-query-on-partitionkey-rowkey-list

    Could you get someone from the Azure team write a blog post about this topic? I found a lot of questions about this but no definite answer.

    Thursday, September 19, 2013 11:48 AM
  • Hi,

    Apologize that I forgot to mention we must make sure all filters have the same partition key. And my sample was incorrect. Below is the correct version:
     
     (PartitionKey == "p1" && RowKey == "r1) || (PartitionKey == "p1" && RowKey == "r2")
     
     Cross partition queries can be slow, as different partitions can be stored on different servers. But I don't think a full table scan is required, because you're including the row key as well. The slow performance is probably caused by forwarding the request to multiple servers and then combine the results.

      >> Could you get someone from the Azure team write a blog post about this topic? I found a lot of questions about this but no definite answer.

    Thanks a lot for your feedback, I will report this issue.

    Best Regards,

    Ming Xu


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Thursday, September 19, 2013 2:39 PM
  • Thank you for the information!

    So the (PartitionKey == "p1" && RowKey == "r1) || (PartitionKey == "p1" && RowKey == "r2") query will have good performance regardless of the size of the partition?

    And does the request still count as one transaction?

    -chris

    Tuesday, September 24, 2013 3:57 PM
  • Hi Chris,

    According to my knowledge, the query is considered as a single transaction and performance can decrease if the partition is too large [with billions of entities (and thus the index is too large)]. But we can expect the performance in most cases.

    Best Regards,

    Ming Xu


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 25, 2013 5:00 PM