Mongo gives me "sort exceeded memory limit" exception even when using pagination

user_13265173 0
Viewed 404

I am trying to create a paginated search for my project using MongoDB. Even using Limit and Skip, it throws a "Sort Exceeded Memory Limit" exception. Thats a little bit weird because I am limiting my query to only 10 itens.

 public async Task<(List<Contato>, long)> BuscarPorFiltro(FiltroContatoDTO filtro, int page, int pageSize)
    {
        var collection = _mongoDBContext.MongoDBConexao.GetCollection<Contato>(_collection);

        var idsFluxoCompleto = await fluxoCompletoRepository.BuscarFluxoCompletoPorEmpresaId(Guid.Parse(filtro.EmpresaId));

        var sortBuilder = Builders<Contato>.Sort;
        var sort = sortBuilder.Descending(c => c.DataHora);

        var options = new FindOptions<Contato>();
        options.Skip = page * pageSize;
        options.Limit = pageSize;
        options.Sort = sort;

        var filterBuilder = Builders<Contato>.Filter;
        var filter = idsFluxoCompleto != null && idsFluxoCompleto.Count > 0 ? filterBuilder.Where(c => idsFluxoCompleto.Contains(c.IdFluxoCompleto)) : filterBuilder.Empty;

        //Filtros Opcionais
        if (filtro.DataInicio.HasValue)
            filter = filter & filterBuilder.Gte(x => x.DataHora, filtro.DataInicio.Value);
        
        if (filtro.DataFim.HasValue)
            filter = filter & filterBuilder.Lt(x => x.DataHora, filtro.DataFim.Value);

        if (filtro.FluxoId.HasValue)
            filter = filter & filterBuilder.Eq(x => x.IdFluxoCompleto, filtro.FluxoId.Value);

        if (!String.IsNullOrWhiteSpace(filtro.Telefone))
            filter = filter & filterBuilder.Where(x => x.Telefone.Contains(filtro.Telefone));

        if (filtro.CompletouFluxo.HasValue)
            filter = filter & filterBuilder.Eq(x => x.Status, filtro.CompletouFluxo.Value);

        if (filtro.CancelouFluxo.HasValue)
            filter = filter & filterBuilder.Eq(x => x.CancelouFluxo, filtro.CancelouFluxo.Value);

        if (filtro.IntegrouCRM.HasValue)
            filter = filter & filterBuilder.Eq(x => x.RealizouIntegracaoCRM, filtro.IntegrouCRM.Value);

        if (filtro.Ultrapassou24Horas.HasValue)
            filter = filter & filterBuilder.Eq(x => x.Ultrapassou24Horas, filtro.Ultrapassou24Horas.Value);

        if (!String.IsNullOrWhiteSpace(filtro.Temperatura))
            filter = filter & filterBuilder.Eq(x => x.Lead, filtro.Temperatura.ToUpper());

        if (filtro.Origem.HasValue)
            filter = filter & filterBuilder.Eq(x => x.Origem, filtro.Origem.Value);

        if (filtro.Unidade.HasValue)
            filter = filter & filterBuilder.Eq(x => x.Unidade, filtro.Unidade.Value);

        var count = await collection.CountDocumentsAsync(filter);
        var result = await collection.FindAsync(filter, options);

        return (result.ToList(), count);
    }

I am aware of the AllowDiskUse option, but I dont wanna use it, because this specific query should return only 10 itens and it should not exceed the 100Mb limit for the returned documents.

mongodb-query mongodb-.net-driver mongodb .net c#
1 Answers

Though you limit your result to 10 documents but DB has to filter all the match document and sort based on your sort criteria and if entire matched data set does not fit in memory limit DB throws the error.

Consider scenario where you have 10,000 documents and you write filter which reduces matched documents to 5000 then you have some sort/skip criteria then DB has to sort and skip documents on the matched 5000 documents and if that 5000 docs won't fit in memory it will throw error.

There is no harm is using Allow disk use flag to true it will be used only if your matched dataset size wont fit in limit.

Documentation: https://docs.mongodb.com/manual/reference/command/aggregate/#command-fields

user_64625380
Related