What is the most efficient way to get count of records in Cosmos query?

78 Views Asked by At

I am writing a SQL query in Cosmos where I noticed that getting COUNT is very expensive. This is most likely the way I have written my query.

i.e., my criteria are, get count of all ids where item (array) is not empty and it either contains vendor name Amazon or Ebay and has updatedDateTime or createdDateTime prior to some date.

SELECT COUNT(c.id) AS totalCount 
FROM c 
WHERE ARRAY_LENGTH(c.items) > 0 
AND EXISTS (SELECT VALUE l FROM l IN c.items WHERE (CONTAINS (l.vendor, 'Amazon', true) OR CONTAINS (l.vendor, 'Ebay', true) ) 
AND ((IS_DEFINED(l.updatedDateTime) AND l.updatedDateTime >= '2023-09-01') OR c.createdDateTime >= '2023-09-01'))

This gives me the desired result, but I see 2237.38 RUs which I want to reduce as much as possible.

How to make this more efficient?

0

There are 0 best solutions below