Azure Cosmos DB for NoSQL now features Query Advisor, designed to help you write faster and more efficient queries. Whether you’re optimizing for performance, cost, or scalability, Query Advisor provides actionable recommendations to help you get the most out of your data.

Why Query Optimization Matters

Azure Cosmos DB’s SQL API is flexible and expressive, allowing developers to query JSON data with familiar SQL-like syntax. But as applications grow in complexity, small differences in query structure can have a big impact on performance and Request Units (RUs).

For example, two queries that return the same result may differ dramatically in efficiency based on how predicates are written, whether filters use array operators or subqueries, and how indexes are leveraged.

Query Advisor analyzes your queries and offers targeted recommendations to help you:

• Reduce RU costs by identifying inefficient expressions or unnecessary filters.
• Improve query performance through more optimal query structures.
• Understand the “why” behind each suggestion, with explanations written in clear, developer-friendly language.

Query Advisor empowers developers to experiment safely. it doesn’t automatically rewrite or execute your query, but instead provides suggestions you can review, test, and adopt based on your scenario.

How It Works

When you submit a query for analysis, the Query Advisor runs a static analysis pass over your query plan, evaluating patterns that may cause high RU consumption, excessive scans, or unnecessary processing. It then returns a set of recommendations that include:

• A description of the potential issue.
• A rewritten version of the query that may perform better.
• A short explanation of why the change could help.

Example: Optimizing Array Queries

Let’s look at a simple but common case: searching inside arrays.

Suppose a user writes the following query:

SELECT VALUE ARRAY_CONTAINS(c.auditEvents, { "type": "invoice", "status": "Success" }, true)
FROM c

This query checks whether the auditEvents array contains an object with both type and status properties. It works correctly, but it’s not the most efficient way to express this condition. Query Advisor analyzes the query and suggests a substitute for the operation:

Suggestion: “Instead of ARRAY_CONTAINS, consider using VALUE EXISTS with a subquery. This approach can improve performance by reducing full array scans.”

The query can then be updated to:

SELECT VALUE EXISTS (
SELECT VALUE t
FROM t IN c.auditEvents
WHERE t.type = "invoice" AND t.status = "Success"
)
FROM c

This change can significantly reduce RU consumption in large collections, especially when auditEvents arrays vary in length or contain complex objects.

Using Query Advisor

You can enable query advisor capabilities by setting the PopulateQueryAdvice property in QueryRequestOptions to true. When not specified, PopulateQueryAdvice defaults to false. To access the advice, via the string property FeedOptions.QueryAdvice. Please note that the query advice is only returned on the first round trip and is unavailable on subsequent continuation calls.

Usage example

string query = "SELECT VALUE r.id FROM root r WHERE CONTAINS(r.name, 'Abc') ";

QueryRequestOptions requestOptions = new QueryRequestOptions() { PopulateQueryAdvice = true }

using FeedIterator<CosmosElement> itemQuery = testContainer.GetItemQueryIterator<CosmosElement>(
query, requestOptions: requestOptions);

string queryAdvice = null;

while (itemQuery.HasMoreResults)
{
   if (queryAdvice != null)
{
   break;
}
FeedResponse<Item> page = itemQuery.ReadNextAsync().Result;
queryAdvice = page.QueryAdvice;
}
Console.WriteLine(queryAdvice);

Example output

In this example query, we observe that there is one single advice, called QA1002:

QA1002: Instead of CONTAINS, consider using STARTSWITH or computed properties, which may improve performance. For more information, please visit https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/queryadvisor/QA1002

The query advice contains three important information:

• The Query Advice id: “QA1002”
• The advice description: “Instead of..”
• The link to the documentation

We encourage you to visit the provided link to learn more about the query advice where you can see further examples, detailed explanations, and suggestions to improve your query.

Additional Examples

Example Query

SELECT GetCurrentTicks()
FROM root r
WHERE GetCurrentTimestamp() > 10

Query Advice

QA1009: Instead of using GetCurrentTimestamp, consider using GetCurrentTimestampStatic, which may improve performance. For more information, please visit https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/queryadvisor/QA1009

QA1008: Instead of using GetCurrentTicks, consider using GetCurrentTicksStatic, which may improve performance. For more information, please visit https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/queryadvisor/QA1008

In this example, there are 2 pieces of advice returned by the Query Advisor, QA1008 and QA1009. Each piece of advice is separated into a new line in the QueryAdvice string.

Conclusion

Query Advisor is a powerful addition to Azure Cosmos DB for NoSQL, giving developers actionable insights to optimize queries for performance, cost, and scalability. By surfacing clear recommendations and explanations, it helps you make informed decisions without guesswork—so your applications stay fast and efficient as they grow. Ready to start optimizing your queries? Learn more about Query Advisor and explore best practices in our documentation located here.

About Azure Cosmos DB

Azure Cosmos DB is a fully managed and serverless NoSQL and vector database for modern app development, including AI applications. With its SLA-backed speed and availability as well as instant dynamic scalability, it is ideal for real-time NoSQL and MongoDB applications that require high performance and distributed computing over massive volumes of NoSQL and vector data.

Try Azure Cosmos DB for free here. To stay in the loop on Azure Cosmos DB updates, follow us on X, YouTube, and LinkedIn.