Issue related to OFFSET and LIMIT in Cosmos

When we use offset and limit, RU increases significantly. Higher in the page count you go we can see peak in RU units. Example for a page size of 100 items:

Page 1: 9.78 RU

Page 10: 37.28 RU

Page 100: 312.22 RU

Page 500: 358.68 RU

Even for simple query

SELECT from c OFFSET [pagesize] LIMIT [size]

For example in the below screenshot, we are running the query for 125000 records where the Limit(size) is 100 records at a time, we can see run reached more than 80% for a considerable amount of time.

This is because when we use offset and limit cosmos will load all the data and filter the data for the required result.

For instance, we got 1000 records spread across 10 pages then,

Page 1: SELECT * from c OFFSET [0] LIMIT [100]

Page 2: SELECT * from c OFFSET [100] LIMIT [200]

...

Page10: SELECT * from c OFFSET [900] LIMIT [1000]

The above query cosmos will load 1000 records and then filter the required result set. Because of this, it takes More RU to read the data.

The solution for the larger data set will be a Continuation token

Continuation tokens

Azure Cosmos DB for NoSQL query executions are stateless at the server side and can be resumed at any time using the continuation token

A Continuation Token is like a bookmark for a query. For example, 1000 records in the database. Our page size is 25 items so you tell CosmosDB to only return 25 of these records at a time. Along with those 25 records, CosmosDB also sends you a Continuation Token. If you provide this token with the next request the query will “pick up” where it left off and continue the query giving you the next 25 records.

For example in the below screenshot, we are running the query for 125000 records It has taken 2 min to complete to get the result set

Sample code for Continuation token

For more information about the Continuation token please refer to the below link

https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/pagination