No response with a query by ID on Azure DocumentDB

1
votes

I'm currently facing very slow/ no response on a collection looking by ID. I have ~ 2 milion of documents in a partitioned collection. If lookup the document using the partitionKey and id the response is immediate

SELECT * FROM c WHERE c.partitionKey=123 AND c.id="20566-2"

if I try using only the id 

SELECT * FROM c WHERE c.id="20566-2"

the response never returns, java client seems freezed and I have the same situation using the Data Explorer from Azure Portal. I tried also looking up by another field that isn't the id or the partitionKey and the response always returns. When I try the select from Java client I always set the flag to enable cross partition query.

The next thing to try is to avoid the character "-" in the ID to test if this character blocks the query (anyway I didn't find anything on the documentation)

1
javaazureazure-cosmosdbnosql
Are you able to run the same query from the portal? - Mikhail Shilkov
No the same query from portal doesn't return anything - Federico Paparoni
Can you add your java code? I can help more with that... Roughly, when you are querying a partitioned table, you must provide a partition key, or turn on cross partition queries, which is off by default because you should be doing this rarely with nosql. - Dan Ciborowski - MSFT
The problem isn't related to Java code because also from portal the behavior is the same. If I query not using the partition key and not using id the query works, so I think the problem is related to partition and id - Federico Paparoni

1 Answers

1
votes

The issue is related to your Java code. Due to Azure DocumentDB Java SDK wrapped the DocumentDB REST APIs, according to the reference of REST API Query Documents, as @DanCiborowski-MSFT said, the header x-ms-documentdb-query-enablecrosspartition explains your issue reason as below.

Header: x-ms-documentdb-query-enablecrosspartition
Required/Type: Optional/Boolean
Description: If the collection is partitioned, this must be set to True to allow execution across multiple partitions. Queries that filter against a single partition key, or against single-partitioned collections do not need to set the header.

So you need to set True to enable cross partition for querying across multiple partitions without a partitionKey in where clause via pass a instance of class FeedOption to the method queryDocuments, as below.

FeedOptions queryOptions = new FeedOptions();
queryOptions.setEnableCrossPartitionQuery(true); // Enable query across multiple partitions
String collectionLink = collection.getSelfLink();
FeedResponse<Document> queryResults = documentClient.queryDocuments(
    collectionLink,
    "SELECT * FROM c WHERE c.id='20566-2'", queryOptions);