DocumentDB paging support with TOP and more query improvements

We’re pleased to announce a number of improvements in how you can query Azure DocumentDB. With the latest service update, we have added the TOP keyword to our SQL grammar, made queries run faster and consume fewer resources, increased the limits for query operators, and added support for additional LINQ operators in the .NET SDK.

Here is the list of changes deployed:

New SQL keywords: TOP for paging and limiting results from a query.

Limit increases: You can now use up to 20 ORs and 200 items within an IN expression.

New LINQ operators: Support for a number of additional LINQ operators – including Take(), CompareTo(), conditional and coalesce operators.

In this post, we’ll take a look at what’s changed and how you can take advantage of these changes to build fast and scalable applications using Azure DocumentDB.

Paging and limiting query results with TOP

The DocumentDB SQL grammar now includes the TOP keyword, which can be used within SQL queries while querying DocumentDB. TOP can be used to page through query results or to limit the number of results coming back from a query.

SELECT TOP 100 *
FROM food
WHERE food.foodGroup = "Snacks"
ORDER BY food.description

TOP can be used within any valid DocumentDB query, and the number of items can also be supplied as a parameter via parameterized queries. You can also use TOP in combination with ORDER BY to implement MIN, MAX and FIRST semantics within DocumentDB queries.

Limit increases for various SQL operators

We recently announced a number of limit increases in the number of AND, JOIN and UDF operators supported within DocumentDB queries. We heard from many framework developers about the need for higher limits for OR and IN operators per query to build flexible query APIs. We’re happy to announce that we have lifted the following limits:

Maximum number of OR clauses per query, increased to 20

Maximum number of values specified in IN expressions per query, increased to 200

With this change, you can fetch larger batches of items by using IN, or the equivalent IEnumerable.Contains operator within LINQ, and limit the number of round trips to DocumentDB.

Performance improvements in the SQL query engine

We have also made a number of performance improvements in the DocumentDB query engine to reduce the request unit (RU) consumption of queries, reduce the latency of queries, and reduce the number of round trips required to serve long running queries.

Faster filtered sort queries

We added support for performing query logical plan optimizations across sort and filter expressions. For example, if you have queries using a filter and sort against the same property, they will be in an order of magnitude faster and require much fewer round trips.

If you use the Azure Search connector with DocumentDB, you’ll notice that your indexer consumes far fewer RUs and completes faster due to this optimization.

More generally, if you have an application that reads data in-order based on a range filter like shown below, your queries will consume fewer RUs and complete faster.

/* Now an order of magnitude faster! */
SELECT *
FROM deviceTelemetry
WHERE deviceTelemetry._ts > @highWaterMark
ORDER BY deviceTelemetry._ts

Performance Tip: If you have queries already using range filters, (using timestamp values), you should consider adding an ORDER BY clause against the same property to take advantage of this optimization.

Better index plan optimization

The DocumentDB query execution engine is now smarter in analyzing query filter expressions and performing runtime optimizations like re-ordering expressions and assessing tradeoffs between executing and skipping expensive filters. This change will result in a broad spectrum of queries executing faster, and consuming lesser RUs.

Improved incremental processing

DocumentDB supports long running queries via continuation tokens. DocumentDB allocates a fixed amount of time and resources to each query, and when that is reached, returns a continuation token which acts as a bookmark for resuming execution.

DocumentDB does not maintain additional state server side for a query; all of this is self-contained within the token, which means they never expire and can be cached by clients. We made significant improvements in how we encode state within continuations for queries with large result sets.

If you have queries like the one shown above, then you may see a 2-3x reduction in RU consumption and execution time.

New LINQ operators

The latest DocumentDB .NET SDK release 1.5.2 has expanded support for LINQ query translation including new operators for paging, conditional expressions and range comparison. With this change, you can write fluent LINQ expressions for more scenarios to interact dynamically with DocumentDB without having to build SQL strings. For a full list of supported LINQ operators, please refer to the LINQ documentation.

DocumentDB supports limiting the number of results from a query using the Take operator.

DocumentDB supports string range comparisons like in relational SQL. This is now supported in LINQ through the CompareTo operator. For example, this query retrieves the families with last names string from N-Z.

The SDK also supports translation from the C# conditional (?) and coalesce operators (??) into the corresponding DocumentDB operators. This change makes it easy to pass-through auto-generated LINQ expressions from frameworks like ODATA down to DocumentDB.