In this post, we’ll use a sample 5 GB dataset to show the significant reduction in RU consumption for many queries that utilize these improvements. To highlight the impact of these improvements, we’ll compare query RU charges with the RU charges if you had run these queries in early 2019. In the dataset, documents have the following structure:<\/p>\n
![\"SampleJSONDocument\"](\"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-content\/uploads\/sites\/52\/2020\/04\/sampleJSON-300x154.png\")
<\/p>\n
The arrivalTime property is defined for about 95% of the documents in this dataset. All other properties are defined for all documents.<\/p>\n
<\/p>\n
Aggregate performance improvements<\/strong><\/span><\/p>\n \u00adWe are excited to announce improved index utilization for aggregate functions in Azure Cosmos DB. The RU charge and execution time for many aggregate functions have significantly improved.<\/p>\n The following aggregate system functions<\/a> will now benefit from an index. They\u2019ll consume fewer RU\u2019s and execute with lower latency than before:<\/p>\n Here\u2019s an example query that finds the maximum airplane speed in our dataset where altitude is greater than 36,000:<\/p>\n Query:<\/strong><\/p>\n Original RU charge:<\/strong> 11,201.54 RUs<\/p>\n Current RU charge (with index improvements):<\/strong> 121.69 RUs<\/p>\n <\/p>\n Here\u2019s another example that computes the count of documents with \u201cLAX\u201d as the departure airport:<\/p>\n Query:<\/strong><\/p>\n Original RU charge<\/strong>: 12,823.04 RUs<\/p>\n Current RU charge (with index improvements):<\/strong> 24.58 RUs<\/p>\n <\/p>\n To utilize these query engine improvements, you should add an index<\/a> for properties in aggregate system functions.<\/p>\n <\/p>\n Performance improvements for inequality filters and filters on undefined values<\/strong><\/span><\/p>\n Queries with inequality filters or filters on undefined values can now be run more efficiently. Previously, these filters did not utilize the index. When executing a query, Azure Cosmos DB would first evaluate other less expensive filters (such as =, >, or <) in the query. If there were inequality filters or filters on undefined values remaining, the query engine would be required to load each of these documents. Since inequality filters and filters on undefined values now utilize the index, we can avoid loading these documents and see a significant improvement in RU charge.<\/p>\n Here\u2019s a full list of query filters with improvements:<\/p>\n If you have queries with these filters, you should add an index<\/a> for the relevant properties.<\/p>\n Here\u2019s an example query with an inequality filter:<\/p>\n Query:<\/strong><\/p>\n Original RU charge:<\/strong> 1,328.93 RUs<\/p>\n Current RU charge (with index improvements):<\/strong> 637.92 RUs<\/p>\n <\/p>\n Here\u2019s another example with a filter on an undefined value:<\/p>\n Query:<\/strong><\/p>\n Original RU charge:<\/strong> 11,296.10 RUs<\/p>\n Current RU charge (with index improvements):<\/strong> 26.34 RUs<\/p>\n <\/p>\n High precision dates and times:<\/strong><\/span><\/p>\n We have increased the precision from milliseconds to 100 nano seconds increments for the GetCurrentDateTime<\/strong> system function in Azure Cosmos DB. GetCurrentDateTime <\/strong>now returns the date in ISO 8601 format with seven-digit fractional second precision.<\/p>\n Here is an example:<\/p>\n Query:<\/strong><\/p>\n SELECT GetCurrentDateTime() AS currentUtcDateTime<\/p>\n Results: <\/strong><\/p>\n Next steps:<\/strong><\/p>\n We are constantly adding new query and indexing features to Azure Cosmos DB. This blog outlines a few recent query and indexing improvements for aggregates, inequality filters, and We are constantly adding new query and indexing features to Azure Cosmos DB. This blog outlines a few recent query and indexing improvements for aggregates, inequality filters, and\n
SELECT MAX(c.speed) FROM c WHERE c.altitude > 36000<\/pre>\n
SELECT COUNT(1) FROM c WHERE c.departureAirport = \u201cLAX\u201d<\/pre>\n
\n
SELECT TOP 2000 * FROM c WHERE c.departureAirport = \"ORD\" AND c.altitude < 10000 AND c.arrivalAirport NOT IN(\"JFK\", \"SEA\", \"EWR\", \"LHR\", \"LGA\",\"MCO\", \"SFO\",\"LAX\", \"DEN\", \"PHL\", \"SAN\")<\/pre>\n
SELECT TOP 10 * FROM c WHERE c. arrivalAirport = \u201cORD\u201d and NOT IS_DEFINED(c.arrivalTime)<\/pre>\n
[{\r\n\"currentUtcDateTime\": \"2020-04-10T21:31:49.1234567Z\"\r\n}]\r\n\r\n<\/pre>\n\n
\ndates and time system functions. This blog covers updates during the first four months of 2020.<\/p>\n","protected":false},"author":12128,"featured_media":61,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[14,818,19],"tags":[],"class_list":["post-773","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-core-sql-api","category-query","category-tips-and-tricks"],"acf":[],"blog_post_summary":"
\ndates and time system functions. This blog covers updates during the first four months of 2020.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/773","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/users\/12128"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/comments?post=773"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/posts\/773\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media\/61"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/media?parent=773"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/categories?post=773"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cosmosdb\/wp-json\/wp\/v2\/tags?post=773"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}