CosmosDb ordering by datetime - high RU

Kai Gorman 1 Reputation point
2020-06-30T01:26:41.713+00:00

Is there a reason why ordering by a datetime property takes a much higher amount of RU than other properties? However it appears the penalty is only paid on the first page of data. e.g. in one of our collections I get these results:

SELECT * FROM c where c.WorkspaceId = 'bayside' and c._type = 'Asset' order by c.CreatedAt

first page: 94.53 RUs (100 items returned, 200 items read)
next page: 11.83 RUs (100 items returned, 100 items read)

SELECT * FROM c where c.WorkspaceId = 'bayside' and c._type = 'Asset' order by c.NameLowerCase

first page: 23.48 RUs (100 items returned, 200 items read)
next page: 11.62 RUs (100 items returned, 100 items read)

SELECT * FROM c where c.WorkspaceId = 'bayside' and c._type = 'Asset' order by c.id

first page: 33.44 RUs (100 items returned, 200 items read)
next page: 14.6 RUs (100 items returned, 100 items read)

The datetimes are stored as ISO 8601.

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,535 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Mike Ubezzi 2,776 Reputation points
    2020-07-01T00:52:43.357+00:00

    Hi @Kai Gorman - Have you made any index changes? If not, I think you might benefit if there are specific queries you run on a continual basis that you would like to optimize, leveraging composite indexes specific to these queries can bring incremental gains. There are some indexing nuances between the available Cosmos APIs, such as the default indexing between MongoDB API version 3.2 and version 3.6. This information would be good to know. If you need help with this, can you post a copy of your current indexing configuration. Also, where are these queries being executed from? From the Query Explorer in the portal or from a client leveraging an SDK?