We are using Azure cosmos DB and we are accessing it from Java using the mongo API
Some simple queries that usually use about 4 RUs sometime take about 1500 RU's and we get the message "rate is too large"
If we do the same query from the azure portal UI we always get the lower RU's consumption
Example Document:
{
"_id": {
"$oid": "5c40a6e3f6fe4d1fec5f092e"
},
"attempts": 0,
"status": {
"confirmed": false,
"nSent": true
}
...
rest of non relevant fields
}
if i translate the query to SQL is will look something like this:
SELECT * FROM c WHERE c.status.confirmed = true and (c.status.nSent=null or c.status.nSent=false) and (c.attempts=null or c.attempts<10)
2 important notes:
- we do not use the shard key in this query. I don't know if it is relevant here or not
- note that we check that c.status.nSent does not exists (=null) or that it is equal to false. If we remove the part that check that c.status.nSent is null then the query goes fine. This is the strange part...
Example of the query as azure sees it in the their log:
(((r1["p1"]["p2"] = true) AND (((r1["p1"]["p3"] ?? null) = null) OR (r1["p1"]["p3"] = false))) AND ((((r1["p4"] ?? null) = null) OR (r1["p4"] <= 10)) AND (r1["p5"] >= 67)))","parameters":[]}"}}
I have also tried to replace this part of the query
(c.status.nSent=null or c.status.nSent=false)
with
(c.status.nSent!=true)
hoping that it will also apply for document that do not have this property but the results are the same
Our average RU/s usage is 40 RU/s and our limit is 4000 RU/s so there should be a lot of spare RU/s for this query
On the exception we get we see this:
RequestCharge: 0.38
