How can we improve Azure Cosmos DB?

← Azure Cosmos DB

[DocumentDB] Allow Paging (skip/take)

Allowing paging would be huge. Btw, Thank You for rolling out this feature at all! It looks wonderful! Can't wait to try it! However, I won't be able to use it for actual work projects until it supports paging and sorting.

3,865 votes
Vote
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
You have left! (?) (thinking…)
Fred Steffen shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
completed  ·  AdminAzure Cosmos DB Team (Product Manager, Microsoft Azure) responded  · 

Work on making this feature available in all SDK’s as well as cross-partition is now available so marking this as complete.

The team has seen your feedback regarding performance for queries with especially large OFFSET values and is currently working on optimizing these by better utilizing the index and further improving performance. These optimizations will be released shortly.

For more information on Offset/Limit please see, https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit

Thank you.

Show previous admin responses (9)

135 comments

Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Justin Hopper commented  ·   ·  Flag as inappropriate

    I would argue this implementation is even LESS usable than if I did this client-side myself, because I do not receive a continuation token when using OFFSET LIMIT. Thus, skipping to a particular page does the client no good, as subsequent pages will need to continue to use OFFSET LIMIT even if the client wants to continue forward.

  • Eric P commented  ·   ·  Flag as inappropriate

    Single partition is not the biggest issue.

    As others have mentioned Offset/Limit is a client implementation and will use as many RUs as needed to get to the page, which is what everyone was implementing anyways for paging. This doesn't scale for non-trivial applications.

    We need native implementation on Cosmos so that RU usage is minimal.

    For now we are using ContinuationToken, which has its own problems. Size can go to 16KB which breaks GET requests.

  • Jahmai Lay commented  ·   ·  Flag as inappropriate

    I can't believe this is implemented client-side with horrible RU scaling. We could already implement that ourselves.

  • Stephen commented  ·   ·  Flag as inappropriate

    Dissapointed that's it's taken so long to get this functionality and now that its finally released it's proven to be unusable on larger datasets due to the high RU usage. Is there any intention to optimize and address this in the near future or is this just the compromise of using cosmosDb that we have to live with ?

  • Benjamin Hubert commented  ·   ·  Flag as inappropriate

    I agree with the others who stated that the implementation needs work.
    The RU cost scaling for offset is too high.

  • McNamara, Jeff commented  ·   ·  Flag as inappropriate

    Agreed with Russ this implementation needs work. The fact that it does not support cross-partition queries is also a big problem.

  • Alexander Shabunevich commented  ·   ·  Flag as inappropriate

    The implementation looks completely useless.
    Seems like under the covers you are iterating till requested offset and then return next page. I have no other explanation, page N consumes N x page1 RUs.

    Page: 1; Offset: 0; Limit: 10000; Consumed RUs: 547.58; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 2; Offset: 10000; Limit: 10000; Consumed RUs: 1071.45; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 3; Offset: 20000; Limit: 10000; Consumed RUs: 1597.51; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 4; Offset: 30000; Limit: 10000; Consumed RUs: 2123.41; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 5; Offset: 40000; Limit: 10000; Consumed RUs: 2649.4; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 6; Offset: 50000; Limit: 10000; Consumed RUs: 3175.53; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 7; Offset: 60000; Limit: 10000; Consumed RUs: 3701.49; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 8; Offset: 70000; Limit: 10000; Consumed RUs: 4227.45; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 9; Offset: 80000; Limit: 10000; Consumed RUs: 4753.42; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit
    Page: 10; Offset: 90000; Limit: 10000; Consumed RUs: 5279.29; Query: SELECT r.id, r.partition FROM root r WHERE r.partition=@partition AND r.label=@label OFFSET @offset LIMIT @limit

  • Russ commented  ·   ·  Flag as inappropriate

    This is an unusable implementation.

    As the skip/limit is done in the aggregation pipeline, my experience is that your RU cost will be 1/10 of the number of items returned from your filter. For example if 10,000 items are matched on your filter it will charge you 1000 RU's from the aggregation pipeline.

    If you have a large collection and you don't know how many records are going to be matched from a search criteria then you are in for some pain.

    I am going to have to move to a MongoDB database as this is just to much of a limitation.

  • Diego Oliveira Sanchez commented  ·   ·  Flag as inappropriate

    Related to @Vince who posted right before me. I am querying using the mongoDB API. I've noticed that querying the 3,000th item consumes many more RUs than querying the first few items. Like 50 times the amount of RUs. I am doing something like model.find(<query>).skip(3000).limit(25). Am I doing something wrong, or do we have to wait for the Cosmos team to implement an efficient skipping mechanism?

  • Vince commented  ·   ·  Flag as inappropriate

    Question - is this an "efficient" implementation? To be specific, is it just as efficient in terms of latency and RUs consumed to read from offset 5 than it is to read from offset 50,000? Did you guys implement this in a way that under the covers, on the server side, you just page through results in a tight loop until you've burned through N results, and then return the next page of items? Or did you actually build an index for this so that it's efficient to immediately jump to the 50,000th item and read the next page of results?

    Looking forward to your answer, thanks.

  • Anargyros Tomaras commented  ·   ·  Flag as inappropriate

    Doesn't work correctly when passing this "SELECT * FROM t OFFSET 0 LIMIT 1" to the Cosmos SDK v2.0. Does this require a specific version of the SDK to work with sqlExpressions (not LINQ) ?

  • Oleg Gliznutsa commented  ·   ·  Flag as inappropriate

    Offset/Limit don't work correctly with EnableCrossPartitionQuery option - e.g. db returns first 15 results when requesting OFFSET 10 LIMIT 5

  • Shayan Khan commented  ·   ·  Flag as inappropriate

    It does not seem to be implemented correctly. I am getting incorrect results when sending a query from my node application

← Previous 1 3 4 5 6 7

Azure Cosmos DB: SQL API

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice