Error when searching large amount of data in Entity framework,

Asked

Viewed 88 times

1

I am putting together a report, and in it there is the option to consult by date period. When I consult with the interval of 30 or 60 days, I can obtain the data, where I group and return.

However, when increasing the query period, the same method returns the error of: 500 (Internal Server Error). Searching, I noticed that in some cases the Entity framework, terminates the query because of the time out or the amount of data to be processed.

Obs: This same query that I made by Entity, I performed directly in sql server, passing the parameters, to check if really return data(and returned as expected).

public IQueryable ObterNotaSimplificada(DateTime compInicial, DateTime compFinal,
        PorteContribuinte? codigoPorte, string query, int? itemServico, bool? simplesNacional)
    {
        var res = this.Servicos
            .Include(x => x.Prestador)
            .Where(a => (a.Competencia >= compInicial && a.Competencia <= compFinal) &&
                        (codigoPorte == null || a.Prestador.Porte.Value == codigoPorte) &&
                        (simplesNacional == null || a.Prestador.IsSimplesNacional == simplesNacional) &&
                        (itemServico == null || a.ItemListaServico.Id == itemServico) &&
                        ((query == null || a.Prestador.RazaoSocial.Contains(query))
                         || (a.Prestador.CpfCnpj.Contains(query))))
            .Where(a => a.DataCancelamento == null)
            .GroupBy(a => a.Prestador.CpfCnpj)
            .Select(g => new
            {
                CNPJ = g.Select(x => x.Prestador.CpfCnpj),
                Competencia = g.Select(x => x.Competencia),
                RazaoSocial = g.Select(x => x.Prestador.RazaoSocial),
                SimplesNacional = g.Select(x => x.Prestador.IsSimplesNacional),
                Porte = g.Select(x => x.Prestador.Porte),
                DescricaoPorte = g.Select(x => x.Prestador.Porte.ToString()),
                QtdNotasEmitidas = g.Count(),
                BaseDeCalculo = g.Select(x => x.BaseCalculo),
                ValorDeducoes = g.Select(x => x.ValorDeducoes),
                ValorServicos = g.Select(x => x.ValorServicos),
                ValorIss = g.Select(x => x.ValorInss),
                TotalBaseDeCalculo = g.Sum(x => x.BaseCalculo),
                TotalValorDeducoes = g.Sum(x => x.ValorDeducoes),
                TotalValorServicos = g.Sum(x => x.ValorServicos),
                TotalValorIss = g.Sum(x => x.ValorIss),
                IssRetido = g.Select(x => x.IssRetido > decimal.Zero),
                ItemDeServico = g.Select(x => x.ItemListaServico.Descricao),
                CodItemServico = g.Select(x => x.ItemListaServico.Codigo)
            });
        return  res;
    }
c# entity-framework performance

  • has to see what it is ItemListaServico for example... if you have Lazy inside each record it will run another select to fetch this object... take out Count as well, if you are counting a collection inside each object...

1 answer

0


It turns out that you are returning a lot of data, using grouping functions (Group By, Sum) and this requires a lot of Entity framework. When working with a lot of data it is recommended to use the SQL query itself, because performance is better.

Try using this example, but adapting to your query:

var results = context.Database.SqlQuery<string>("SELECT COLUNAS FROM NOMETABELA").ToArray();

If performance remains poor, I recommend creating indexes for your database.

Browser other questions tagged c# entity-framework performance

You are not signed in. Login or sign up in order to post.