Azure-cosmos-dotnet-v3: ReadNextAsync with ORDER BY query loads all items at once

[KristianJakubik]

Describe the bug
FeedIterator.ReadNextAsync() reads all data for queries with ORDER BY.

NOTE:
All queries in our application are single partition queries (Partition key is specified).

To Reproduce
Here is small repro.
https://gist.github.com/KristianJakubik/8d90c3b97e385c461df012acbc14a2f8

Expected behavior
I expected, that ReadNextAsync() would return small portion of data. The same portion as it does when I call query without ORDER BY.
For query which should return in total 1000 items, first call to ReadNextAsync() returns only 100 items.

Actual behavior
For query which should return in total 1000 items, first call to ReadNextAsync() returns all 1000 items.

Environment summary
SDK Version: 3.6.0, 3.7.0 preview 2
OS Version: Windows

Additional context
This is pretty serious bug for us. It directly impact our customers. Order by queries are not bearable for our Cosmos Db anymore, they drain so much RUs and take so much time to execute, when one ReadNextAsync load whole query at once and does not divide it in smaller chunks
(continuation token is useless) as it used to be in v2 sdk.
We have designed our application, that our client works with continuation token from cosmos sdk and therefore client decides when it wants to load additional data (lazy approach). This way the load on our database was easily maintainable, with new v3 sdk this is not possible.

[j82w]

@KristianJakubik can you set the MaxItemCount?

[KristianJakubik]

@j82w
This is not feasible solution for us. The client already sets how much data is able to process by setting MaxItemCount property. So this property is in use already 😄. For some cases client can process much more data, than current Cosmos DB instance is scaled for. When this occurs, client uses continuation token to load another batch after processing current one. This worked like a charm until ORDER BY queries came into play. And now we can't process data by batches, however we have to wait when whole query is executed, which leads to 429s and query which takes 40sec to execute 😄. That's why we relied on this behavior.
We have different cosmos instances and one instance can be scaled to 1 000RUs another can be up to 40 000RUs. If we specified one general constant for all of them, one cosmos db can be overheated and others won't be fully utilized up to their defined scale.

I have tried to make it as abstracted as possible. I hope it helped a little bit to understand more of our use case and that fix of this bug is really important for us.

@KristianJakubik can you set the MaxItemCount?

We have already released hot-fix with specified universal upper bound for MaxItemCount value, but this solution is really not scalable for our application and we hope it is temporarily, otherwise we can't use v3 sdk.

@j82w
Please can you let me know, if this bug has any priority and if you have any plan solving this in near future?

[j82w]

If you don't set a MaxItemCount then you are relying on undefined behavior. The service is going to change and optimize the payload based on user feedback and performance testing results. Based on some quick testing the service is returning 1000 items in a single page request, and is not draining the entire query. On your original post you only want the service to return a 100 items per a ReadNextAsync then you need to set QueryRequestOptions.MaxItemCount to 100. Just to clarify MaxItemCount is not a overall query max it is the max per a page from Cosmos DB. If you want a query to drain more for some containers than other then you need to change the value. It's not possible for the service or the SDK to determine how much load your configuration can handle.

[KristianJakubik]

@j82w
It seems to me that we don't understand each others reasoning. Anyway I need some time to create the more descriptive repro, should be ready by the end of this week. Could you please keep this issue open until that time.

[j82w]

@KristianJakubik that would be great if you have a more descriptive repo. Sorry I'm not understanding the problem.

[KristianJakubik]

It's not possible for the service or the SDK to determine how much load your configuration can handle.

Yes, I fully agree. Neither client nor service nor SDK can decide what is the optimal limit. Although in my opinion It should be decided by cosmos db instance, what is its upper limit, how much data it can deliver regarding its RUs configuration.

I've updated the gist with set MaxItemCount property and I've used Fiddler as http proxy.

| SQL | # items returned by ReadNextAsync | MaxItemCount | total # items in container | # http requests in one ReadNextAsync | # items return in one http call | total RU cost |
| --- | --- | --- | --- | --- | --- | --- |
| without order by | 207 | 1200 | 1500 | 1 | 207 | 140.75 |
| order by | 1200 | 1200 | 1500 | 6 | 206 | 854.31 |
| without order by | 100 | null | 1500 | 1 | 207 | 69.46 |
| order by | 1000 | null| 1500 | 5 | 206 | 712.32 |
| without order by | 207 | -1 | 1500 | 1 | 207 | 140.75 |
| order by | 1500 | -1 | 1500 | 8 | 206 | 1038.03 |

I did three measurements against cosmos instance scaled up to 400 RUs, 2 000 RUs, 10 000 RUs. Surprisingly the result for all three of them was the same and it seems, that the response from each http request is maximum of 4.2MB. My previous assumption was that this size would vary based on container's scale.

Expected behavior:
I would expect, that ORDER BY queries would return from ReadNextAsync() the same number of items as does for quieries without ORDER BY clause. And one call to ReadNextAsync() would represent one call to cosmos instance, the same way it does in non ORDER BY queries.

@j82w
I hope we are now more on the same page 😃. If you have any more questions, want more detailed explanation, more detailed data from measurements or anything, please feel free to ask.

[j82w]

@bchong95 can you take a look?

[khdang]

@KristianJakubik thanks for the information. I have a question for you. Can you elaborate a bit more what are your concerns about setting the MaxItemCount to 100? It seems like a suitable approach for me, because setting the MaxItemCount is the way we indicate to SDK the upper bound of the data it should target to retrieve. Not setting this value essentially means letting the SDK decide this on its own. Different queries are executed with different default behavior. In fact, as agreed, the SDK or the service doesn't know what is the behavior that you expect, which vary by scenarios. Some other users may prefer the SDK to return more results in one page. Therefore, it seems reasonable to set MaxItemCount to achieve a specific behavior.

[vit-svoboda]

So if I set MaxItemCount to that 100, is it guaranteed to fetch all the 100 documents in a single request? That doesn't seem feasible. And if it actually really weren't, is there a way to stop the query after the first request? How low MaxItemCount would I have to set, other than dropping the order by clause?

[KristianJakubik]

@khdang thank you for helping me to figure out the issue and for joining to this conversation.

I'm not exactly concerned about the number of retrieved items, I'm definitely more concerned about the amount of underlying http requests from the sdk to the cosmos instance.

You can see in second row of my table, that when I specified MaxItemCount to 1200 the sdk did 6 http requests for retrieving data and returns exactly 1200 items. And yes, on our production enviroment this behavior usually leads to 429 responses, unnecessarily long running queries and RU's starvation.

MaxItemCount is the way we indicate to SDK the upper bound of the data it should target to retrieve.

I don't think for queries with ORDER BY this is true. In my experience the sdk handles MaxItemCount property as exact number of items it should retrieve, definitely not UPPER BOUND.

@khdang

• If MaxItemCount was handled as UPPER BOUND restriction, why would the sdk load additional data with more http requests?
  
  
  
  • Why wouldn't be the count of items returned by ReadNextAsyncsufficient, when they absolutely fulfill your condition: _number of items <= MaxItemCount_?
  
  
• Why there are so many useless http requests then?
  _(please look at the first and second row of the table)_