Efficient SQL Queries for Data Retrieval

1. Point Reads vs. SQL Queries: The Right Tool for the Job

We've established that a point read is a direct key/value lookup. An SQL query, on the other hand, uses the Cosmos DB query engine to search for items that match a specific filter. Even if a query targets a single item by its id and partitionKey, it is fundamentally different from a point read.

Let's clarify the distinction and its performance implications.

As you've just read, the key takeaways are:

• Point Read (ReadItemAsync): Sub-10ms latency, costs exactly 1 RU (for a 1KB item). Use this whenever you have the item's id and partitionKey.
• SQL Query: Variable latency and a minimum cost of ~2.3 RUs, even for the simplest query. It engages the query engine, which incurs a baseline cost.

This is a crucial design principle in Cosmos DB: optimize for point reads where possible, and write efficient queries for all other scenarios.

2. The Anatomy of a Cosmos DB SQL Query

The query language in Cosmos DB's SQL API uses a syntax that will be very familiar to you. You can use clauses like SELECT, FROM, WHERE, and ORDER BY to retrieve and organize your data.

Let's explore this using the Data Explorer in the Azure Portal. This provides a quick feedback loop for testing queries before implementing them in code. In your Azure Portal, navigate to your Cosmos DB account, open the Data Explorer, select your TasksDB database and Items container, and then click "New SQL Query".

Let's assume you've added a few more tasks to your container from the previous lesson's code. Here are some example queries you can run.

Here are some examples adapted for our TaskItem data model. Try them in the Data Explorer:

• Get all items:

  SELECT * FROM c
  

• Get items in a specific category (filtering):

  SELECT * FROM c WHERE c.category = "Personal"
  

• Get incomplete tasks, projecting only specific fields:

  SELECT c.id, c.Description FROM c WHERE c.IsComplete = false
  

• Get the top 2 oldest tasks (by GUID id):

  SELECT TOP 2 * FROM c ORDER BY c.id ASC
  

3. In-Partition vs. Cross-Partition Queries

This is the most critical concept for query performance in Cosmos DB. The choice of your partition key and how you use it in your queries has a massive impact on cost and speed.

• In-Partition Query: A query that includes an equality filter on the partition key (e.g., WHERE c.category = 'Work'). Cosmos DB is smart enough to route this query to the single logical partition that holds this data. This is very efficient.
• Cross-Partition Query: A query that does not filter on the partition key (e.g., WHERE c.IsComplete = true). Cosmos DB doesn't know where the matching items live, so it must "fan out" the query to all physical partitions and then aggregate the results. This is significantly more expensive and slower, especially as your data grows.

The following video provides an excellent visual and conceptual explanation of this.

To summarize the video's key points:

• Always try to write in-partition queries for your application's most frequent, latency-sensitive operations.
• Cross-partition queries are acceptable for less frequent or analytical workloads where higher latency and cost are tolerable.

4. Executing Queries with the .NET SDK

Now, let's execute these queries from the .NET console application you've been working on. The primary method for this is GetItemQueryIterator<T>. It takes a QueryDefinition and returns an iterator that you can use to read pages of results.

First, add a couple more items to your container so you have some data to query. Run the "Create" portion of your code from the last lesson a few times with different data (e.g., different Category and Description).

Now, let's add the query logic to your Program.cs.

Example 1: In-Partition Query

Here, we will query for all tasks within the "Personal" category. This is an efficient in-partition query.

// 1. Define an in-partition query
var sqlQueryText = "SELECT * FROM c WHERE c.category = @category";

Console.WriteLine($"\nRunning in-partition query: {sqlQueryText}");

QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText)
    .WithParameter("@category", "Personal");

// 2. Create the query iterator
FeedIterator<TaskItem> queryResultSetIterator = container.GetItemQueryIterator<TaskItem>(
    queryDefinition,
    // We don't need to specify QueryRequestOptions here, as the partition key is in the WHERE clause.
    // However, for cross-partition queries, you might set QueryRequestOptions.
    // For example: requestOptions: new QueryRequestOptions { MaxItemCount = 1 } to get 1 item per page.
);

// 3. Iterate over the results
while (queryResultSetIterator.HasMoreResults)
{
    FeedResponse<TaskItem> currentResultSet = await queryResultSetIterator.ReadNextAsync();
    Console.WriteLine($"\tRequest charge: {currentResultSet.RequestCharge} RUs");
    foreach (TaskItem task in currentResultSet)
    {
        Console.WriteLine($"\tFound item: {task.Id} - {task.Description}");
    }
}

Key Concepts:

• QueryDefinition: This class encapsulates your SQL query. Using parameters with @ (like @category) is the best practice to prevent injection attacks.
• GetItemQueryIterator<T>: This is the central method for querying. It returns a FeedIterator.
• FeedIterator<T>: You loop while (iterator.HasMoreResults) and call ReadNextAsync() inside to get one page of results at a time. The FeedResponse contains the list of items for that page and the RequestCharge for retrieving it.

Example 2: Cross-Partition Query

Now, let's find all tasks that are not yet complete, regardless of their category.

// 4. Define a cross-partition query
sqlQueryText = "SELECT * FROM c WHERE c.IsComplete = @isComplete";

Console.WriteLine($"\nRunning cross-partition query: {sqlQueryText}");

queryDefinition = new QueryDefinition(sqlQueryText)
    .WithParameter("@isComplete", false);

// 5. Create the query iterator
queryResultSetIterator = container.GetItemQueryIterator<TaskItem>(
    queryDefinition
    // Note: To enable cross-partition queries, no special flag is needed anymore with current SDKs.
    // The SDK handles it, but be aware of the performance cost.
);

// 6. Iterate over the results
double totalRequestCharge = 0;
while (queryResultSetIterator.HasMoreResults)
{
    FeedResponse<TaskItem> currentResultSet = await queryResultSetIterator.ReadNextAsync();
    totalRequestCharge += currentResultSet.RequestCharge;
    foreach (TaskItem task in currentResultSet)
    {
        Console.WriteLine($"\tFound item: {task.Id} - {task.Description}");
    }
}
Console.WriteLine($"\tTotal request charge for cross-partition query: {totalRequestCharge} RUs");

When you run this code, compare the RU charge for the in-partition query with the total RU charge for the cross-partition query. You will likely see that the cross-partition query is more expensive, as it has to fan out across all partitions.

Conclusion

Great job! You have now unlocked the ability to retrieve data from Cosmos DB with much more flexibility than before. You can write complex queries and, most importantly, you understand the performance implications of how you write them.

Key Takeaways:

• Point reads (ReadItemAsync) are the fastest and cheapest way to get an item by id and partitionKey.
• SQL queries (GetItemQueryIterator) are used for all other data retrieval scenarios.
• An in-partition query includes the partition key in the WHERE clause, making it efficient.
• A cross-partition query does not include the partition key, is more expensive, and should be used judiciously.
• Always use parameterized queries with QueryDefinition to prevent SQL injection.
• Results are fetched in pages using a FeedIterator.

Next Steps

We've talked a lot about Request Units (RUs) as the "currency" of Cosmos DB performance. You've seen how different operations have different RU costs. But how do you control your budget?

In our next lesson, we will focus on the learning outcome: "Provision and manage throughput (RU/s) at the database and container level." You will learn how to set, monitor, and scale the throughput for your Cosmos DB resources to balance performance and cost effectively.