CODEWITHSUNDEEP
azure-cosmosdb
jamesjara
jamesjara

Reputation: 554

We get data with ORDER BY ASC but NOT BY DESC

We got multiple odd scenarios.

For example:

a) We are unable to order by _ts : empty results

SELECT * FROM data ORDER BY data._ts DESC

b) We can ORDER BY ASC and we get Results(more than >100). But if we ORDER BY DESC we get Zero results, has no sense for us :( ,

Assuming that c is an integer, this is the behavior we are seeing:

SELECT * FROM data ORDER BY data.c ASC  = RESULTS
SELECT * FROM data ORDER BY data.c DESC = zero results

c) We have an UDF to do contains insesitive, but is not working for all cases, JS function its tested outside and IT is working, we don't understand SELECT * FROM data r where udf.TEST(r.c, "AS") = RESULTS SELECT * FROM data r where udf.TEST(r.c, "health") = zero results (but by other field I can find tha value)

Thanks a lot!

Upvotes: 1

Views: 2660

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

jamesjara and I synced offline... posting our discussion here for everyone else's benefit :)

1) Query response limits and continuation tokens

There are limits for how long a query will execute on DocumentDB. These limits include the query's resource consumption (you can ballpark this w/ the amount of provisioned RU/sec * 5 sec + an undisclosed buffer), response size (1mb), and timeout (5 sec).

If these limits are hit, then a partial set of results may be returned. The work done by the query execution is preserved by passing the state back in the form of a continuation token (x-ms-continuation in the HTTP response header). You can resume the execution of the query by passing the continuation token in a follow-up query. The Client SDKs make this interaction easier by automatically paging through results via toList() or toArray() (depending on the SDK flavor).

It's possible to get an empty page in the result. This can happen when the resource consumption limit is reached before the query engine finds the first result (e.g. when scanning through a collection to look for few documents in a large dataset).

2) ORDER BY and Index Policies

In order to use ORDER BY or range comparisons (<, >, etc) within your queries, you should specify an index policy that contains a Range index with the maximum precision (precision = -1) over the JSON properties used to sort with. This allows the query engine to take advantage of an index.

Otherwise, you can force a scan by specifying the x-ms-documentdb-query-enable-scan HTTP request header w/ the value set to true. In the client SDKs, this is exposed via the FeedOptions object.

Suggested Index Policy for ORDER BY:

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    {
      "path": "/*",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        },
        {
          "kind": "Range",
          "dataType": "String",
          "precision": -1
        }
      ]
    },
    {
      "path": "/_ts/?",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        }
      ]
    }
  ],
  "excludedPaths": []
}

3) UDFs and indexing

UDFs are not able to take advantage of indexes, and will result in a scan. Therefore, it is advised to include additional filters in your queries WHERE clause to reduce the amount of documents to be scanned.

Upvotes: 3

Related Questions