Azure Table Storage multi row query performance

Question

We have had issue in a service utilizing Azure Table Storage where sometimes the queries take multiple seconds (3 to 30 seconds). This happens daily, but only for some of the queries. We do not have huge load on the service and the table storage (some hundreds of calls per hour). But still the table storage is not performing.

The slow queries are all doing filter queries that should return in maximum 10 rows. I have the filters structured so that there is always partition key and row key joined by and followed by next pair of partition and row keys after an or operator:

(partitionKey1 and RowKey1) or (partitionKey2 and rowKey2) or (partitionKey3 and rowKey3)

So qurrently I am on the premise that I need to split the query into separate queries. This was somewhat verified with a python script I did. Where when I repeat same query as single query (combined query with or's and expecting multiple rows as result) or split to multiple queries executed in separate treads, I see the combined query slow up every now and then.

import time
import threading
from azure.cosmosdb.table.tableservice import TableService
from azure.cosmosdb.table.models import Entity

############################################################################
# Script for querying data from azure table storage or cosmos DB table API.
# SAS token needs to be generated for using this script and a table with data 
# needs to exist.
#
# Warning: extensive use of this script may burden the table performance, 
#          so use with care.
#
# PIP requirements:
#  - requires azure-cosmosdb-table to be installed
#     * run: 'pip install azure-cosmosdb-table'

dateTimeSince = '2019-06-12T13:16:45.446Z'

sasToken = 'SAS_TOKEN_HERE' 
tableName = 'TABLE_NAME_HERER'

table_service = TableService(account_name="ACCOUNT_NAME_HERE", sas_token=sasToken)

tableFilter = "(PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_ed6d31b0') and (RowKey eq 'ed6d31b0-d2a3-4f18-9d16-7f72cbc88cb3') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_9be86f34') and (RowKey eq '9be86f34-865b-4c0f-8ab0-decf928dc4fc') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_97af3bdc') and (RowKey eq '97af3bdc-b827-4451-9cc4-a8e7c1190d17') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_9d557b56') and (RowKey eq '9d557b56-279e-47fa-a104-c3ccbcc9b023') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_e251a31a') and (RowKey eq 'e251a31a-1aaa-40a8-8cde-45134550235c')"

resultDict = {}

# Do separate queries

filters = tableFilter.split(" or ")
threads = []

def runQueryPrintResult(filter):
    result = table_service.query_entities(table_name=tableName, filter=filter)
    item = result.items[0]
    resultDict[item.RowKey] = item

# Loop where: 
# - Step 1: test is run with tableFilter query split to multiple threads
#      * returns single  row per query
# - Step 2: Query is runs tableFilter query as single query
# - Press enter to repeat the two query tests
while 1:
    start2 = time.time()
    for filter in filters:
        x = threading.Thread(target=runQueryPrintResult, args=(filter,))
        x.start()
        threads.append(x)

    for x in threads:
        x.join()

    end2 = time.time()
    print("Time elapsed with multi threaded implementation: {}".format(end2-start2))

    # Do single query
    start1 = time.time()
    listGenerator = table_service.query_entities(table_name=tableName, filter=tableFilter)
    end1 = time.time()
    print("Time elapsed with single query: {}".format(end1-start1))

    counter = 0
    allVerified = True
    for item in listGenerator:
        if resultDict[item.RowKey]:
            counter += 1
        else:
            allVerified = False

    if len(listGenerator.items) != len(resultDict):
        allVerified = False

    print("table item count since x: " + str(counter))

    if allVerified:
        print("Both queries returned same amount of results")
    else:
        print("Result count does not match, single threaded count={}, multithreaded count={}".format(len(listGenerator.items), len(resultDict)))

    input('Press enter to retry test!')

Here is an example output from the python code:

Time elapsed with multi threaded implementation: 0.10776209831237793
Time elapsed with single query: 0.2323908805847168
table item count since x: 5
Both queries returned same amount of results
Press enter to retry test!
Time elapsed with multi threaded implementation: 0.0897986888885498
Time elapsed with single query: 0.21547174453735352
table item count since x: 5
Both queries returned same amount of results
Press enter to retry test!
Time elapsed with multi threaded implementation: 0.08280491828918457
Time elapsed with single query: 3.2932426929473877
table item count since x: 5
Both queries returned same amount of results
Press enter to retry test!
Time elapsed with multi threaded implementation: 0.07794523239135742
Time elapsed with single query: 1.4898555278778076
table item count since x: 5
Both queries returned same amount of results
Press enter to retry test!
Time elapsed with multi threaded implementation: 0.07962584495544434
Time elapsed with single query: 0.20011520385742188
table item count since x: 5
Both queries returned same amount of results
Press enter to retry test!

The service we have problems with is implemented in C# though and I have yet to reproduce the results gotten with python script on the C# side. There I seem to have worse performance when splitting the query to multiple separate queries vs using single filter query (returning all the required rows).

So doing following multiple times and awaiting all to complete seems to be slower:

TableOperation getOperation =
                TableOperation.Retrieve<HqrScreenshotItemTableEntity>(partitionKey, id.ToString());
            TableResult result = await table.ExecuteAsync(getOperation);

Than doing all in single query:

        private IEnumerable<MyTableEntity> GetBatchedItemsTableResult(Guid[] ids, string applicationLink)
        {
            var table = InitializeTableStorage();

            TableQuery<MyTableEntity> itemsQuery= 
                new TableQuery<MyTableEntity>().Where(TableQueryConstructor(ids, applicationLink));

            IEnumerable<MyTableEntity> result = table.ExecuteQuery(itemsQuery);

            return result;
        }

        public string TableQueryConstructor(Guid[] ids, string applicationLink)
        {
            var fullQuery = new StringBuilder();

            foreach (var id in ids)
            {
                    // Encode link before setting to partition key as REST GET requests 
                    // do not accept non encoded URL params by default)
                    partitionKey = HttpUtility.UrlEncode(applicationLink);


                // Create query for single row in a requested partition
                string queryForRow = TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionKey),
                    TableOperators.And,
                    TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, id.ToString()));

                if (fullQuery.Length == 0)
                {
                    // Append query for first row

                    fullQuery.Append(queryForRow);
                }
                else
                {
                    // Append query for subsequent rows with or operator to make queries independent of each other.

                    fullQuery.Append($" {TableOperators.Or} ");
                    fullQuery.Append(queryForRow);
                }
            }

            return fullQuery.ToString();
        }

The test case used with the C# code is quite different though from the python test. In C# I am querying 2000 rows from data of something like 100000 rows. If the data is queried in batches of 50 rows the latter filter query beats the single row query run in 50 tasks.

Maybe I should just repeat the test I did with python in C# as a console app to see if the .Net client api seems to behave the same way as python perf vice.

Answer 1

I think you should use multi-threaded implementation, since it consists of multiple Point Query. Doing all in single query probably results in a Table Scan. As the official doc mentions:

Using an "or" to specify a filter based on RowKey values results in a partition scan and is not treated as a range query. Therefore, you should avoid queries that use filters such as: $filter=PartitionKey eq 'Sales' and (RowKey eq '121' or RowKey eq '322')

You might think the example above is two Point Queries, but it actually results in a Partition Scan.

Answer 2

To me the answer here seems to be that executing queries on table storage has not been optimized to work with OR operator as you would expect. Query is not handled as point query when it combines point queries with OR operator.

This can be reproduced in python, C# and Azure Storage Explorer in which all where if you combine point queries with OR it can be 10x slower (or even more) than doing separate point queries that only return one row.

So most efficient way to get number of rows with partition and row keys known is to do them all with separate async queries with TableOperation.Retrieve (in C#). Using TableQuery is highly inefficient and does not produce results anywhere near the performance scalability targets for Azure Table Storage are leading to expect. Scalability targets say for example: "Target throughput for a single table partition (1 KiB-entities) Up to 2,000 entities per second". And here I was not even able to be served with 5 rows per second although all rows were in different partitions.

This limitation in query performance is not very clearly stated anywhere in any documentation or performance optimization guide, but it could be understod from these lines in the Azure storage performance checklist:

Querying

This section describes proven practices for querying the table service.

Query scope

There are several ways to specify the range of entities to query. The following is a discussion of the uses of each.

In general, avoid scans (queries larger than a single entity), but if you must scan, try to organize your data so that your scans retrieve the data you need without scanning or returning significant amounts of entities you don't need.

Point queries

A point query retrieves exactly one entity. It does this by specifying both the partition key and row key of the entity to retrieve. These queries are efficient, and you should use them wherever possible.

Partition queries

A partition query is a query that retrieves a set of data that shares a common partition key. Typically, the query specifies a range of row key values or a range of values for some entity property in addition to a partition key. These are less efficient than point queries, and should be used sparingly.

Table queries

A table query is a query that retrieves a set of entities that does not share a common partition key. These queries are not efficient and you should avoid them if possible.

So "A point query retrieves exactly one entity" and "Use point queries when ever possible". Since I had split the data to partitions, it may have been handled as table query: "A table query is a query that retrieves a set of entities that does not share a common partition key". This although the query combined set of point queries as it listed both partition and row keys for all entities that were expected. But since the combined query was not retriewing only one query it cannot be expected to perform as point query (or set of point queries).

Answer 3

Posting as an answer since it was getting bigger for comments.

Can you try by changing your query to something like the following:

(PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_ed6d31b0' and RowKey eq 'ed6d31b0-d2a3-4f18-9d16-7f72cbc88cb3') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_9be86f34' and RowKey eq '9be86f34-865b-4c0f-8ab0-decf928dc4fc') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_97af3bdc' and RowKey eq '97af3bdc-b827-4451-9cc4-a8e7c1190d17') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_9d557b56' and RowKey eq '9d557b56-279e-47fa-a104-c3ccbcc9b023') or (PartitionKey eq 'http%3a%2f%2fsome_website.azurewebsites.net%2fApiName_e251a31a' and RowKey eq 'e251a31a-1aaa-40a8-8cde-45134550235c')