Cosmos DB (DocumentDB API): efficient way to query the last document by section id?

Question

I have a Cosmos DB collection with numerous sections based on device ID. I often have use cases that require getting the most recent document using a specific device identifier. I am currently using the SELECT TOP 1 functions available in the DocumentDB API, as shown below, to do the following:

 SELECT TOP 1 * FROM c WHERE c.deviceId = 5 ORDER BY c.timeStamp DESC 

This approach leads to an increase in RU / s consumption and a decrease in productivity as the size of the collection and individual sections grows, as one would expect. As a temporary way to fix this problem, I added additional suggestions that limit the scope of the request by timestamp:

 SELECT TOP 1 * FROM c WHERE c.deviceId = 5 AND c.timeStamp >= 1506608558 --timestamps are unix/epoch based to optimize indexing AND c.timeStamp <= 1506694958 ORDER BY c.timeStamp DESC 

I would like to know if there is a better way to select the last document by section ID, since adding this where clause may lead to unexpected or missing results.

Answer 1

I had a similar scenario in which the identifier of the object that I am tracking forms my section key, and inside this section there are 2880 events per day for each asset and will grow over time.

If for other use cases a complete history of events was necessary, for this particular use case it was necessary to retrieve the last event. Thus, an alternative collection was created that uses the same section key but contains the CURRENT state, i.e. the last event for this asset.

When an event is recorded to the WRITE side, being a collection that stores all events for the asset, the trigger updates the READ side with the most recent value.

Although this may seem like a redoubled write effort, in our use case it is an increase in read-side performance.

I found this MS article helpful Working with change feed support in Azure Cosmos DB