CODEWITHSUNDEEP
c#asp.net-corepaginationazure-cosmosdb
MiSta
MiSta

Reputation: 33

Is there a recommendation for how to use pagination in cosmosdb with .NET SDK v3

I am currently implementing CosmosDB .NET SDK v3. For pagination I am using OFFSET LIMIT SQL-API functionality. This works well with small datasets. For Example

SELECT * FROM c ORDER BY c._ts OFFSET 3 LIMIT 20

But following query with larger offset leads to immense performance issues (5 mins. execution time):

SELECT * FROM c ORDER BY c._ts OFFSET 5000 LIMIT 500

Are there special options which need to be turned on in the database (index etc.) or are there any special request options in the SDK?

Thanks Michael

Upvotes: 2

Views: 717

Answers (1)

Nick Chapsas
Nick Chapsas

Reputation: 7190

This is to be expected. You are executing a cross partition query across thousands of documents that don't live together. Cosmos is trying to give you the results you want but performing a cross partition query with ordering and pagination just isn't something that Cosmos DB is meant to be fast or efficient on in the first place.

Limiting this query into a single partition will actually improve performance significantly since Cosmos will know that all the data lives in a single physical partition. Cross partition queries as part of your regular application's workflow are a big no no and should not be used. You should only be looking up documents by id+partition key or querying using the partition key in the query or the partition header.

Upvotes: 2

Related Questions