c# - 使用实体框架查询非常慢
问题描述
我正在处理另一家公司的项目,该公司开发了一种使用 Entity Framework 6 从数据库中检索信息的方法,以填充网格组件。
使用这种方法,我们面临着巨大的性能问题,即使我们只有几行,比如 70、100 等。我只是不知道该怎么做。
如果您能告诉我正确的方法,我将不胜感激。
一些信息:Visual Studio 2013 .NET 4.5 SQL Server 2017
这个方法:
[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;
}
}
解决方案
实体是否设置了导航属性?例如,诊断实体是否声明了类似下面的内容?
public virtual Parciero ParcieroPf {get; set;}
public virtual Parciero ParcieroPj {get; set;}
从阅读查询的第二部分来看,确实存在映射的相关实体。
如果您可以使用导航属性,那么您可以构建这些查询以使用导航属性而不是嵌入式 SQL。如前所述,这种查询方式容易受到 SQL 注入的影响,应该优先消除它。
您可能看到的性能成本是由于为填充查询结果的各种相关详细信息而进行的手动延迟加载。
至少,您可以通过首先从查询结果中提取“idDiagnostic”值并使用这些值一次性加载所有相关子记录,然后将它们关联到各自的 Diagnostico 实体,从而加快这些相关详细信息的加载:
因此,假设您至少需要保留 SQL 查询:
// ... 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();
}
但是,使用导航属性,这一切都可以取消并从检索到的初始数据模型中加载。这是一个相当复杂的模型,并且(意大利语?)命名约定使它有点难以遵循,但希望这能给您一些关于如何解决性能问题的想法。
推荐阅读
- python - 改进长硒搜索
- php - 从 ionic 中的 HTTP 请求中获取响应
- tensorflow - 为单个图像实施 CVAE
- python - 如何在不使用 random.seed() 的情况下基于种子创建随机数?
- sass - 使用 str-split 和 Sass 时如何解决在输出上有 pharentesis 的字符串拆分问题
- typescript - 创建一个可以具有多种值类型的地图子类
- php - Laravel 安装程序内存不足
- reactjs - 如何从 React 类组件调用钩子?
- mysql - 无法删除 AWS 上的 RDS
- javascript - 如何在 auth0-js 的授权 URL 中添加“&onlyLock=true”?