Query using Entity Framework is Very SLOW

c# entity-framework entity-framework-6 linq performance

Question

I´m handling with a project from another company who developed a method to retrieve informations from database using Entity Framework 6, in order to populate a grid component.

We are facing a huge performance problem with this approach, even when we get just a few rows, like 70, 100, etc. I just don´t know what to do here.

I will be grateful if you can show me the correct way.

Some informations: Visual Studio 2013 .NET 4.5 SQL Server 2017

This the method:

[HttpPost]
        public object RetornaRelatorioEnvioSms(RetornaRelatorioEnvioSmsRequestModel model)
        {
            try
            {
                using (ctx = new SebraeContainer())
                {
                    ctx.Configuration.LazyLoadingEnabled = false;
                    ctx.Configuration.ProxyCreationEnabled = false;
                    ctx.Configuration.AutoDetectChangesEnabled = false;

                    decimal cpfDecimal = string.IsNullOrWhiteSpace(model.CPF) ? decimal.Zero : decimal.Parse(model.CPF.Replace(".", string.Empty).Replace("-", string.Empty));
                    decimal cnpjDecimal = string.IsNullOrWhiteSpace(model.CNPJ) ? decimal.Zero : decimal.Parse(model.CNPJ.Replace(".", string.Empty).Replace("-", string.Empty));


                    string query = "select d.id_diagnostico idDiagnostico " +
                                   "     , ppf.nm_razao_social nomeCliente " +
                                   "     , sx.nm_descricao sexo " +
                                   "     , CONVERT(VARCHAR, d.dt_fim, 103) dataAtendimento " +
                                   "     , r.nm_regiao regional " +
                                   "     , cem.nm_numero email " +
                                   "     , cel.nm_numero celular " +
                                   "     , p.nm_nome porte " +
                                   "     , u.nm_completo agente " +
                                   "  from tb_diagnostico d  " +
                                   "  left join tb_parceiro ppf on ppf.id_parceiro = d.id_parceiropf " +
                                   "  left join tb_parceiro ppj  on ppj.id_parceiro = d.id_parceiropj " +
                                   "  left join tb_pessoa_fisica pf on pf.id_parceiro = ppf.id_parceiro " +
                                   "  left join tb_pessoa_juridica pj  on pj.id_parceiro = ppj.id_parceiro " +
                                   "  left join tb_sexo sx  on pf.id_sexo = sx.id_sexo " +
                                   "  left join tb_acao_regiao acr on d.id_acao = acr.id_acao " +
                                   "  left join tb_regiao r  on acr.id_regiao = r.id_regiao " +
                                   "  left join tb_comunicacao cem on ppf.id_parceiro = cem.id_parceiro " +
                                   "  left join tb_comunicacao cel on ppf.id_parceiro = cel.id_parceiro " + 
                                   "  left join tb_porte p  on pj.id_porte = p.id_porte " +
                                   "  left join tb_usuario u  on d.id_usuario = u.id_usuario " +
                                   " where " +
                                   "    exists (select 1 " +
                                   "                 from tb_historico_envio_sms_sse " +
                                   "                where id_diagnostico = d.id_diagnostico " +
                                   "                  and id_tipo_mensagem_envio = 4)" +
                                   "   and d.dt_fim is not null " +
                                   "   and d.id_sistema = " + DatabaseIDs.IdSistemaSSE + " " +
                                   "   and cem.id_tipo_comunicacao = " + DatabaseIDs.IdTipoComunicacaoEmail + " " +
                                   "   and cel.id_tipo_comunicacao = " + DatabaseIDs.IdTipoComunicacaoTelefoneCelular + " ";

                    if (model.DataInicio != DateTime.MinValue)
                        query = query + "   and d.dt_fim >= CONVERT(DATETIME, '" + model.DataInicio.ToString("yyyy-MM-ddT00:00:00", new CultureInfo("pt-BR")) + "', 126)";

                    if (model.DataFinal != DateTime.MinValue)
                        query = query + "   and d.dt_fim <= CONVERT(DATETIME, '" + model.DataFinal.ToString("yyyy-MM-ddT23:59:59", new CultureInfo("pt-BR")) + "', 126)";

                    if (!string.IsNullOrWhiteSpace(model.Nome))
                        query = query + "   and ppf.nm_razao_social like '%" + model.Nome.Replace("'", string.Empty) + "%' ";

                    if (!string.IsNullOrWhiteSpace(model.CPF))
                        query = query + "   and ppf.nu_CGCCPF = " + cpfDecimal + " ";

                    if (model.Sexo.HasValue)
                        query = query + "   and pf.id_sexo = " + model.Sexo.Value + " ";

                    if (!string.IsNullOrWhiteSpace(model.RazaoSocial))
                        query = query + "   and ppj.nm_razao_social like '%" + model.RazaoSocial.Replace("'", string.Empty) + "%' ";

                    if (!string.IsNullOrWhiteSpace(model.CNPJ))
                        query = query + "   and ppj.nu_CGCCPF = " + cnpjDecimal + " ";

                    if (model.Porte != null && model.Porte.Any())
                        query = query + "   and pj.id_porte in (" + String.Join(",", model.Porte.Select(idporte => idporte.ToString())) + ") ";

                    if (model.Perfil != null && model.Perfil.Any())
                        query = query + "   and d.id_perfil in (" + String.Join(",", model.Perfil.Select(idperfil => idperfil.ToString())) + ") ";

                    if (model.Regional != null && model.Regional.Any())
                        query = query + "   and r.id_regiao in (" + String.Join(",", model.Regional.Select(idregiao => idregiao.ToString())) + ") ";

                    if (model.CNAE != null && model.CNAE.Any())
                        query = query + "   and exists (select 1 " +
                                        "                 from (select DISTINCT " +
                                        "                              REPLICATE('0', 2 - LEN(cd_class)) + CONVERT(VARCHAR, cd_class) " +
                                        "                            + REPLICATE('0', 5 - LEN(cd_atividade)) + cd_atividade " +
                                        "                            + REPLICATE('0', 2 - LEN(id_cnaefiscal)) + id_cnaefiscal cnae " +
                                        "                         from tb_diagnosticoCNAE " +
                                        "                        where id_diagnostico = d.id_diagnostico) dc " +
                                        "                where dc.cnae in ('" + String.Join("', '", model.CNAE.Select(cnae => cnae)) + "'))";

                    if (model.Setor != null && model.Setor.Any())
                        query = query + "   and exists (select 1 " +
                                        "                 from tb_diagnosticoCNAE dc " +
                                        "                 join tb_atividade_economica atv on dc.cd_atividade = atv.ds_codAtivEcon and dc.cd_class = atv.cd_class " +
                                        "                where dc.id_diagnostico = d.id_diagnostico " +
                                        "                  and atv.id_setor_economico in (" + String.Join(",", model.Setor.Select(idsetor => idsetor.ToString())) + "))";

                    if (model.Tematica != null && model.Tematica.Any())
                        query = query + "   and exists (select 1 " +
                                        "                 from tb_diagnosticoperfiltema dpt " +
                                        "                 join tb_perfiltema pt on dpt.id_perfiltema = pt.id_perfiltema " +
                                        "                 join tb_tema t on pt.id_tema = t.id_tema " +
                                        "                where dpt.id_diagnostico = d.id_diagnostico " +
                                        "                  and t.id_tema in (" + String.Join(",", model.Tematica.Select(idtema => idtema.ToString())) + "))";

                    if (model.Nivel != null && model.Nivel.Any())
                        query = query + "   and exists (select 1 " +
                                        "                 from tb_diagnosticoperfiltema " +
                                        "                where id_diagnostico = d.id_diagnostico " +
                                        "                  and id_nivel in (" + String.Join(",", model.Nivel.Select(idnivel => idnivel.ToString())) + "))";

                    if (model.Agente != null && model.Agente.Any())
                        query = query + "   and exists (select 1 " +
                                        "                 from tb_usuario_sse " +
                                        "               where id_usuario = d.id_usuario " +
                                        "                 and id_agente in (" + String.Join(",", model.Agente.Select(idagente => idagente.ToString())) + "))";

                    ctx.Database.CommandTimeout = 60;
                    IEnumerable<RetornaRelatorioEnvioSmsModel> ret = ctx.Database.SqlQuery<RetornaRelatorioEnvioSmsModel>(query).ToList();

                    if (ret.Any())
                    {
                        foreach (RetornaRelatorioEnvioSmsModel item in ret)
                        {
                            item.formasContato = (from d in ctx.tb_diagnostico.AsNoTracking()
                                                  where d.id_diagnostico == item.idDiagnostico
                                                  from fc in d.tb_parceiro.tb_tipo_forma_contato
                                                  select new RetornaRelatorioEnvioSmsFormaContatoModel
                                                  {
                                                      formaContato = fc.nm_tipo_forma_contato
                                                  }).ToList();

                            item.temas = (from d in ctx.tb_diagnostico.AsNoTracking()
                                          where d.id_diagnostico == item.idDiagnostico
                                          from dpt in d.tb_diagnosticoperfiltema
                                          select new RetornaRelatorioEnvioSmsTemaModel
                                          {
                                              tema = dpt.tb_perfiltema.tb_tema.nm_tema,
                                              nivel = dpt.tb_nivelmaturidade.nm_nivel
                                          }).ToList();

                            item.temaPrioritario = (from d in ctx.tb_diagnostico.AsNoTracking()
                                                    where d.id_diagnostico == item.idDiagnostico
                                                    from dpt in d.tb_diagnosticoperfiltema
                                                    orderby dpt.nu_pontuacao descending, dpt.tb_perfiltema.nu_prioridade ascending
                                                    select new RetornaRelatorioEnvioSmsTemaModel
                                                    {
                                                        tema = dpt.tb_perfiltema.tb_tema.nm_tema,
                                                        nivel = dpt.tb_nivelmaturidade.nm_nivel
                                                    }).FirstOrDefault();
                        }
                    }

                    return ret;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
1
-4
8/14/2018 12:24:12 AM

Accepted Answer

Have the entities been set up with navigation properties? For instance does the Diagnostico entitiy have something like below declared?

public virtual Parciero ParcieroPf {get; set;} 
public virtual Parciero ParcieroPj {get; set;} 

From reading the second part of the query it does look like there are related entities mapped.

If you have navigation properties at your disposal then you can structure those queries to use the navigation properties rather than the embedded SQL. As mentioned, that way of querying is vulnerable to SQL injection and it should be a priority to eliminate it.

The performance cost you are likely seeing is due to the manual lazy-load that is being done to populate the various related details for the query results.

At a minimum you can speed up the loading of these related details by first extracting your "idDiagnostico" values from the query results and using those to load all of the related child records in one hit, then associate them to their respective Diagnostico entities:

So assuming you need to keep the SQL query at least to begin with:

// ... load SQL based initial data ...

List<RetornaRelatorioEnvioSmsModel> models = ctx.Database.SqlQuery<RetornaRelatorioEnvioSmsModel>(query).ToList();

if (models.Count == 0)
   return models;

// Fetch all applicable IDs.
var diagnosticoIds = ret.Select(x => x.idDiagnostico).ToList();

// Load the related data for *all* applicable diagnostico IDs above. Load into their view models, then at the end, split them among the related diagnostico.

var formasContatos = ctx.tb_diagnostico
    .Where(x => diagnosticoIds.Contains(x.id_diagnostico))
    .Select(x => new RetornaRelatorioEnvioSmsFormaContatoModel
    {
        formaContato = x.tb_parceiro.tb_tipo_forma_contato.nm_tipo_forma_contato
    }).ToList();

var temas = ctx.tb_diagnostico
    .Where(x => diagosticoIds.Contains(x.id_diagnostico))
    .Select(x => new RetornaRelatorioEnvioSmsTemaModel
    {
        tema = x.tb_diagnosticoperfiltema.tb_perfiltema.tb_tema.nm_tema,
        nivel = x.tb_diagnosticoperfiltema.tb_nivelmaturidade.nm.nivel
    }).ToList();

// This part is a bit tricky.. It looks like you want the the lowest nu_prioridade of the highest nu_pontuacao 
var temaPrioritario = ctx.tb_diagnostico
    .SelectMany(x => x.tb_diagnosticoperfiltema) // from diagnostico
    .SelectMany(x => x.tb_perfiltema) // from diagnostico.diagnosticoperfiltema
    .GroupBy(x => x.tb_diagnosticoperfiltema.tb_diagnostico.id_diagnostico) // group by diagnostico ID. Requires bi-directional references...
    .Select(x => new 
    {
        x.Key, // id_diagnostico
        Tema = x.OrderByDescending(y => y.tb_diagnosticoperfiltema.nu_pontuacao)
           .ThenBy(y => y.nu_prioridade)
           .Select(y => new RetornaRelatorioEnvioSmsTemaModel
           {
              tema = y.tb_tema.nm_tema,
              nivel = y.tb_diagnosticoperfiltema.tb_nivelmaturidade.nm_nivel
           }).FirstOrDefault())
    .Where(x => diagnosticoIds.Contains(x.Key))
    .Select(x => x.Tema)
    .ToList();

// Caveat, the above needs to be tested but should give you an idea on how to select the desired data.

foreach(var model in models)
{
   model.formasContato = formasContatos.Where(x => x.id_diagnostico == model.id_diagnostico).ToList();
   model.temas = temas.Where(x => x.id_diagnostico == model.id_diagnostico).ToList();
   model.temaPrioritario = temaPrioritarios.Where(x => x.id_diagnostico == model.id_diagnostico).ToList();
}

With the navigation properties though, this can all be done away with and loaded from the initial data model retrieved. It's a pretty complex model, and the (Italian?) naming convention makes it a bit hard to follow but hopefully that gives you some ideas on how to tackle the performance issues.

1
8/14/2018 4:12:45 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow