CODEWITHSUNDEEP
azureazure-cosmosdb
Rob Reagan
Rob Reagan

Reputation: 7686

Cosmos DB High Query Charge

I am using Cosmos DB in Autopilot mode with a max RU/s set at the Collection for 20K RU/s. I'm using the SQL API. I have several hundred thousand records in this particular partition. I have the default indexing policy, which is that everything is indexed. I have gone into the Data Explorer within the Azure Portal and have run the following query:

SELECT count(1) FROM c where c.partitionKey = "12140" and c.uniqueId = "20cdb686-6959-4ed6-ac31-c7f414238f68" and c.timestampAsString = null

The Query Stats show an RU/s charge of 3.46. This is great!

I then run a very similar query, but I am testing c.timestampAsString for being not null:

SELECT count(1) FROM c where c.partitionKey = "12140" and c.uniqueId = "20cdb686-6959-4ed6-ac31-c7f414238f68" and c.timestampAsString != null

Total RU/s charges for the above query are 2989.73 RU/s. This is quite a bit higher. I was expecting something along the lines of the original query.

Can anyone explain why these query charges are so very different?

Upvotes: 0

Views: 312

Answers (1)

Mark Brown
Mark Brown

Reputation: 8763

The reason for this huge discrepancy in RU is how/whether we push filters down to the index. For queries like c.timestampAsString = null we do push this down to the index. For queries where you do aggregates and filters like c.timestampAsString != null we do not.

You can do some things however to optimize this query. If you create a second property called, "istimestampAsStringNull" and set to true to false this would give you the ability to do the query below and you should get roughly the same RU/s cost.

SELECT count(1) FROM c where c.partitionKey = "12140" and c.uniqueId = "20cdb686-6959-4ed6-ac31-c7f414238f68" and c. isTimestampAsStringNull == false

We are looking at this for a future optimization but for now this workaround above should work.

Hope this is helpful.

Upvotes: 1

Related Questions