{"id":4422,"date":"2022-06-21T13:54:41","date_gmt":"2022-06-21T20:54:41","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=4422"},"modified":"2022-06-21T13:54:41","modified_gmt":"2022-06-21T20:54:41","slug":"introducing-a-new-system-function-and-optimized-query-operators","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/introducing-a-new-system-function-and-optimized-query-operators\/","title":{"rendered":"Introducing a new system function and optimized query operators"},"content":{"rendered":"

The query engine in Azure Cosmos DB Core (SQL) API<\/a> now has a new system function and optimizations for a set of query operations to better use the index. In this post we\u2019ll cover the new DateTimeBin function as well as improvements to GROUP BY, DISTINCT, OFFSET LIMIT, and JOIN.<\/span><\/p>\n

These example scenarios are for Contoso, a fictional online retailer that stores a product catalog of 1 million items in Azure Cosmos DB. They have 1.5 GB of data stored in a container partitioned by the id property. Here\u2019s an example document:<\/span>\u00a0<\/span><\/p>\n

\n
{ \r\n    \"id\":\u202f\"4e487804-a9a8-463c-b767-5fe307a86c47\",\r\n    \"Name\":\u202f\"Embroidered\u202fSilk\u202fShirt\", \r\n    \"Price\":\u202f35.0, \r\n    \"Category\":\u202f\"Clothing\", \r\n    \"Description\":\u202f\"Luxurious silk shirt with hand embroidered flowers.\",\r\n    \"FirstAvailable\":\u202f\"2021-06-21T00:00:00.0000000Z\",\r\n    \"CustomerRatings\":\u202f[ \r\n        { \r\n            \"Username\":\u202f\"User1\", \r\n            \"Stars\":\u202f5 \r\n        }, \r\n        { \r\n            \"Username\":\u202f\"User2\", \r\n            \"Stars\":\u202f4 \r\n        }, \r\n        { \r\n            \"Username\":\u202f\"User3\", \r\n            \"Stars\":\u202f5 \r\n        } \r\n    ] \r\n}\r\n\r\n<\/pre>\n<\/blockquote>\n
DateTimeBin<\/span> s<\/span>ystem <\/span>f<\/span>unction<\/span><\/span>\u00a0<\/span><\/h3>\n
Contoso wants to know how many products were made available each week in June so far. They can use DateTimeBin, a new system function that makes aggregation queries with dates even more useful for analyzing data.<\/span>\u00a0<\/span><\/p>\n
Contoso wrote the query below to achieve this:<\/span>\u00a0<\/span><\/p>\n
SELECT\u202fCount(1)\u202fas\u202fNewProduts,\u202fDateTimeBin(c.FirstAvailable,\u202f'd',\u202f7)\u202fAS\u202fDayAvailable \r\nFROM\u202fc \r\nWHERE\u202fc.FirstAvailable\u202f>\u202f\"2022-06-01T00:00:00.0000000Z\" \r\nGROUP\u202fBY\u202fDateTimeBin(c.FirstAvailable,\u202f'd',\u202f7)\r\n\r\n<\/pre>\n
When using the <\/span>DateTimeBin<\/span> function,<\/span> specify a<\/span> UTC<\/span> DateTime<\/span> string in the<\/span> format `<\/span>YYYY-MM<\/span>-DDThh:mm:ss<\/span>. <\/span>fffffffZ<\/span>`<\/span>,<\/span> a <\/span>unit of measurement to bin by<\/span>, and optionally <\/span>the size of the bins<\/span>. A full list of supported <\/span>parameters<\/span> can be found in <\/span>the<\/span> <\/span>documentation<\/span><\/span><\/span><\/span><\/a>.<\/span><\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
GROUP BY and DISTINCT optimizations<\/h3>\n
Contoso can also take advantage of <\/span>new improvements to <\/span>GROUP BY and DISTINCT<\/span> that allow <\/span>the<\/span>m<\/span> to <\/span>better utilize<\/span> the index.<\/span> Previously, <\/span>queries with these operators would use the index while evaluating <\/span>filters, but not for evaluating the operator itself.<\/span> With these new improvements these operators <\/span>can also<\/span> benefit from <\/span>either single or composite<\/span> indices<\/span>, leading to both increased performance and reduced <\/span>RU<\/span>s<\/span>.<\/span><\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
GROUP BY<\/h4>\n
In the query below, Contoso is using GROUP BY to get <\/span>the <\/span>average price<\/span> of products in each category<\/span>.<\/span> Because there is an aggregation on Price in addition to grouping by<\/span> Category, <\/span>this query would benefit from a composite index on those two properties.<\/span> If there were filter properties outside of the aggregation or grouping, a single index on those properties would <\/span>also be evaluated.<\/span><\/span>\u00a0<\/span><\/p>\n
SELECT\u202fAVG(c.Price),\u202fc.Category\u202f \r\nFROM\u202fc\u202f \r\nGROUP\u202fBY\u202fc.Category<\/pre>\n
Previous query charge:<\/em> 23,885.49 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Previous execution time:<\/em> 30.72 seconds<\/span>\u00a0<\/span><\/p>\n
New query charge:<\/em> 371.51 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0New execution time:<\/em> 6.35 seconds<\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
DISTINCT<\/h4>\n
Contoso is using <\/span>DISTINCT to get the name of <\/span>all of<\/span> their products that are over <\/span>$500. Because this query can now take advantage of a single index on Name<\/span>, it is much more efficient.<\/span><\/span>\u00a0<\/span><\/p>\n
SELECT\u202fDISTINCT\u202fc.Name\u202f \r\nFROM\u202fc \r\nWHERE\u202fc.Price\u202f>\u202f500<\/pre>\n
Previous query charge:<\/em> 16,545.58 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Previous execution time:<\/em> 47.38 seconds<\/span>\u00a0<\/span><\/p>\n
New query charge:<\/em> 1,005.8 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0New execution time:<\/em> 11.43 seconds<\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
OFFSET LIMIT optimizations<\/h3>\n
The query engine will now be able to avoid document loading<\/span> time<\/span> and parsing time for documents outside of the <\/span>offset<\/span> range. Queries with large documents and a high offset will see proportional benefits.<\/span> These improvements <\/span>can <\/span>be seen in queries with some combination of ORDER BY and filter clauses<\/span> like the <\/span>query below<\/span> from Contoso<\/span> which gets all products <\/span>in a given<\/span> price range by their <\/span>FirstAvailable<\/span> date<\/span>.<\/span> Queries with other operators<\/span> such as aggregations, joins, or subqueries <\/span>cannot take advantage of these improvements.<\/span><\/span>\u00a0<\/span><\/p>\n
SELECT\u202fc.Name,\u202fc.Price,\u202fc.FirstAvailable \r\nFROM\u202fc \r\nWHERE\u202fc.Price\u202f>\u202f15\u202fAND\u202fc.Price\u202f<\u202f50 \r\nORDER\u202fBY\u202fc.FirstAvailable\u202fDESC \r\nOFFSET\u202f1000\u202fLIMIT\u202f100<\/pre>\n
Previous query charge:<\/em> 332.92 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Previous execution time: <\/em>8.63 seconds<\/span>\u00a0<\/span><\/p>\n
New query charge:<\/em> 311.2 RUs<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 New execution time:<\/em> 7.01 seconds<\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
JOIN optimizations<\/h3>\n
Contoso lets users rate the products sold on their site from 1 \u2013 5 stars. <\/span>T<\/span>o <\/span>make sure their product quality is high, they want to check which of their products that <\/span>cost over $100<\/span> have a rating below 3 stars. To achieve <\/span>this Contoso can use JOIN to create a cross product of <\/span>customer ratings and products in their catalog.\u00a0<\/span><\/span>\u00a0<\/span><\/p>\n
SELECT\u202fc.Name,\u202fc.Price,\u202fr\u202fas\u202fRating \r\nFROM\u202fc \r\nJOIN\u202fr\u202fIN\u202fc.CustomerRatings \r\nWHERE\u202fc.Price\u202f>\u202f100 and r.Stars < 3 \r\n\r\n<\/pre>\n
Previously, <\/span>Contoso<\/span> had re-written this query to use a subquery<\/span> which is more efficient <\/span>w<\/span>hen there is a<\/span> large arra<\/span>y<\/span> and<\/span> a filter<\/span> on a property from th<\/span>at<\/span> array<\/span>. <\/span>With a subquery, the filter is applied before the JOIN to <\/span>ignore<\/span> irrelevant rows <\/span>before creating the<\/span> cross product instead of<\/span> after the join is applied.<\/span> In this case, Contoso wants to only look at customer ratings below 3 stars.<\/span><\/span>\u00a0<\/span><\/p>\n
SELECT\u202fc.Name,\u202fc.Price,\u202fRating \r\nFROM\u202fc \r\nJOIN\u202f(SELECT\u202fVALUE\u202fr\u202fFROM\u202fr\u202fIN\u202fc.CustomerRatings\u202fWHERE\u202fr.Stars\u202f<\u202f3)\u202fAS\u202fRating \r\nWHERE\u202fc.Price\u202f>\u202f100 \r\n\r\n<\/pre>\n
Now, with new improvements to JOIN, Contoso no longer <\/span>has to<\/span> explicitly write this subquery themselves. The query engine will now automatically push down the <\/span>filters while evaluating the cross product for you. This means that Contoso can now write this query in <\/span>its original,<\/span> simpler <\/span>form<\/span> to achieve the same performance.<\/span><\/span>\u00a0<\/span><\/p>\n
 <\/p>\n
Learn more<\/h3>\n