Query optimization improves database performance by finding unnecessary processing and reducing the resources queries consume. SQL may be easy to write, but keeping it from bogging down a busy database takes investigation. section 10.4.2
During a busy online sale, Banning Books uses Query Performance Insight to discover that a frequently run order-details query is consuming substantial database resources and slowing customer pages. section 10.4.2
Practical optimization moves include:
- Inspect resource consumption to identify the queries doing the most work. section 10.4.2
- Add indexes to columns used in JOIN and WHERE clauses when they are appropriate. section 10.4.2
- Use specific input criteria so Cosmos DB searches only relevant records instead of scanning an entire catalog. section 11.6.2
- Review indexing policies so only necessary fields are indexed, balancing query speed against storage cost. section 11.6.2
- Monitor repeatedly and adjust provisioning, scaling, and queries as workload demands change. section 11.6.2
A targeted order query
The Banning Books query joins Orders, OrderItems, Customers, and Books, then filters by a customer identifier. If it runs frequently over large tables, inspecting it can reveal opportunities for indexes or less frequent, off-peak execution. section 10.4.2
Listing 10.2 Query for the Banning Books database schema
SELECT Orders.OrderID, Customers.CustomerName, Books.Title, OrderItems.Quantity FROM Orders JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID JOIN Customers ON Orders.CustomerID = Customers.CustomerID JOIN Books ON OrderItems.BookID = Books.BookID WHERE Customers.CustomerID = @CustomerID;
Use the complete SQL listing to inspect the joins, selected columns, and customer filter that shape the query’s workload. section 10.4.2
C:/> az monitor metrics alert create --name "High RU Consumption"
--resource-group AlpacaRG --scopes /subscriptions/
{subscription-id}/resourceGroups/AlpacaRG/providers/Microsoft.DocumentDB/
databaseAccounts/alpacacosmosdbaccount
--condition "avg totalRequestUnits > 900" --window-size 5m
--evaluation-frequency 1m --action {action-group}
Use the Azure CLI alert example to watch for high Cosmos DB request-unit consumption during ongoing operations. section 11.6.2
