Tips & Tricks for Query Pagination in Azure Cosmos DB

Tim

In this post, we’ll take a look at tips for query pagination in Azure Cosmos DB. Pagination of query results is done to divide a large volume of data across many small pages or handle a long-running query. While it’s important to understand how query pagination works in Azure Cosmos DB, it’s not a concept that’s unique to databases.

 

Let’s start by taking a look an example of pagination outside of databases. Here’s an Internet search that returns 61.8 million results:

Image paginationCosmosDB

Only 10 results are shown on the first page, but you can select to go to a later page if you want more results:

Image TabPagination

Why is it beneficial to break up the results into multiple pages? There are a few reasons:

  • Data volume – There’s a finite amount of data that the server can return back to my web browser. Returning the full 61.8 million results would take too many resources.
  • Execution time – Users are only willing to wait a short amount of time for data. In general, most users expect a web page to return data within a second or less.
  • Usability – It’s easiest for the user to read through a small set of results at a time. Even if we could return all 61.8 million results at once, the user would have a more difficult time digesting it.

In Azure Cosmos DB, there are similar reasons why queries can be split up into multiple pages of results:

  • Data volume – Each page of query results is capped at 4 MB.
  • Execution time – Every query execution is capped at 5 seconds. Even if you run a query and results aren’t available yet, you will always get a response back from Azure Cosmos DB after 5 seconds.
  • Usability – Many applications can only handle a certain number of results back at once from Azure Cosmos DB. This is configurable with the MaxItemCount.

There are also cases where the query engine returns results in multiple pages because it’s better for query performance. This is common for cross-partition queries, where you’ll occasionally get an empty page of results returned if there aren’t results from all physical partitions. The query engine may also split up results due to throttling.

 

Pagination tips

Your code should always be able to handle the case where query results span multiple pages. Here’s a query pagination example using the .NET SDK. The Azure Cosmos DB SDK does most of the work for you. Including a while loop is essential here, allowing the query to keep running until there are no more results.

If you want to stop your query and resume it later, you can extract a continuation token. A continuation token is a string (up to 16 KB) that the query engine can use to continue the query without having to repeat previous executions leading up to that point. You can limit continuation token size if needed, but we recommend keeping it as high as your application allows. Here’s how to do pagination with continuation tokens.

Getting query results as quickly as possible

In some cases, you might want your query to return as much data as it can, as quickly as possible. The best way to do this is by minimizing the number of round trips that your application needs to make to Azure Cosmos DB.

In this case, you should set MaxItemCount to -1 in your QueryOptions. In most Azure Cosmos DB SDK versions, MaxItemCount defaults to 100. With that default setting, queries are limited to returning 100 items per query page, regardless of whether the query is close to hitting the 4 MB limit per page limit or 5 second query execution limit. If the query engine can return more results in each page, the query will require fewer round trips.

Here’s an example:

string sqlQuery = "SELECT TOP 20 * FROM c WHERE c.Item = 'shirt' AND c.Price > 75";

QueryDefinition query = new QueryDefinition(sqlQuery);

FeedIterator<Item> resultSetIterator = exampleApp.container.GetItemQueryIterator<Item>(
query, requestOptions: new QueryRequestOptions
    {
        MaxItemCount = -1
    }
);

Retrieving an exact number of query results

Many applications want to obtain an exact number of query results. In the earlier Bing search example, the server returned 10 results per page. In that case, while it was important to have a maximum of 10 results per page, it was also totally fine if there were fewer than 10 results. However, there are other instances where you might want an exact number of results returned.

Here’s one approach to get an exact number of query results, assuming exactly 10 results are desired:

  1. Run the query with MaxItemCount = 10
  2. If n results are returned from the query and n < 10, repeat the query with a new MaxItemCount value of 10 – n
  3. Repeat step 2, subtracting the total number of query results so far from the new MaxItemCount value

Cancelling expensive queries

An expensive query can quickly consume a significant amount of your provisioned throughput. Each time a page of query results is returned, you can check the query’s RU charge and, if necessary, stop progressing to the next page. Because a query will return results after 5 seconds, this limits the damage from an unexpectedly expensive query. When a query is executing, you cannot stop execution until the query engine returns a response for that page.

Here’s an example for you to adjust your code to support stopping expensive queries:

double totalRUsConsumed = 0;
double budgetRUs = 100;

FeedIterator<Item> resultSetIterator = exampleApp.container.GetItemQueryIterator<Item>(
query, requestOptions: new QueryRequestOptions
    {
        MaxItemCount = -1
    });

FeedResponse<Item> response;

while (resultSetIterator.HasMoreResults)
{
    response = await resultSetIterator.ReadNextAsync();
    totalRUsConsumed = totalRUsConsumed + response.RequestCharge;

    if (totalRUsConsumed > budgetRUs)
    {
        throw new Exception("RUs budget exceeded!");
    }
}

Learn more about query pagination in Azure Cosmos DB:

0 comments

Leave a comment