Cosmos DB "In" operator SQL Query

Question

I have a requirement to query by documents (cosmos DB) based on unique key in batch request.

My approach to above requirement

Since my key say customerId is unique I am making customerId as Id and Partition key also as /id.
Since its batch read request (25 calls/per request and 10 batch requests/sec) , I am utilizing “in” operator in SQL query and extracting all the documents by CosmosClient.CreateDocumentQuery function Ex: my SQL query would look like “Select * from c where c.Id in (‘a1’,’b1’)
Following are my feed options:

new FeedOptions { MaxItemCount = -1, EnableCrossPartitionQuery = true, MaxDegreeOfParallelism = -1, MaxBufferedItemCount = -1 }

Based on the above scenario:

Am I actually utilizing the query by Id functionality well to achieve faster response time?
Does EnableCrossPartitionQuery makes sense in the current scenario?
Am I doing partioning right?

Is there a better way I can utilize the capability of extracting the data is key value pair fashion?

Jay Gong · Accepted Answer · 2020-02-25 03:46:16Z

1

1.Does EnableCrossPartitionQuery makes sense in the current scenario?

Yes.Since your partition key is unique id,you need EnableCrossPartitionQuery =true to make sure the query could scan all the partitions when you query id column.

2.Am I actually utilizing the query by Id functionality well to achieve faster response time?Am I doing partioning right?

According to the scenario you mentioned in your question,i believe that you have already read Overview of Choosing partition key in cosmos db.Since you set MaxItemCount= -1(return all the filter data without pagination),you have to balance the RUs settings following the rules mentioned in the above link:

Azure Cosmos containers have a minimum throughput of 400 request units per second (RU/s). When throughput is provisioned on a database, minimum RUs per container is 100 request units per second (RU/s). Requests to the same partition key can't exceed the throughput that's allocated to a partition. If requests exceed the allocated throughput, requests are rate-limited. So, it's important to pick a partition key that doesn't result in "hot spots" within your application.

If you don't want to raise RUs settings,you may consider setting MaxItemCount = some page size and load all the data by continuation token.

3.Is there a better way I can utilize the capability of extracting the data is key value pair fashion?

If your deviceIds have some common naming rules,maybe you could consider below 2 ideas:

1.Adjust the index policy of devideId following this document:https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy

2.Try to create a synthetic partition key following this link:https://learn.microsoft.com/en-us/azure/cosmos-db/synthetic-partition-keys,for example,add some suffix before the devideId.

answered Feb 25, 2020 at 3:46

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sushma Over a year ago

Thanks @Jay Gong. I will go through these links

Jay Gong Over a year ago

@Sushma Sure,any concern,pls let me know.

Collectives™ on Stack Overflow

Cosmos DB "In" operator SQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related