5

In Azure Cosmos DB (SQL API) the following query charges 9356.66 RU's:

SELECT * FROM Core c WHERE c.id = @id -- @id is a GUID

In contrast the following more complex query charges only 6.84 RU's:

SELECT TOP 10 * FROM Core c WHERE c.type = "Agent"

The documents in both examples are pretty small having a handful of attributes. Also the document collection does not use any custom indexing policy. The collection contains 105685 documents.

To me this sounds as if there is no properly working index on the "id" field in place.

How is this possible and how can this be fixed?

Updates:

  • Without the TOP keyword the second query charges 3516.35 RU's and returns 100000 records.
  • The partition key is "/partition" and its values are 0 or 1 (evenly distributed).
driAn
  • 3,245
  • 4
  • 41
  • 57
  • `id` by default has a Hash index. It doesn't make sense to compare the two queries though as one of them is limiting to the `TOP 10`. Can you remove it and try without the `TOP` keyword and what what the charge is? Keep in mind RU/s aren't just query complexity but it also involves the size of the resultsset. – Nick Chapsas Jul 13 '18 at 10:44
  • Also give more info on whether the collection is partitioned, what is the partition key etc. – Nick Chapsas Jul 13 '18 at 10:48
  • @Nick, thanks - I updated the question. Let me know if you need more info. – driAn Jul 13 '18 at 12:22

2 Answers2

1

If you have partition collection you need to specify partition keyif you want to do request most efficiently. Cross-partition queries is really expensive (and slower) in cosmos, because partitions data can be stored in different places.

Try following:

SELECT * FROM Core c WHERE c.id = @id AND c.partition = @partition

Or, specify partition key in feed options if you're using CosmosDB SDK.

Let me know, if this helps.

Olha Shumeliuk
  • 720
  • 7
  • 14
  • Thangs Olga. When specifying the partition the first query now charges only 2.31 RUs. I only have two partitions (each ~50MiB) and therefore omitting the partition key should not cause such a high query charge as 9356.66, rather a request charge of 2*2.31 would be somewhat acceptable. Furthermore, for me it is not an option to include partition key into the query because that would mean the application has to mantain partition Information for every single foreign key which is very hard to maintain... – driAn Jul 16 '18 at 06:16
1

I assume the solution is the same as posted here: Azure DocumentDB Query by Id is very slow

I will close my own question once I am able to verify this with Microsoft Support.

driAn
  • 3,245
  • 4
  • 41
  • 57