{"id":3352,"date":"2021-09-14T07:00:36","date_gmt":"2021-09-14T14:00:36","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=3352"},"modified":"2021-09-09T15:32:17","modified_gmt":"2021-09-09T22:32:17","slug":"getting-started-end-to-end-example-2","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/getting-started-end-to-end-example-2\/","title":{"rendered":"Getting started with Azure Cosmos DB \u2013 end to end example, part 2"},"content":{"rendered":"

This is the second in a two-part series in which we walk through the steps for realizing your first Azure Cosmos DB implementation. In this post, we will optimize the queries we created in part 1<\/a>.<\/p>\n

The table below shows the RUs consumed for each query.<\/p>\n\n\n\n\n\n\n\n
Query<\/strong><\/td>\nLoops<\/strong><\/td>\nItems returned<\/strong><\/td>\nRU consumption<\/strong><\/td>\n<\/tr>\n
1<\/td>\n6<\/td>\n5001<\/td>\n208<\/td>\n<\/tr>\n
2<\/td>\n1<\/td>\n10<\/td>\n3<\/td>\n<\/tr>\n
3<\/td>\n6<\/td>\n5002<\/td>\n191<\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n1<\/td>\n3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Performance tuning<\/span><\/h2>\n

Now that we have created the first version of our application, our next step is optimizing RU consumption. The first step in this process is to optimize the Indexing policy which is set on the container level. The default Indexing Policy indexes all the property paths of our JSON.<\/p>\n

\"A<\/p>\n

This has a negative impact on write operations since index management also consumes RUs. To optimize the Indexing Policy we need to examine which property paths are used as filters in our read queries. Only these property paths will be included in the policy as you can see below.<\/p>\n

\"A<\/p>\n

We now repeat the Insert operation which initially consumed 9 RUs. With the optimized Index Policy, this operation now takes 7 RUs – saving 2 RUs per item written to Azure Cosmos DB. This may seem not much, but in scenarios where an application is writing thousands of items every minute, optimizing the Index Policy can have a huge impact. For more information, see Manage indexing policies in Azure Cosmos DB<\/a>.<\/p>\n

Now that we have optimized writing items to Azure Cosmos Dhttps:\/\/docs.microsoft.com\/azure\/cosmos-db\/how-to-manage-indexing-policyB we will now take a look at optimizing our read queries.<\/p>\n

Optimizing Query 1<\/span><\/h3>\n

The purpose of query 1 is to return all top-ranked participants for a selected event. This is our initial query:<\/p>\n

SELECT\u00a0*\u00a0FROM\u00a0c\u00a0 \r\nWHERE c.Eventname\u00a0=\u00a0'<eventname>' \r\nAND c.Eventdate\u00a0=\u00a0'<eventdate' \r\nORDER BY c.TotalScore\u00a0asc<\/pre>\n
However, for our use case, we don\u2019t need to see all items. All we need to see is the first 100 participants. By using TOP and Order By we can optimize our query.<\/p>\n
The TOP keyword returns the first N number of items in an undefined order. It will simply stop processing any other items. By adding Order By to our query the items TOP touches become predictable: it\u2019s always the 100 highest scores. For more information, see SQL keywords for Azure Cosmos DB<\/a><\/p>\n
SELECT\u00a0TOP 100 *\u00a0FROM\u00a0c\u00a0\r\nWHERE c.Eventname\u00a0=\u00a0'<eventname>'\r\nAND c.Eventdate\u00a0=\u00a0'<eventdate'\r\nORDER BY c.TotalScore\u00a0asc<\/pre>\n
When running our test again the results quite different:<\/p>\n\n\n\n\n
Query<\/strong><\/td>\nItems returned<\/strong><\/td>\nInitial RU charge<\/strong><\/td>\nOptimized RU charge<\/strong><\/td>\n<\/tr>\n
1<\/td>\n100<\/td>\n208<\/td>\n7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Optimizing Query 2<\/span><\/h3>\n
The purpose of query 2 is to return all events for a year a person participated in.<\/p>\n
SELECT c.Eventname FROM c \r\nWHERE c.Eventdate > '<startdate>' AND c.Eventdate < '<enddate>'\r\nAND c.ParticipantId = '<id>'\r\n<\/pre>\n
This query doesn\u2019t use the partition key and therefor hits all physical partitions. However, we only have at most 100 GB of data in our container, which means a maximum of two physical partitions. The latency for querying two physical partitions is very minimal. Since it\u2019s also highly unlikely that a person will participate in hundreds of events in a single year, we will not optimize this query to use the partition key as a filter. For more information, see Partitioning and horizontal scaling in Azure Cosmos DB<\/a><\/p>\n
Optimizing Query 3<\/span><\/h3>\n
The purpose of query 3 is to return all participants for a given event.<\/p>\n
SELECT\u00a0c.ParticipantFirstname,\u00a0c.ParticipantLastname,\u00a0c.ParticipantId\u00a0\u00a0FROM\u00a0c\u00a0\r\nWHERE c.Eventname\u00a0=\u00a0'<eventname>'<\/pre>\n
In this case, we cannot use the TOP statement since we might need to show all the participants. However, it\u2019s very likely a user will not always browse through the entire list of participants. Therefor we can optimize this query by using pagination in our application and by using query pagination in Azure Cosmos DB.<\/p>\n
Query pagination for Azure Cosmos DB can be implemented by using a continuation token in our application. Continuation tokens can be used as a bookmark for the query’s progress. Query executions in Azure Cosmos DB are stateless on the server side and can be resumed at any time using this token. For more information, see Pagination in Azure Cosmos DB<\/a><\/p>\n
Our application will implement pagination and show 100 items per page. A user can click next to view the next 100 items. The first time Query 2 is executed we retrieve and store the continuation token in our application. We also make sure we set the MaxItemCount to 100. For any subsequent calls we will pass the continuation token to retrieve the next 100 items.<\/p>\n
            QueryDefinition query = new QueryDefinition(\"SELECT c.ParticipantFirstname, c.ParticipantLastname, c.ParticipantId  FROM c WHERE c.Eventname = @Eventname\")\r\n                .WithParameter(\"@Eventname\", eventName);\r\n\r\n            string continuationToken = string.Empty;\r\n            using (FeedIterator<dynamic> resultset = _container.GetItemQueryIterator<dynamic>(query, null, new QueryRequestOptions() { MaxItemCount = 100 }))\r\n            {\r\n                FeedResponse<dynamic> response = await resultset.ReadNextAsync();\r\n                Console.WriteLine(\"Q3 took {0} ms. RU consumed: {1}, Number of items : {2}\", response.Diagnostics.GetClientElapsedTime().TotalMilliseconds, response.RequestCharge, response.Count);\r\n\r\n                foreach (var item in response)\r\n                {\r\n                    list.Add(item);\r\n                }\r\n\r\n                continuationToken = response.ContinuationToken;\r\n            }<\/pre>\n
The code snippet above shows that we set MaxItemCount property of QueryRequestOptions object to 100. This instructs Azure Cosmos DB to only return 100 items. From the FeedResponse object, we grab the ContinuationToken value and cache this in our application. When the user decides to view the next 100 items in the application the query will be executed again. However, this time we add the continuation token value instructing Azure Cosmos DB to return the next 100 items.<\/p>\n
            QueryDefinition query = new QueryDefinition(\"SELECT c.ParticipantFirstname, c.ParticipantLastname, c.ParticipantId  FROM c WHERE c.Eventname = @Eventname\")\r\n                .WithParameter(\"@Eventname\", eventName);\r\n\r\n            using (FeedIterator<dynamic> resultset = _container.GetItemQueryIterator<dynamic>(query, continuationToken, new QueryRequestOptions() { MaxItemCount = 100 }))\r\n            {\r\n                FeedResponse<dynamic> response = await resultset.ReadNextAsync();\r\n                Console.WriteLine(\"Q3 took {0} ms. RU consumed: {1}, Number of items : {2}\", response.Diagnostics.GetClientElapsedTime().TotalMilliseconds, response.RequestCharge, response.Count);\r\n\r\n                foreach (var item in response)\r\n                {\r\n                    list.Add(item);\r\n                }\r\n\r\n                continuationToken = response.ContinuationToken;\r\n            }<\/pre>\n
When running our test again the results are:<\/p>\n\n\n\n\n
Query<\/strong><\/td>\nItems returned<\/strong><\/td>\nInitial RU charge<\/strong><\/td>\nOptimized RU charge<\/strong><\/td>\n<\/tr>\n
3<\/td>\n100<\/td>\n191<\/td>\n6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Optimizing Query 4<\/span><\/h3>\n
The purpose of query 4 is to show the total score for a single participant per event. This always returns one item.<\/p>\n
SELECT\u00a0c.ParticipantFirstname,\u00a0c.ParticipantLastname,\u00a0c.TotalScore\u00a0FROM\u00a0c\u00a0\r\nWHERE c.ParticipantId\u00a0=\u00a0'<id>'\r\nAND c.Eventname\u00a0=\u00a0'<eventname>'<\/pre>\n
When a query always returns one item and also uses the partition key as a filter we can optimize this by using a point read. A point read is a key value look up based on partition key value and item Id. This is the most efficient way of retrieving a single item in Azure Cosmos DB and always consumes 1 RU. Executing a point read can only be done in the SDK by using the ReadItemAsync method. For more information see, Optimizing the cost of your requests in Azure Cosmos DB<\/a><\/p>\n
ItemResponse<Marathon> response = await _container.ReadItemAsync<Marathon>(id, new PartitionKey(eventName));<\/pre>\n
When running our test again the results are:<\/p>\n\n\n\n\n
Query<\/strong><\/td>\nItems returned<\/strong><\/td>\nInitial RU charge<\/strong><\/td>\nOptimized RU charge<\/strong><\/td>\n<\/tr>\n
4<\/td>\n1<\/td>\n3<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Optimizing Indexing policy<\/span><\/h3>\n
Now our queries are optimized greatly, we can tune the index policy by optimizing our range indexes. When analyzing Query 1 we see that two filters in the WHERE clause and an Order By is used. In case of multiple filters we can leverage composite indexes to achieve faster index lookup time. For more information, see Azure Cosmos DB indexing policies | Microsoft Docs<\/a>.<\/p>\n
\"compositeIndexes\": [\r\n        [\r\n            {\r\n                \"path\": \"\/Eventname\",\r\n                \"order\": \"ascending\"\r\n            },\r\n            {\r\n                \"path\": \"\/Eventdate\",\r\n                \"order\": \"ascending\"\r\n            },\r\n            {\r\n                \"path\": \"\/TotalScore\",\r\n                \"order\": \"ascending\"\r\n            }\r\n        ]\r\n    ]<\/pre>\n
When running our test again the results are:<\/p>\n\n\n\n\n
Query<\/strong><\/td>\nItems returned<\/strong><\/td>\nInitial RU charge<\/strong><\/td>\nOptimized RU charge<\/strong><\/td>\n<\/tr>\n
1<\/td>\n100<\/td>\n7<\/td>\n6<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
We can see the RU consumption is slightly less and the index lookup time is around 10% faster. The differences will become even more noticeable on larger scale scenarios, where you might have over a million of documents in your container.<\/p>\n
Monitoring<\/span><\/h2>\n
Once our application is finished we need to understand how to monitor Azure Cosmos DB to detect any issues or performance bottlenecks. The following metrics are very useful to monitor :<\/p>\n