Overflow sort stage buffered data usage when trying to skip and take from MongoDB repository

Question

I have a classic REST and ODATA enabled Web API controller calling MongoDB based implementation of a repository pattern.

I keep on getting

Overflow sort stage buffered data usage of 33556193 bytes exceeds internal limit of 33554432 byte Exception

when i try to skip first 12010+ records and get top 10

?$skip=12020&$top=10&$orderby=Serial

After some search I tried to implement an index on Serial like

private void GetCollection() //is like DBSet of some entity
{
  _collection = _dbContext.Database
     .GetCollection<TEntity>(typeof(TEntity).Name);
  Type typeParameterType = typeof(TEntity);
  if (typeParameterType.Name == "StoreCommand")
    if (_collection.IndexExists("Serial") == false)
      _collection.CreateIndex(IndexKeys<StoreCommand>.Ascending(_ => _.Serial));
}

My repository implementation is like this.

    public class MongoDbRepository<TEntity> :
    IRepository<TEntity> where
        TEntity : EntityBase
{
    private MongoCollection<TEntity> _collection;

    private SNDbContext _dbContext;
    public MongoDbRepository(SNDbContext dbContext)
    {
        _dbContext = dbContext;
        GetCollection();
    }
    private void GetCollection()
    {
        _collection = _dbContext.Database
            .GetCollection<TEntity>(typeof(TEntity).Name);
    }
    public IQueryable<TEntity> GetAll()
    {
        return _collection.AsQueryable(); 
    }//............And other functions after this

}

call from service layer is like this

 IQueryable<StoreCommand> GetAllStoreCommands() 
  {
     return _uow.StoreCommands.GetAll(); 
   }

where SNDbContext has all the code related to getting me the Database using MongoClient and connection string.

JotaBe JotaBe · Accepted Answer · 2015-05-22T08:48:16

The problem is that in your repository implementation (not shown) you're getting all the data from MongoDB, and then all this data is being sorted in buffered and sorted in memory to allow the skip and take.

What you have to do is modify your repository in any of this two ways:

return a pure queryable, so that you can compose the rest of the query and still get it executed on the MongoDB engine
expose directly a method that receives parameters to skip and take, and make a query that executes them directly in the database.

You can implement it with a cursor, like this:

var collection = db.GetCollection<YourType>("yourtype");
var sort = SortBy<YourType>
            .Ascending(p => p.YourProperty1)
            .Descending(p => p.YourProperty2);
MongoCursor<YourType> cursor = collection
  .FindAll()
  .SetSortOrder(sort)
  .SetSkip(12010)
  .SetLimit(10);

If you iterate this cursor you should not get any problem at all.

You can also define and execute a SelectQuery, specifying the Skip and Take values. See the docs: SelectQuery Properties

Overflow sort stage buffered data usage when trying to skip and take from MongoDB repository

1 Answers