CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
Sasha Ramjee
Sasha Ramjee

Reputation: 53

cosmos db sql api datetimepart function increasing rus

my cosmos db has data stored per minute time intervals, I would like to query it using different intervals, depending on start date and end date. But when using the DateTimePart function, the RUs are drastically increasing. Without the datetimepart function, for a months data is 184, if I add the datetimepart function it jumps to 6000.

I tried the query like this:

SELECT c.config,c.datetime
FROM c where c.config='DBM'  and c.datetime >='2021-04-21T07:02:16' 
and c.datetime <='2021-05-21T07:02:16' and (DateTimePart('minute' , c.body.metadata.datetime) % 60) = 0

as well as using a subquery join, but still no luck with bringing down the rus.

Does anyone have any other idea I could use to query the data with an interval, so basically if I query data for a month, but choose an hourly interval, i should get data for every hour for that month

Sasha

Upvotes: 1

Views: 547

Answers (1)

Noah Stahl
Noah Stahl

Reputation: 7563

For common and expensive queries, it can make sense to incorporate additional derived fields in your model to optimize the query cost. For example, if you are always querying for items on the hour mark, you could write those records with an additional property minuteOfHour: 0 and then use the more straightforward query:

SELECT c.config,c.datetime
FROM c where c.config='DBM' and c.minuteOfHour = 0 and c.datetime >='2021-04-21T07:02:16' 
and c.datetime <='2021-05-21T07:02:16'

In other words, the strategy is to precompute things once rather than forcing the query engine to perform the work every query for every item.

It may also help to add one or more composite indexes to reduce the query cost.

Of course, always test different approaches to see what yields the least cost.

Upvotes: 1

Related Questions