Query performance tips for Azure Cosmos DB SDKs

Reduce Query Plan calls

To execute a query, a query plan needs to be built. This in general represents a network request to the Azure Cosmos DB Gateway, which adds to the latency of the query operation. There are two ways to remove this request and reduce the latency of the query operation:

Optimizing single partition queries with Optimistic Direct Execution

Azure Cosmos DB NoSQL has an optimization called Optimistic Direct Execution (ODE), which can improve the efficiency of certain NoSQL queries. Specifically, queries that don’t require distribution include those that can be executed on a single physical partition or that have responses that don't require pagination. Queries that don’t require distribution can confidently skip some processes, such as client-side query plan generation and query rewrite, thereby reducing query latency and RU cost. If you specify the partition key in the request or query itself (or have only one physical partition), and the results of your query don’t require pagination, then ODE can improve your queries.

ODE is now available in the .NET SDK version 3.38.0 and later. When you execute a query and specify a partition key in the request or query itself, or your database has only one physical partition, your query execution can leverage the benefits of ODE. To enable ODE, set EnableOptimisticDirectExecution to true in the QueryRequestOptions.

Single partition queries that feature GROUP BY, ORDER BY, DISTINCT, and aggregation functions (like sum, mean, min, and max) can significantly benefit from using ODE. However, in scenarios where the query is targeting multiple partitions or still requires pagination, the latency of the query response and RU cost might be higher than without using ODE. Therefore, when using ODE, we recommend to:

• Specify the partition key in the call or query itself.
• Ensure that your data size hasn’t grown and caused the partition to split.
• Ensure that your query results don’t require pagination to get the full benefit of ODE.

Here are a few examples of simple single partition queries which can benefit from ODE:

- SELECT * FROM r
- SELECT * FROM r WHERE r.pk == "value"
- SELECT * FROM r WHERE r.id > 5
- SELECT r.id FROM r JOIN id IN r.id
- SELECT TOP 5 r.id FROM r ORDER BY r.id
- SELECT * FROM r WHERE r.id > 5 OFFSET 5 LIMIT 3 

There can be cases where single partition queries may still require distribution if the number of data items increases over time and your Azure Cosmos DB database splits the partition. Examples of queries where this could occur include:

- SELECT Count(r.id) AS count_a FROM r
- SELECT DISTINCT r.id FROM r
- SELECT Max(r.a) as min_a FROM r
- SELECT Avg(r.a) as min_a FROM r
- SELECT Sum(r.a) as sum_a FROM r WHERE r.a > 0 

Some complex queries can always require distribution, even if targeting a single partition. Examples of such queries include:

- SELECT Sum(id) as sum_id FROM r JOIN id IN r.id
- SELECT DISTINCT r.id FROM r GROUP BY r.id
- SELECT DISTINCT r.id, Sum(r.id) as sum_a FROM r GROUP BY r.id
- SELECT Count(1) FROM (SELECT DISTINCT r.id FROM root r)
- SELECT Avg(1) AS avg FROM root r 

It's important to note that ODE might not always retrieve the query plan and, as a result, is not able to disallow or turn off for unsupported queries. For example, after partition split, such queries are no longer eligible for ODE and, therefore, won't run because client-side query plan evaluation will block those. To ensure compatibility/service continuity, it's critical to ensure that only queries that are fully supported in scenarios without ODE (that is, they execute and produce the correct result in the general multi-partition case) are used with ODE.

Use local Query Plan generation

The SQL SDK includes a native ServiceInterop.dll to parse and optimize queries locally. ServiceInterop.dll is supported only on the Windows x64 platform. The following types of applications use 32-bit host processing by default. To change host processing to 64-bit processing, follow these steps, based on the type of your application:

• For executable applications, you can change host processing by setting the platform target to x64 in the Project Properties window, on the Build tab.

• For VSTest-based test projects, you can change host processing by selecting Test > Test Settings > Default Processor Architecture as X64 on the Visual Studio Test menu.

• For locally deployed ASP.NET web applications, you can change host processing by selecting Use the 64-bit version of IIS Express for web sites and projects under Tools > Options > Projects and Solutions > Web Projects.

• For ASP.NET web applications deployed on Azure, you can change host processing by selecting the 64-bit platform in Application settings in the Azure portal.

Use single partition queries

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle' AND c.state = 'Shanghai'"
{
    // ...
}

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle'",
    requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Washington")}))
{
    // ...
}

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        PartitionKey = new PartitionKey("Washington")
    }).AsDocumentQuery();

Avoid recreating the iterator unnecessarily

When all the query results are consumed by the current component, you don't need to re-create the iterator with the continuation for every page. Always prefer to drain the query fully unless the pagination is controlled by another calling component:

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle'",
    requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Washington")}))
{
    while (feedIterator.HasMoreResults) 
    {
        foreach(MyItem document in await feedIterator.ReadNextAsync())
        {
            // Iterate through documents
        }
    }
}

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        PartitionKey = new PartitionKey("Washington")
    }).AsDocumentQuery();
while (query.HasMoreResults) 
{
    foreach(Document document in await queryable.ExecuteNextAsync())
    {
        // Iterate through documents
    }
}

Tune the degree of parallelism

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle'",
    requestOptions: new QueryRequestOptions() { 
        PartitionKey = new PartitionKey("Washington"),
        MaxConcurrency = -1 }))
{
    // ...
}

IDocumentQuery<dynamic> query = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT * FROM c WHERE c.city = 'Seattle'", 
    new FeedOptions 
    { 
        MaxDegreeOfParallelism = -1, 
        EnableCrossPartitionQuery = true 
    }).AsDocumentQuery();

Let's assume that

• D = Default Maximum number of parallel tasks (= total number of processors in the client machine)
• P = User-specified maximum number of parallel tasks
• N = Number of partitions that needs to be visited for answering a query

Following are implications of how the parallel queries would behave for different values of P.

• (P == 0) => Serial Mode
• (P == 1) => Maximum of one task
• (P > 1) => Min (P, N) parallel tasks
• (P < 1) => Min (N, D) parallel tasks

Tune the page size

When you issue a SQL query, the results are returned in a segmented fashion if the result set is too large.

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle'",
    requestOptions: new QueryRequestOptions() { 
        PartitionKey = new PartitionKey("Washington"),
        MaxItemCount = 1000}))
{
    // ...
}

IQueryable<dynamic> authorResults = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT p.Author FROM Pages p WHERE p.Title = 'About Seattle'", 
    new FeedOptions { MaxItemCount = 1000 });

When a query is executed, the resulting data is sent within a TCP packet. If you specify too low a value for MaxItemCount, the number of trips required to send the data within the TCP packet is high, which affects performance. So if you're not sure what value to set for the MaxItemCount property, it's best to set it to -1 and let the SDK choose the default value.

Tune the buffer size

using (FeedIterator<MyItem> feedIterator = container.GetItemQueryIterator<MyItem>(
    "SELECT * FROM c WHERE c.city = 'Seattle'",
    requestOptions: new QueryRequestOptions() { 
        PartitionKey = new PartitionKey("Washington"),
        MaxBufferedItemCount = -1}))
{
    // ...
}

IQueryable<dynamic> authorResults = client.CreateDocumentQuery(
    UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName), 
    "SELECT p.Author FROM Pages p WHERE p.Title = 'About Seattle'", 
    new FeedOptions { MaxBufferedItemCount = -1 });

Pre-fetching works the same way regardless of the degree of parallelism, and there's a single buffer for the data from all partitions.

Next steps

To learn more about performance using the .NET SDK:

• Best practices for Azure Cosmos DB .NET SDK
• Performance tips for Azure Cosmos DB .NET V3 SDK
• Performance tips for Azure Cosmos DB .NET V2 SDK

Reduce Query Plan calls

To execute a query, a query plan needs to be built. This in general represents a network request to the Azure Cosmos DB Gateway, which adds to the latency of the query operation.

Use Query Plan caching

The query plan, for a query scoped to a single partition, is cached on the client. This eliminates the need to make a call to the gateway to retrieve the query plan after the first call. The key for the cached query plan is the SQL query string. You need to make sure the query is parametrized. If not, the query plan cache lookup will often be a cache miss as the query string is unlikely to be identical across calls. Query plan caching is enabled by default for Java SDK version 4.20.0 and above and for Spring Data Azure Cosmos DB SDK version 3.13.0 and above.

Use parametrized single partition queries

For parametrized queries that are scoped to a partition key with setPartitionKey in CosmosQueryRequestOptions and contain no aggregations (including Distinct, DCount, Group By), the query plan can be avoided:

CosmosQueryRequestOptions options = new CosmosQueryRequestOptions();
options.setPartitionKey(new PartitionKey("Washington"));

ArrayList<SqlParameter> paramList = new ArrayList<SqlParameter>();
paramList.add(new SqlParameter("@city", "Seattle"));
SqlQuerySpec querySpec = new SqlQuerySpec(
        "SELECT * FROM c WHERE c.city = @city",
        paramList);

//  Sync API
CosmosPagedIterable<MyItem> filteredItems = 
    container.queryItems(querySpec, options, MyItem.class);

//  Async API
CosmosPagedFlux<MyItem> filteredItems = 
    asyncContainer.queryItems(querySpec, options, MyItem.class);

Tune the degree of parallelism

Parallel queries work by querying multiple partitions in parallel. However, data from an individual partitioned container is fetched serially with respect to the query. So, use setMaxDegreeOfParallelism on CosmosQueryRequestOptions to set the value to the number of partitions you have. If you don't know the number of partitions, you can use setMaxDegreeOfParallelism to set a high number, and the system chooses the minimum (number of partitions, user provided input) as the maximum degree of parallelism. Setting the value to -1 will let the SDK decide the optimal concurrency.

It is important to note that parallel queries produce the best benefits if the data is evenly distributed across all partitions with respect to the query. If the partitioned container is partitioned such a way that all or a majority of the data returned by a query is concentrated in a few partitions (one partition in worst case), then the performance of the query would be degraded.

CosmosQueryRequestOptions options = new CosmosQueryRequestOptions();
options.setPartitionKey(new PartitionKey("Washington"));
options.setMaxDegreeOfParallelism(-1);

// Define the query

//  Sync API
CosmosPagedIterable<MyItem> filteredItems = 
    container.queryItems(querySpec, options, MyItem.class);

//  Async API
CosmosPagedFlux<MyItem> filteredItems = 
    asyncContainer.queryItems(querySpec, options, MyItem.class);

Let's assume that

• D = Default Maximum number of parallel tasks (= total number of processors in the client machine)
• P = User-specified maximum number of parallel tasks
• N = Number of partitions that needs to be visited for answering a query

Following are implications of how the parallel queries would behave for different values of P.

• (P == 0) => Serial Mode
• (P == 1) => Maximum of one task
• (P > 1) => Min (P, N) parallel tasks
• (P == -1) => Min (N, D) parallel tasks

Tune the page size

When you issue a SQL query, the results are returned in a segmented fashion if the result set is too large. By default, results are returned in chunks of 100 items or 4 MB, whichever limit is hit first. Increasing the page size will reduce the number of round trips required and increase performance for queries that return more than 100 items. If you're not sure what value to set, 1000 is typically a good choice. Memory consumption will increase as page size increases, so if your workload is memory sensitive consider a lower value.

You can use the pageSize parameter in iterableByPage() for sync API and byPage() for async API, to define a page size:

//  Sync API
Iterable<FeedResponse<MyItem>> filteredItemsAsPages =
    container.queryItems(querySpec, options, MyItem.class).iterableByPage(continuationToken,pageSize);

for (FeedResponse<MyItem> page : filteredItemsAsPages) {
    for (MyItem item : page.getResults()) {
        //...
    }
}

//  Async API
Flux<FeedResponse<MyItem>> filteredItemsAsPages =
    asyncContainer.queryItems(querySpec, options, MyItem.class).byPage(continuationToken,pageSize);

filteredItemsAsPages.map(page -> {
    for (MyItem item : page.getResults()) {
        //...
    }
}).subscribe();

Tune the buffer size

Parallel query is designed to pre-fetch results while the current batch of results is being processed by the client. The pre-fetching helps in overall latency improvement of a query. setMaxBufferedItemCount in CosmosQueryRequestOptions limits the number of pre-fetched results. To maximize the pre-fetching, set the maxBufferedItemCount to a higher number than the pageSize (NOTE: This can also result in high memory consumption). To minimize the pre-fetching, set the maxBufferedItemCount equal to the pageSize. If you set this value to 0, the system will automatically determine the number of items to buffer.

CosmosQueryRequestOptions options = new CosmosQueryRequestOptions();
options.setPartitionKey(new PartitionKey("Washington"));
options.setMaxBufferedItemCount(-1);

// Define the query

//  Sync API
CosmosPagedIterable<MyItem> filteredItems = 
    container.queryItems(querySpec, options, MyItem.class);

//  Async API
CosmosPagedFlux<MyItem> filteredItems = 
    asyncContainer.queryItems(querySpec, options, MyItem.class);

Pre-fetching works the same way regardless of the degree of parallelism, and there's a single buffer for the data from all partitions.

Next steps

To learn more about performance using the Java SDK:

• Best practices for Azure Cosmos DB Java V4 SDK
• Performance tips for Azure Cosmos DB Java V4 SDK

Reduce Query Plan calls

To execute a query, a query plan needs to be built. This in general represents a network request to the Azure Cosmos DB Gateway, which adds to the latency of the query operation. There is a way to remove this request and reduce the latency of the single partition query operation. For single partition queries specify the partition key value for the item and pass it as partition_key argument:

items = container.query_items(
        query="SELECT * FROM r where r.city = 'Seattle'",
        partition_key="Washington"
    )

Tune the page size

When you issue a SQL query, the results are returned in a segmented fashion if the result set is too large. The max_item_count allows you to set the maximum number of items to be returned in the enumeration operation.

items = container.query_items(
        query="SELECT * FROM r where r.city = 'Seattle'",
        partition_key="Washington",
        max_item_count=1000
    )

Next steps

To learn more about using the Python SDK for API for NoSQL:

• Azure Cosmos DB Python SDK for API for NoSQL
• Quickstart: Azure Cosmos DB for NoSQL client library for Python

Reduce Query Plan calls

To execute a query, a query plan needs to be built. This in general represents a network request to the Azure Cosmos DB Gateway, which adds to the latency of the query operation. There is a way to remove this request and reduce the latency of the single partition query operation. For single partition queries scoping a query to a single partition can be accomplished two ways.

Using a parameterized query expression and specifying partition key in query statement. The query is programmatically composed to SELECT * FROM todo t WHERE t.partitionKey = 'Bikes, Touring Bikes':

// find all items with same categoryId (partitionKey)
const querySpec = {
    query: "select * from products p where p.categoryId=@categoryId",
    parameters: [
        {
            name: "@categoryId",
            value: "Bikes, Touring Bikes"
        }
    ]
};

// Get items 
const { resources } = await container.items.query(querySpec).fetchAll();

for (const item of resources) {
    console.log(`${item.id}: ${item.name}, ${item.sku}`);
}

Or specify partitionKey in FeedOptions and pass it as argument:

const querySpec = {
    query: "select * from products p"
};

const { resources } = await container.items.query(querySpec, { partitionKey: "Bikes, Touring Bikes" }).fetchAll();

for (const item of resources) {
    console.log(`${item.id}: ${item.name}, ${item.sku}`);
}

Tune the page size

When you issue a SQL query, the results are returned in a segmented fashion if the result set is too large. The maxItemCount allows you to set the maximum number of items to be returned in the enumeration operation.

const querySpec = {
    query: "select * from products p where p.categoryId=@categoryId",
    parameters: [
        {
            name: "@categoryId",
            value: items[2].categoryId
        }
    ]
};

const { resources } = await container.items.query(querySpec, { maxItemCount: 1000 }).fetchAll();

for (const item of resources) {
    console.log(`${item.id}: ${item.name}, ${item.sku}`);
}

Next steps

To learn more about using the Node.js SDK for API for NoSQL:

• Azure Cosmos DB Node.js SDK for API for NoSQL
• Quickstart - Azure Cosmos DB for NoSQL client library for Node.js