Multiple partitions in COSMOS DB collection

Question

1) I have a Cosmos DB collection with about 500k documents and which is Partitioned by a property "SITEID". In the Query Request Options only one partition key value can be passed. In my case I have queries where the SITEID in (1,2,3,4) needs to be executed where SiteID is the partition key.

For example, my SP is as follows:

SELECT * FROM c WHERE c.SITEID IN
("SiteId1","SiteId2","SiteId3","SiteId4","SiteId5")
AND c.STATUS IN ("Status1","Status2","Status3","Status4")

I am Calling the above SP using the below SQL API code.

await client.ExecuteStoredProcedureAsync<string>(UriFactory.CreateStoredProcedureUri("DBName", "CollectionName", "Sample"),new RequestOptions { PartitionKey = new PartitionKey("SiteId1") })

In the above SQL API Code, PartitionKey property only supports a Single value. Where I need to pass several partition values. Is there any other options to do this?

2) "EnableCrossPartitionQuery" property is only availbale in the FeedOptions but not in the Request Options class. Client.ExecuteStoredProcedureAsync only supports the RequestOptions parameter not FeedOptions. Now I need to execute a Stored Procedure at once and across all partitions. Is there any other options to pass EnableCrossPartitionQuery in ExecuteStoredProcedureAsync method.

E.g)

client.CreateDocumentQuery<Doc>(UriFactory.CreateDocumentCollectionUri("DBName", "CollectionName"), "select * from c", new FeedOptions { EnableCrossPartitionQuery = true }).ToList()


await client.ExecuteStoredProcedureAsync<string>(UriFactory.CreateStoredProcedureUri("DBName", "CollectionName", "Sample"),new RequestOptions { PartitionKey = new PartitionKey("WGC") })

Nick Chapsas Nick Chapsas · Accepted Answer · 2018-11-08T11:05:00

Stored procedures can only be executed against a single partition. There is nothing you can do about that.

They are not considered a query that returns a feed but a request that could return a response of any type. That's they they don't used the FeedOptions but rather the RequestOptions.

You can still execute your query as a normal document query and set the EnableCrossPartitionQuery to true. Cosmos should recognise the partition key in the query and should limit the requests to the specific partition key values.

I say should because this answer suggests that this is the case but there are some comments that say otherwise. I would suggest you check your metrics regarding the amount of collection hits.

Multiple partitions in COSMOS DB collection

1 Answers