Slow LINQ query with two entitys

Question

This query are extremely slow. Can help I haven't any idea to where start. When remove .Include("Pessoa") the return is very fast.

[Table("Pessoa")]
public class Pessoa
{

    public int PessoaId { get; set; }

    public string NomPessoa { get; set; }

    public string FlgTipoPessoa { get; set; }

    public DateTime DatCadastro { get; set; }

    public string ObsPessoa { get; set; }

    public virtual ICollection<Endereco> Enderecos { get; set; }

    public Pessoa()
    {
        Enderecos = new List<Endereco>();
    }

}

[Table("PessoaFisica")]
public class PessoaFisica : Pessoa
{

    public string NumCpf { get; set; }

    public string NumRg { get; set; }

    public DateTime? DatNascimento { get; set; }

    public string FlgSexo { get; set; }

}

[Table("PessoaJuridica")]
public class PessoaJuridica : Pessoa
{
    public string NumCnpj { get; set; }

    public string NumInscricaoEstadual { get; set; }

    public string NumInscricaoMunicipal { get; set; }

}

 [Table("Funcionario")]
public class Funcionario : PessoaFisica
{
    public DateTime DatAdmissao { get; set; }
    public DateTime? DatDemissao { get; set; }
    public decimal PercRoyalty { get; set; }
}

[Table("FluxoDeCaixa")]
public class FluxoDeCaixa
{
    public int FluxoDeCaixaId { get; set; }

    public int PlanoDeContasId { get; set; }
    public PlanoDeContas PlanoDeContas { get; set; }

    public string DscFluxoDeCaixa { get; set; }

    public int FuncionarioId { get; set; }
    [ForeignKey("FuncionarioId")]
    public Funcionario Funcionario { get; set; }


    public decimal ValFluxoDeCaixa { get; set; }
    public DateTime DatEmissao { get; set; }
    public DateTime DatVencimento { get; set; }
    public DateTime DatFluxo { get; set; }

    public int PessoaId { get; set; }
    [ForeignKey("PessoaId")]
    public virtual Pessoa Pessoa { get; set; }

    public string NumDocto { get; set; }
    public string NumCheque { get; set; }
    public string FlgSituacaoCheque { get; set; }
    public string ObsFluxoDeCaixa { get; set; }        
}

public override IQueryable<Entity.Modelos.FluxoDeCaixa> Filtro(System.Linq.Expressions.Expression<System.Func<Entity.Modelos.FluxoDeCaixa, bool>> expressao)
    {
        return _contexto.FluxoDeCaixa.Include("Pessoa").Where(expressao);
    }

.Filtro(f => f.DatFluxo >= dataInicial.Date && f.DatFluxo <= dataFinal.Date).ToList();

This is the query generated by entity framework, I not understand why used so much Left Join. I believe this is the reason for the slowly.

{SELECT 
[Extent1].[FluxoDeCaixaId] AS [FluxoDeCaixaId], 
[Extent1].[PlanoDeContasId] AS [PlanoDeContasId], 
[Extent1].[DscFluxoDeCaixa] AS [DscFluxoDeCaixa], 
[Extent1].[FuncionarioId] AS [FuncionarioId], 
[Extent1].[ValFluxoDeCaixa] AS [ValFluxoDeCaixa], 
[Extent1].[DatEmissao] AS [DatEmissao], 
[Extent1].[DatVencimento] AS [DatVencimento], 
[Extent1].[DatFluxo] AS [DatFluxo], 
[Extent1].[PessoaId] AS [PessoaId], 
[Extent1].[NumDocto] AS [NumDocto], 
[Extent1].[NumCheque] AS [NumCheque], 
[Extent1].[FlgSituacaoCheque] AS [FlgSituacaoCheque], 
[Extent1].[ObsFluxoDeCaixa] AS [ObsFluxoDeCaixa], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN N'2X' WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN N'2X0X' WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN N'2X0X0X' ELSE N'2X1X' END AS [C1], 
[Join3].[PessoaId1] AS [PessoaId1], 
[Join3].[NomPessoa] AS [NomPessoa], 
[Join3].[FlgTipoPessoa] AS [FlgTipoPessoa], 
[Join3].[DatCadastro] AS [DatCadastro], 
[Join3].[ObsPessoa] AS [ObsPessoa], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[NumCpf] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[NumCpf] END AS [C2], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[NumRg] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[NumRg] END AS [C3], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[DatNascimento] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatNascimento] END AS [C4], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[FlgSexo] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[FlgSexo] END AS [C5], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatAdmissao] END AS [C6], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatDemissao] END AS [C7], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[PercRoyalty] END AS [C8], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumCnpj] END AS [C9], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumInscricaoEstadual] END AS [C10], 
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumInscricaoMunicipal] END AS [C11]
FROM  [FluxoDeCaixa] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[PessoaId] AS [PessoaId1], [Extent2].[NomPessoa] AS [NomPessoa], [Extent2].[FlgTipoPessoa] AS [FlgTipoPessoa], [Extent2].[DatCadastro] AS [DatCadastro], [Extent2].[ObsPessoa] AS [ObsPessoa], [Project1].[PessoaId] AS [PessoaId2], [Project1].[NumCnpj] AS [NumCnpj], [Project1].[NumInscricaoEstadual] AS [NumInscricaoEstadual], [Project1].[NumInscricaoMunicipal] AS [NumInscricaoMunicipal], [Project1].[C1] AS [C12], [Project3].[PessoaId] AS [PessoaId3], [Project3].[NumCpf] AS [NumCpf], [Project3].[NumRg] AS [NumRg], [Project3].[DatNascimento] AS [DatNascimento], [Project3].[FlgSexo] AS [FlgSexo], [Project3].[C1] AS [C11], [Project3].[DatAdmissao] AS [DatAdmissao], [Project3].[DatDemissao] AS [DatDemissao], [Project3].[PercRoyalty] AS [PercRoyalty], [Project3].[C2] AS [C2]
    FROM   [Pessoa] AS [Extent2]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[PessoaId] AS [PessoaId], 
        [Extent3].[NumCnpj] AS [NumCnpj], 
        [Extent3].[NumInscricaoEstadual] AS [NumInscricaoEstadual], 
        [Extent3].[NumInscricaoMunicipal] AS [NumInscricaoMunicipal], 
        cast(1 as bit) AS [C1]
        FROM [PessoaJuridica] AS [Extent3] ) AS [Project1] ON [Extent2].[PessoaId] = [Project1].[PessoaId]
    LEFT OUTER JOIN  (SELECT 
        [Extent4].[PessoaId] AS [PessoaId], 
        [Extent4].[NumCpf] AS [NumCpf], 
        [Extent4].[NumRg] AS [NumRg], 
        [Extent4].[DatNascimento] AS [DatNascimento], 
        [Extent4].[FlgSexo] AS [FlgSexo], 
        cast(1 as bit) AS [C1], 
        [Project2].[DatAdmissao] AS [DatAdmissao], 
        [Project2].[DatDemissao] AS [DatDemissao], 
        [Project2].[PercRoyalty] AS [PercRoyalty], 
        CASE WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL))) THEN cast(0 as bit) END AS [C2]
        FROM  [PessoaFisica] AS [Extent4]
        LEFT OUTER JOIN  (SELECT 
            [Extent5].[PessoaId] AS [PessoaId], 
            [Extent5].[DatAdmissao] AS [DatAdmissao], 
            [Extent5].[DatDemissao] AS [DatDemissao], 
            [Extent5].[PercRoyalty] AS [PercRoyalty], 
            cast(1 as bit) AS [C1]
            FROM [Funcionario] AS [Extent5] ) AS [Project2] ON [Extent4].[PessoaId] = [Project2].[PessoaId] ) AS [Project3] ON [Extent2].[PessoaId] = [Project3].[PessoaId] ) AS [Join3] ON [Extent1].[PessoaId] = [Join3].[PessoaId1]
WHERE ([Extent1].[DatFluxo] >= @p__linq__0) AND ([Extent1].[DatFluxo] <= @p__linq__1)}

Answer 1

Your problem may be due to using Table Per Type(TPT) inheritance in your data model. I'm struggling with a similar issue at the moment. In order to materialise the correct derived type when selecting on a base type, Entity Framework joins the base type table with every possible derived table unless you use the OfType<>() method.

context.BaseEntities.OfType<DerivedEntityX>();

I've found it's best to use TPT only in simple cases where there are not too many derived types and where there is an inheritance depth of just one. Where you do use TPT try and perform simple queries only. You may find that pre-generating views may reduce your initial cold query execution time. Projecting to an anonymous type or view model where you can select just the necessary attributes and/or use the OfType<>() method may also be useful in increasing performance. Of course you could also consider using Table Per Heirarchy (TPH) so that all your derived types map to a single table.

Answer 2

not sure what ef version you are using but

1) have proper indexes in db , use that generated sql as payload (modify to hardcode parameters): Using Database Engine Tuning Advisor

2) if you are using objectcontext directly then this may speed things up (depending on how large date range is) then modify Filtro body like this:

var dataList= _contexto.FluxoDeCaixa.Where(expressao).ToList();

for (int x = 0; x < dataList.Count; x++)
     _contexto.LoadProperty(dataList[x], p => p.Pessoa);

return dataList;

3) idealy if you want to get max performance, redo it so you would not need to return every column, return only what is needed like:

_contexto.FluxoDeCaixa.Select(t => new { t.DatFluxo , t.DatVencimento}).Where ....

Answer 3

First, if you do not need to query Pessoa, then do not include it.

Second, this is probably because of the following property of your Pessoa entity:

public virtual ICollection<Endereco> Enderecos { get; set; }

Since you declared Enderecos as virtual, it will be lazy-loaded every time you query the Pessoa entity.

Please read this link for more info on loading related properties.