Tune a high-cost query in Python

In this exercise, you measure request charges and query metrics for a product catalog. You then rewrite a filter, add a composite index, and compare queries with and without a partition-key filter.

Record your measurements after each task. Request charges vary with your data and query configuration, so your values might differ from the examples.

This exercise takes approximately 45 minutes to complete.

Before you start

To complete this exercise, you need an Azure subscription with permission to create resources and assign roles.

If your lab environment isn’t set up yet, follow Set up your lab environment to install Visual Studio Code, Git, the Azure CLI, and PowerShell 7.

You also need Python 3.12 or 3.13 installed.

Set up your Azure Cosmos DB resources

The core exercises reuse an account prepared with the core profile, not the two-item account from the first portal exercise. Before skipping setup, open Allfiles/Labs/Shared in PowerShell, sign in with az login, and set $resourceGroup, $location, and $accountName to your recorded values. Run ./verify.ps1 -ResourceGroup $resourceGroup -AccountName $accountName -LabProfile core and continue only when it succeeds. In Data Explorer, confirm 295 items in cosmicworks/product and 237 in cosmicworks/productMeta with SELECT VALUE COUNT(1) FROM c.

If you have no verified core account, follow the setup steps below. To add missing resources to an existing lab account, pass its explicit -AccountName to setup rather than using a different module’s name prefix. Reseeding restores canonical items but doesn’t remove extra items or reset container policies. Resolve mismatches before continuing; don’t reset a shared account automatically.

For a repeat of this exercise, inspect the product indexing policy before recording a baseline. If the composite index from a previous run is still present, record that fact or use a fresh core lab account. Don’t treat the earlier optimized policy as the default baseline.

  1. Start Visual Studio Code.

  2. If you don’t have the lab code yet, clone the repository for DP-420: open the command palette with Ctrl+Shift+P, run Git: Clone, and enter the following URL. Choose a local folder when prompted. Otherwise, open the folder from your previous clone.

     https://github.com/microsoftlearning/dp-420-cosmos-db-dev

  3. Once the repository is cloned, open that local folder in Visual Studio Code.

  4. In the Explorer pane, browse to the Allfiles/Labs/Shared folder.

  5. Open the context menu for the folder and select Open in Integrated Terminal. If the terminal isn’t PowerShell, select the dropdown beside the + in the terminal toolbar and choose PowerShell.

  6. Sign in to the Azure CLI and follow the sign-in prompts for your environment.

     az login

  7. Set variables for the resource group (If a resource group was provided by your lab environment, use that name) and region you want to use. Change either value if you prefer a different resource group name or region.

     $resourceGroup = "ResourceGroup1"
 $location = "westus2"

  8. Run the setup script.

     ./setup.ps1 -ResourceGroup $resourceGroup -Location $location -NamePrefix dp420lab13 -LabProfile core

    Azure Cosmos DB account names have to be globally unique, so the script builds one for you by adding six random characters to the prefix, giving a name like dp420lab13a7f3k9.

  9. Wait for the script to finish. The whole script takes 5-10 minutes to run.

  10. Record the Account name and Account endpoint values the script prints. You need the endpoint later in this exercise, and it looks like https://<your-account-name>.documents.azure.com:443/.

Provisioned throughput values are in request units per second (RU/s). The script creates the following resources:

Resource Configuration
Azure Cosmos DB account API for NoSQL, with key-based authentication disabled
cosmicworks database Holds every container this learning path uses
product container Partitioned on /categoryId, autoscale up to 1,000 RU/s, loaded with 295 CosmicWorks products
productMeta container Partitioned on /type, autoscale up to 1,000 RU/s, loaded with product categories and tags
leases container Partitioned on /id, 400 RU/s, used by change feed exercises
operations container Partitioned on /categoryId, autoscale up to 1,000 RU/s, left empty
bulkload container Partitioned on /categoryId, autoscale up to 1,000 RU/s, left empty
Role assignment Cosmos DB Built-in Data Contributor, granted to your signed-in identity

Because key-based authentication is disabled, no key or connection string appears anywhere in this exercise. Every operation authenticates with the identity from your az login session, which is the recommended approach for new accounts.

📝 A new role assignment takes a few minutes to propagate. If a later step fails with a 403 error, wait a moment and try again.

  1. Set a variable for the account name so the Azure CLI commands in this exercise can use it.

     $accountName = "<your-account-name>"

Task 1: Record a baseline

Measure three operations: a point read, a name lookup using case conversion, and a query that filters by category and price.

  1. In the terminal, create a project folder and a virtual environment.

     mkdir query-tuning-lab
 cd query-tuning-lab
 python -m venv .venv
 .venv\Scripts\activate

    On Linux or macOS, activate the environment with source .venv/bin/activate.

  2. Install the two packages the application needs.

     pip install azure-cosmos azure-identity

  3. Create a file named measure.py and add the following code. Replace <cosmos-endpoint> with the account endpoint you recorded.

     from azure.cosmos import CosmosClient
 from azure.identity import AzureCliCredential

 ENDPOINT = "<cosmos-endpoint>"
 CATEGORY_ID = "75BF1ACB-168D-469C-9AA3-1FD26BB4EA4C"
 PRODUCT_ID = "BD340F0A-F661-4ED8-B36F-FBA7623605D9"

 # Reuse one client authenticated by az login for all measurements.
 client = CosmosClient(ENDPOINT, AzureCliCredential())
 product = client.get_database_client("cosmicworks").get_container_client("product")


 def charge_of_last_request():
     # Use only for the point read, immediately after that single request.
     # Queries can combine several service responses into one visible page.
     return float(product.client_connection.last_response_headers["x-ms-request-charge"])


 def measure(label, query, parameters):
     total = 0.0
     count = 0

     def record_response(headers, _):
         nonlocal total
         total += float(headers["x-ms-request-charge"])

     for page in product.query_items(
         query=query,
         parameters=parameters,
         enable_cross_partition_query=True,
         response_hook=record_response,
     ).by_page():
         # Consume all results so the hook captures every service response.
         count += len(list(page))

     print(f"{label:<24}: {total:>8.2f} RU for {count} items")


 # This point read uses an ID and partition key, providing a baseline
 # that doesn't require executing a SQL query.
 item = product.read_item(item=PRODUCT_ID, partition_key=CATEGORY_ID)
 print(f"{'point read':<24}: {charge_of_last_request():>8.2f} RU  ({item['name']})")

 measure(
     "UPPER(name) lookup",
     "SELECT * FROM c WHERE UPPER(CONCAT(c.name, '')) = @name",
     [{"name": "@name", "value": "TOURING-3000 BLUE, 62"}],
 )

 measure(
     "category and price",
     "SELECT * FROM c WHERE c.categoryId = @categoryId AND c.price > @floor",
     [
         {"name": "@categoryId", "value": CATEGORY_ID},
         {"name": "@floor", "value": 1000},
     ],
 )

  4. Run the application.

     python measure.py

    The output reports one charge per operation, and the item counts confirm the queries match the data you expect.

     point read              :     1.00 RU  (Touring-3000 Blue, 62)
 UPPER(name) lookup      :    12.86 RU for 1 items
 category and price      :     3.29 RU for 12 items

The point read is your reference for the same item and consistency level. A point read of a 1 KB item costs 1 request unit (RU) with session, consistent prefix, or eventual consistency. Strong and bounded staleness reads cost twice as much. Record the consistency level and all three charges before continuing.

Task 2: Inspect the same queries with query metrics

The UPPER(CONCAT(c.name, '')) query returns one item. The server-side metrics reveal how many documents the engine loaded to produce that result.

  1. In measure.py, replace the entire measure function with the following version. Leave the point read and both measure(...) calls below it unchanged.

    The response hook totals the request charge and query metrics across all service responses.

     def measure(label, query, parameters):
     total = 0.0
     retrieved = 0
     output = 0

     def record_response(headers, _):
         nonlocal total, retrieved, output
         total += float(headers["x-ms-request-charge"])
         raw = headers["x-ms-documentdb-query-metrics"]
         metrics = dict(pair.split("=", 1) for pair in raw.split(";") if pair)
         retrieved += int(metrics["retrievedDocumentCount"])
         output += int(metrics["outputDocumentCount"])

     for page in product.query_items(
         query=query,
         parameters=parameters,
         enable_cross_partition_query=True,
         populate_query_metrics=True,
         response_hook=record_response,
     ).by_page():
         list(page)

     print(f"{label:<24}: {total:>8.2f} RU  retrieved {retrieved:>4}  output {output:>4}")

  2. Run the application again.

     python measure.py

Example output:

UPPER(name) lookup      :    12.86 RU  retrieved  295  output    1
category and price      :     3.29 RU  retrieved   12  output   12

  1. Review the measurements:

    • Retrieved counts documents loaded by the engine; output counts documents returned. Retrieved greater than output is evidence of document loading that didn’t contribute to the result.
    • The name query retrieves 295 documents but returns 1, demonstrating a full-container document scan.
    • The category and price query retrieves and returns 12 documents.

Record both queries’ retrieved counts, output counts, and request charges for comparison in Task 3.

Task 3: Rewrite the filter so the index serves it

Direct property equality is an index-friendly alternative to the baseline expression. For this lookup, you know the exact stored product name, so you can compare it directly without converting its casing.

  1. In measure.py, replace only the measure(...) call labeled UPPER(name) lookup with the following call. Leave the measurement function and the category and price query unchanged.

     # Compare the indexed property directly, using the name's stored casing.
 measure(
     "direct name match",
     "SELECT * FROM c WHERE c.name = @name",
     [{"name": "@name", "value": "Touring-3000 Blue, 62"}],
 )

  2. Run python measure.py again. Confirm that the rewritten query still returns the same product, then compare its charge and retrieved count with your Task 2 measurements. Example output:

     direct name match       :     2.87 RU  retrieved    1  output    1

The retrieved count drops from 295 to 1 and the request charge falls. The default policy already indexes /name/?, so this improvement requires no indexing policy change.

Task 4: Add a composite index for the two-property filter

For the category and price query, retrieved 12 and output 12 indicate that the engine loaded only the documents it returned. A composite index can still help, because a query combining an equality filter with a range filter can be answered from a single composite index rather than by intersecting two range indexes.

  1. In measure.py, add the following imports at the top of the file:

     import base64
 import json

    In measure, replace the for statement with the following:

     for page in product.query_items(
     query=query,
     parameters=parameters,
     enable_cross_partition_query=True,
     populate_query_metrics=True,
     populate_index_metrics=True,
     response_hook=record_response,
 ).by_page():
     list(page)

  2. Add the following code at the end of the nested record_response function, after updating output, to print the index utilization.

     utilization = headers["x-ms-cosmos-index-utilization"]
 if isinstance(utilization, str):
     utilization = json.loads(base64.b64decode(utilization))

 print(f"\n{label} - index utilization")
 for section, entries in utilization.items():
     if not entries:
         continue
     heading = section.replace("SingleIndexes", " single indexes").replace(
         "CompositeIndexes", " composite indexes"
     )
     print(f"  {heading}")
     for entry in entries:
         spec = entry.get("IndexSpec") or ", ".join(entry.get("IndexSpecs", []))
         print(f"    {spec} (impact: {entry['IndexImpactScore']})")

  3. Run the application and read the index utilization section for category and price. Record which paths appear as utilized and whether any composite index appears under the potential section. Example output:

     category and price - index utilization
   Utilized single indexes
     /price/? (impact: High)
     /categoryId/? (impact: High)
   Potential composite indexes
     /categoryId ASC, /price ASC (impact: High)
 category and price      :     3.27 RU  retrieved   12  output   12

  4. For a fresh core container with the default policy, create a file named indexing-policy.json in your project folder with the following content. The equality filter comes first and the range filter last, which is the ordering a composite index requires.

    If you reuse a customized policy, copy its current JSON from Data Explorer > Settings > Indexing Policy instead. Add the composite entry below only if it isn’t already present. Preserve the other indexes and paths in that policy.

     {
   "indexingMode": "consistent",
   "automatic": true,
   "includedPaths": [
     {
       "path": "/*"
     }
   ],
   "excludedPaths": [
     {
       "path": "/\"_etag\"/?"
     }
   ],
   "compositeIndexes": [
     [
       {
         "path": "/categoryId",
         "order": "ascending"
       },
       {
         "path": "/price",
         "order": "ascending"
       }
     ]
   ]
 }

  5. Apply the policy to the product container.

     az cosmosdb sql container update `
     --account-name $accountName `
     --resource-group $resourceGroup `
     --database-name cosmicworks `
     --name product `
     --idx '@indexing-policy.json'

  6. In Data Explorer, open the product container’s Settings and check the index transformation progress. Wait until the transformation completes, then run the application again and compare the request charge and index utilization against the baseline.

The container remains available for reads and writes during index transformation.

Task 5: Compare fan-out with single-partition routing

The product container is partitioned on /categoryId. Every query so far that filtered on categoryId routed to one partition. This task runs the same price filter with and without that routing.

  1. Replace the existing measure(...) call filtering on category and price with the following two calls. Keep the point read and the direct name match call unchanged.

     # Without a partition-key equality filter, the query can visit every partition.
 measure(
     "price only (fan-out)",
     "SELECT * FROM c WHERE c.price > @floor",
     [{"name": "@floor", "value": 1000}],
 )

 # Adding /categoryId equality restricts the query to that category's partition.
 measure(
     "price within category",
     "SELECT * FROM c WHERE c.categoryId = @categoryId AND c.price > @floor",
     [
         {"name": "@categoryId", "value": CATEGORY_ID},
         {"name": "@floor", "value": 1000},
     ],
 )

  2. Run the application and compare the two lines.

     price only (fan-out)    :    10.44 RU  retrieved   86  output   86
 price within category   :     3.29 RU  retrieved   12  output   12

Both queries use indexes: retrieved and output counts are equal. The price-only query returns 86 products; the category-scoped query returns 12. If the container has one physical partition, the lower RU charge comes from returning fewer products. With multiple physical partitions, the category filter also avoids querying other physical partitions.

  1. In the Azure portal, open the account and select Metrics. Select Physical Partition Count with Maximum aggregation. Filter DatabaseName to cosmicworks, CollectionName to product, and Region to one account region. Use the latest metric sample and allow for reporting delay. A value of 1 means both queries run on the same physical partition.

Clean up resources

When you finish the course, delete the resource group only if you created it and every resource in it can be removed. If your lab provided ResourceGroup1, skip this command and delete only the exercise resources you no longer need:

az group delete --name $resourceGroup --yes --no-wait

If your lab environment provided the resource group, delete only the Azure Cosmos DB account instead:

az cosmosdb delete --name <your-account-name> --resource-group $resourceGroup --yes

The composite index you added in Task 4 stays on the product container until you remove it. Adding it preserves query results but can change query and write charges, including baselines in later exercises. Record the policy change before comparing costs in another exercise.

Review your measurements and identify which changes reduced the number of documents retrieved or the request charge.