5

When using Azure Cosmos DB and querying one partition, i just specify the partition key in the FeedOptions. But when i have to query n partitions, i have (afaik) 2 options:

  1. Run a separate Task for every partition and merge the result in my application code
  2. Set the Flag "EnableCrossPartitionQuery" (along with MaxDegreeOfParallelism) in FeedOoptions and contrain my partitions in the query.

When i have to apply sort criteria along with paging on the whole result set (across all partitions) i think the first approach will reach it's limits.

What is the recommended way to query across multiple partitions in Cosmos DB using the .NET SQL API?

Markus S.
  • 2,602
  • 13
  • 44

1 Answers1

4

The first approach is not recommended unless you know every possible partition key value that your documents have and you are ready to write some parallel request code. It's only efficient if you wanna query a few partitions but not all.

Enabling the EnableCrossPartitionQuery is the recommended approach if you want to query all the partitions but ideally, you wanna use it as less as possible.

CosmosDB knows if the partition key definition is part of the query and will limit it's results to the partitions for this query if the partition key values are provided.

This means that if you write something like select * from c where c.partitionKey = 'something' || c.partitionKey = 'somethingelse' and you enable the EnableCrossPartitionQuery options, your query will be executed only against the 2 partitions that are part of your query (something and somethingelse).

Nick Chapsas
  • 6,872
  • 1
  • 20
  • 29
  • Thank you for your feedback. I know all the partition keys. They are usually 1-5, up to a max of ~10 (representing devices per user). Does performance differ between those 2 approaches? AFAIK when using EnableCrossPartitionQuery = true, the results are somehow merged on the client side but not on the server side. Is that true? Also when using Sorting/Paging across partitions: I think the only way to do this is using the EnableCrossPartitionQuery-Way. Is that correct? – Markus S. Oct 16 '18 at 06:21
  • 2
    Does performance differ between those 2 approaches?: This comes down to how you code it. It is just not recommended because it is meant to be abstracted away from you and taken care of by the SDK. Also yes, unless you use the cross partition setting you can't have reliable sorting/paging. – Nick Chapsas Oct 16 '18 at 06:54