CODEWITHSUNDEEP
azure-cosmosdb
Sushma
Sushma

Reputation: 86

Cosmos DB "In" operator SQL Query

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

My approach to above requirement

  1. Since my key say customerId is unique I am making customerId as Id and Partition key also as /id.
  2. 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’)

  3. Following are my feed options:

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

Based on the above scenario:

  1. Am I actually utilizing the query by Id functionality well to achieve faster response time?

  2. Does EnableCrossPartitionQuery makes sense in the current scenario?

  3. Am I doing partioning right?

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

Upvotes: 0

Views: 1699

Answers (1)

Jay Gong
Jay Gong

Reputation: 23792

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.

Upvotes: 1

Related Questions