{"id":1886,"date":"2020-10-22T09:00:28","date_gmt":"2020-10-22T16:00:28","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=1886"},"modified":"2020-12-14T11:24:08","modified_gmt":"2020-12-14T19:24:08","slug":"point-reads-versus-queries","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/point-reads-versus-queries\/","title":{"rendered":"Understanding the difference between point reads and queries in Azure Cosmos DB"},"content":{"rendered":"

This blog is part one of a series of blogs (read part two here<\/a>) where we\u2019ll demystify commonly confused concepts for developers learning how to query in the Core (SQL) API in Azure Cosmos DB.<\/p>\n

First, we’ll take an in-depth look at the differences between point reads and queries.<\/p>\n

There are two <\/strong>ways to read data in Azure Cosmos DB: point reads and queries. Most developers know that you can query data using Cosmos DB’s SQL query language, but not everyone realizes that point reads are an even more efficient way to read data. A point read is a key\/value lookup on a single item ID<\/em>\u00a0and partition key.<\/p>\n

Here are the major differences between point reads and queries:<\/p>\n\n\n\n\n\n\n\n
<\/td>\nPoint read<\/strong> (assumes 1 KB item)<\/td>\nQuery<\/strong><\/td>\n<\/tr>\n
Latency<\/strong><\/td>\nTypically less than 10 ms<\/td>\nVariable<\/td>\n<\/tr>\n
RU charge<\/strong><\/td>\n1 RU<\/td>\nAt least 2.3 RUs, variable<\/td>\n<\/tr>\n
Number of items returned<\/strong><\/td>\n1 item<\/td>\nUnlimited (if results size is too large, results are split across multiple pages)<\/td>\n<\/tr>\n
Include partition key?<\/strong><\/td>\nRequired<\/td>\nRecommended<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

In many cases, you can make simple changes in your app to rewrite simple queries as point reads. Most read-heavy workloads on Azure Cosmos D<\/span>B use a combination of both point reads and SQL queries. If you just need to read a single item, point reads are cheaper and faster than queries. Point reads can read the data directly and don’t require the query engine.<\/p>\n

Transforming queries to point reads:<\/strong><\/h2>\n

Let\u2019s imagine that Contoso Retail, a fictional company, has a large Cosmos orders <\/strong>container that has customers orders data. Each item in the orders <\/strong>container represents a separate customer\u2019s order and you can identify an order by its id <\/strong>value. The partition key of the orders <\/strong>container is customerId<\/strong>.<\/p>\n

Here\u2019s an example item in the orders<\/strong> container:<\/p>\n

{\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"id\" : \"order0103\",\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"customerId\": \"TimS1234\"\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"order details\" : \u2026.\r\n}<\/pre>\n
Contoso Retail\u2019s app runs the following query on the orders<\/strong> container very frequently:<\/p>\n
SELECT * <\/strong>\r\nFROM c<\/strong>\r\nWHERE c.id = \"order0103\" AND c.customerId = \"TimS1234\"<\/strong><\/pre>\n
This query will cost a little under 3 RU\u2019s when executed. Because the query has an equality filter on customerId<\/strong> (the partition key), the query is an in-partition query.<\/p>\n
While this query is efficient, it could be written as a point read. Here\u2019s an example using the .NET SDK and an Order <\/strong>entity:<\/p>\n
Order order = await container.ReadItemAsync<Order>(id: \"order0103\", partitionKey: new PartitionKey(\"TimS1234\"));<\/pre>\n
This point read operation will be slightly faster than the query and only use 1 RU<\/strong>. This equates to an RU cost savings of 66%<\/strong>! If the application ran this read operation frequently enough, this one line code change could translate to significant savings!<\/p>\n
\u00a0<\/strong><\/p>\n
Utilizing the id property and partition key:<\/strong><\/h2>\n
You can only do a point read if you have both the id and partition key of an item. The id and partition key combination are also a globally unique key for items within an Azure Cosmos container. Developers know to take great care in selecting a good partition key choice for their Cosmos container. However, it is not as widely known that assigning meaningful values to the id property are required for doing point reads.<\/p>\n
Here\u2019s a data model which simply assigns a random guid as the id<\/strong> value:<\/p>\n
{\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"id\": \"f2ef7v28f9802fa482\",\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"customerId\" : \"TimS1234\",\r\n           \"orderId\" : \"order0103\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"order details\" : \u2026.\r\n}\r\n\r\n<\/pre>\n
Consider this query, which filters on orderId<\/strong> (instead of id <\/strong>like in the previous example):<\/p>\n
SELECT * \r\nFROM c \r\nWHERE c.orderId = \"order0103\" AND c.customerId = \"TimS1234\"<\/strong><\/pre>\n
Unlike the previous query which searched for a specific id and partition key combination, this query includes the orderId<\/strong> instead. In order to rewrite this read as a point read, it is important that the operation include a specific id<\/strong> value. The id <\/strong>property is special, in the sense that no other properties in the item can be used to do point reads. In this case, it would be worthwhile to modify the data model to have the orderId <\/strong>\u00a0value stored as the id<\/strong> value. Assigning a meaningful id<\/strong> value makes it much more efficient to read data.<\/p>\n
Learn more:<\/p>\n