Part 2<\/a> looks at performance tuning and monitoring.<\/p>\n For our scenario, we need to store data from sports events (e.g., marathon, triathlon, cycling, etc.). Users should be able to select an event and view a leaderboard. The amount of data that will be stored is estimated at 100 GB.<\/p>\n The schema of the data is different for various events and likely to change. As a result, this requires the database to be schema-agnostic and therefore we decided to use Azure Cosmos DB as our database. For more information, see Understanding the differences between Azure Cosmos DB NoSQL and relational databases | Microsoft Docs<\/a>.<\/p>\n To design an efficient data model it is important to understand how the client application will interact with Azure Cosmos DB. The most important questions are:<\/p>\n If the access pattern is read-heavy you want to choose a partition key that appears frequently as a filter in your queries. Queries can be efficiently routed to only the relevant physical partitions by including the partition key in the filter predicate.<\/p>\n When the access pattern is write-heavy you might want to choose item ID as the partition key. Item ID does a great job with evenly balancing partitioned throughput (RUs) and data storage since it\u2019s a unique value. For more information, see Partitioning and horizontal scaling in Azure Cosmos DB | Microsoft Docs<\/a>.<\/p>\n Finally, we need to understand the document size. 1 kb documents are very efficient in Azure Cosmos DB. To understand the impact of large documents on RU utilization see the capacity calculator<\/a> and change the item size to a larger value.\u00a0As a starting point you should start with only one container and embed all values of an entity in a single JSON document. This provides the best reading performance. However, if your document size is unpredictable and can grow to hundreds of kilobytes you might want to split these in different documents within the same container.\u00a0For more information, see Modeling data in Azure Cosmos DB – Azure Cosmos DB | Microsoft Docs<\/a>.<\/p>\n Let\u2019s apply the considerations mentioned above to our scenario:<\/p>\n For our scenario, we expect far more reads than writes. Therefore we will optimize our Azure Cosmos DB instance for read access. The main queries for our scenario are:<\/p>\n Except for query 2, all these queries are using Eventname <\/em>as a filter. The amount of data stored per event is expected to be 1 GB which is well below the 20 GB limit<\/a> of a logical partition. Therefore Eventname<\/em> is a great fit as partition key. The document size for our sports events is small; less than 1 kb. Below is an example of such a document. Depending on the event the columns might change.<\/p>\n Now that we have a good understanding of the data model, we can create our Azure Cosmos DB account. For more information, see Create an Azure Cosmos account, database, and container from the Azure portal. <\/a>Once the account is created a new database can be created using the Data Explorer:<\/span><\/p>\n We will not provision throughput on the database level. Provisioning throughput on the database level is useful when you need multiple containers which have a different utilization pattern. Since we only use a single container and we want predictable throughput for our container, we will provision throughput on the container level. For more information, see Provision throughput on Azure Cosmos containers and databases<\/a><\/p>\n Once the database is created a new container can be created:<\/p>\n At the container level the partition key is specified, which in our case is \/Eventname. We also provision throughput manually and select 1000 RU as a starting point. You can always change the amount of RU provisioned, or switch to autoscale at any moment in time. This doesn\u2019t cause any downtime. For more information, see How to choose between manual and autoscale on Azure Cosmos DB<\/a>.<\/p>\n Once the container is created, we can add a few documents by selecting items and clicking new item.<\/p>\n Now we have added a few documents, we click the Query button and test one of our main queries. When clicking on Query Stats we see useful information about the RU consumption and the efficiency of our query. When writing queries for Azure Cosmos DB always validate how efficient your queries are.\u00a0 For more information, see Troubleshoot query issues when using Azure Cosmos DB<\/a><\/p>\n Now our data model is defined and our Azure Cosmos DB instance is configured we will create the first version of our application using the Client SDK. The Client SDK is available for multiple languages, but for our scenario we will choose the latest stable .NET client library. For more information, see Quickstart – Build a .NET console app to manage Azure Cosmos DB SQL API resources<\/a><\/p>\n It is highly recommended to use a singleton Azure Cosmos DB client for the lifetime of your application. The reason for this is that initiating the client object is an expensive operation. Once initialized the client object addresses connection management and caching. For more information, see Azure Cosmos DB performance tips for .NET SDK v3<\/a><\/p>\n Adding an item using the Client SDK is straightforward:<\/p>\n The ItemResponse object<\/a> has a property called RequestCharge. This property displays the RU\u2019s being used to perform the insert operation. It is recommended to log this value for troubleshooting purposes. For more information, see Find request unit (RU) charge for a SQL query in Azure Cosmos DB<\/a><\/p>\n In our case the Insert operation consumes 9 RU<\/strong>.<\/p>\n To prepare and parameterize a query we will use the QueryDefinition<\/a> object. For creating a query we can use the GetQueryItemIterator<\/a> method:<\/p>\n The GetQueryItemIterator returns a FeedIterator<\/a> object. With this object, we can loop through the items. You will notice that the SDK applies query pagination. By default, a maximum of 1000 items are returned at once. This can be changed by adjusting the MaxItemCount of the QueryRequestOptions<\/a> object. Using the method ReadNextAsync()<\/a> method we can iterate through the entire result set. It is important to understand each iteration consumes RUs.<\/p>\n The table below shows the RUs consumed for each query.<\/p>\nRelational or NoSQL<\/span><\/h2>\n
Identify access patterns<\/span><\/h2>\n
\n
Our scenario<\/h3>\n
Query 1: View top ranked participants for a selected event<\/strong><\/h5>\n
SELECT * FROM c\u00a0\r\nWHERE c.Eventname = '<eventname>'\r\nAND c.Eventdate = '<eventdate'\r\nORDER BY c.TotalScore asc<\/pre>\n
Query 2: View all events for a selected year a person has participated in<\/strong><\/h5>\n
SELECT\u00a0c.Eventname\u00a0FROM\u00a0c\u00a0\r\nWHERE c.Eventdate\u00a0>\u00a0'<startdate>'\u00a0AND c.Eventdate\u00a0<\u00a0'<enddate>'\r\nAND c.ParticipantId\u00a0=\u00a0<id><\/pre>\n
Query 3: View all registered participants per event<\/strong><\/h5>\n
SELECT\u00a0c.ParticipantFirstname,\u00a0c.ParticipantLastname,\u00a0c.ParticipantId\u00a0\u00a0FROM\u00a0c\u00a0\r\nWHERE c.Eventname\u00a0=\u00a0'<eventname>'<\/pre>\n
Query 4: View total score for a single participant per event<\/strong><\/h5>\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
\"id\": \"d5c137ae-5954-4eb2-9a7b-59ad3e65236d\",\r\n\"Eventname\": \"Triathlon Amsterdam\",\r\n\"Eventdate\": \"2021-03-04\",\r\n\"ParticipantId\": 4713,\r\n\"ParticipantLastname\": \"Visser\",\r\n\"ParticipantFirstname\": \"Cyrille\",\r\n\"Category\": \"Olympic\",\r\n\"SwimmingScore\": \"03:17:16\",\r\n\"CyclingScore\": \"03:25:48\",\r\n\"RunningScore\": \"03:04:23\",\r\n\"TotalScore\": \"09:47:27\"<\/pre>\n
Configure Azure Cosmos DB<\/span><\/h2>\n
![\"Application](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2021\/08\/application-description-automatically-generated-w.png\")
<\/p>\n![\"Graphical](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2021\/08\/graphical-user-interface-text-application-email.png\")
<\/p>\n![\"Graphical](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2021\/08\/graphical-user-interface-application-description.png\")
<\/p>\n![\"Graphical](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2021\/08\/graphical-user-interface-text-application-descr.png\")
<\/p>\nUsing the Client SDK<\/span><\/h2>\n
Writing data<\/span><\/h2>\n
ItemResponse<dynamic> response = await _container.CreateItemAsync<dynamic>(item, new PartitionKey(partitionKeyValue));<\/pre>\n
Selecting data<\/span><\/h2>\n
List<dynamic> list = new List<dynamic>();\r\n\r\n QueryDefinition query = new QueryDefinition(\"SELECT c.TotalScore FROM c WHERE c.Eventname = @Eventname AND c.Eventdate = @Eventdate ORDER BY c.TotalScore ASC\")\r\n .WithParameter(\"@Eventname\", eventName)\r\n .WithParameter(\"@Eventdate\", eventDate);\r\n\r\n using (FeedIterator<dynamic> resultset = _container.GetItemQueryIterator<dynamic>(query))\r\n {\r\n while (resultset.HasMoreResults)\r\n {\r\n FeedResponse<dynamic> response = await resultset.ReadNextAsync();\r\n Console.WriteLine(\"Q1 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 }<\/pre>\n