.NET LINQ Expression< Func& T，bool& gt;性能问题 [英] .NET LINQ Expression<Func<T, bool>> Performance Issues

问题描述

我有以下两个功能-

public IQueryable<RequestSummaryDTO> GetProgramOfficerUSA(Guid officerId)
{
    List<string> officerCountries = UnityProvider.Instance.Get<IProgramOfficerService>().GetPOCountries(officerId).Select(c => c.CNTR_ID.ToUpper()).ToList();
    Expression<Func<TaskRequest, bool>> countriesFilter = (a) => officerCountries.Contains(a.tblTaskDetail.FirstOrDefault().tblOrganization.ORG_Country.ToUpper());

    Expression<Func<TaskRequest, bool>> USAAndNotDelegated = LinqUtils.And(this.USAFilter(), this.NotDelegatedFilter(officerId));
    Expression<Func<TaskRequest, bool>> countriesOrOwned = LinqUtils.Or(countriesFilter, this.OwnedFilter(officerId));
    Expression<Func<TaskRequest, bool>> filter = LinqUtils.And(USAAndNotDelegated, countriesOrOwned);

    return this.Get(filter, TaskRequestState.USA);
}



private IQueryable<RequestSummaryDTO> Get(Expression<Func<TaskRequest, bool>> additionalFilter, TaskRequestState? TaskRequestState = null)
{
   var Tasks = this.TaskRequestRepository.List(additionalFilter).Where(x => x.tblTaskDetail.FirstOrDefault().PD_TaskRequestID == x.Id && 
            (x.tblRequestDetail.AD_Status == (int)RequestStatus.Paid || x.tblRequestDetail.AD_Status == (int)RequestStatus.NotConfirmed));

    if (TaskRequestState == TaskRequestState.USA)
    {
        Tasks = Tasks.Where(w => (w.tblTaskDetail.FirstOrDefault().PD_PStatus == null || w.tblTaskDetail.FirstOrDefault().PD_PStatus == 125));
    }
    else
    {
        Tasks = Tasks.Where(w => w.State == null);
    }

    return Tasks.ToList().Select(TaskSummaryFactory.CreateDto).AsQueryable();
}

我在LINQ中使用了Expression>和IQueryable.应该使用LINQ To SQL而不是LINQ To Objects.表现应该不错.

I used Expression> and IQueryable in the LINQ. It is supposed to use LINQ To SQL instead of LINQ To Objects. The performance should be good.

但是我看不到.我相信LINQ使用LINQ To Objects将每个表的数据拉入内存进行处理.我看不到按SQL配置文件跟踪发送到SQL Server的联接sql查询，但是看不到一堆单独的表选择语句.

But I do not see that. I believe the LINQ pulls every table's data into memory to process using LINQ To Objects. I do not see a join sql query sent to SQL Server tracking by SQL profile but a bunch of individual table selection statement.

从

return Tasks.ToList().Select(TaskSummaryFactory.CreateDto).AsQueryable();

我知道问题出在

Expression<Func<TaskRequest, bool>> countriesFilter = (a) => officerCountries.Contains(a.tblTaskDetail.FirstOrDefault().tblOrganization.ORG_Country.ToUpper());

tblTask​​Detail是一个大表.如果我切换到较小的服务器，性能会得到明显改善.

tblTaskDetail is a big table. If I switch to a smaller one, the performance is noticeably improved.

任何人都可以帮助找出那里的错误原因.

Anyone can help find out why is wrong there.

谢谢

更新1-来自SQL概要文件中登录的Entity Framework的语句都像这样-

Update 1 - The statement from Entity Framework logged in SQL Profile are all like this -

exec sp_executesql N'SELECT [Extent1].[ORG_ID] AS [ORG_ID]，[Extent1].[ORG_CreatedBy] AS [ORG_CreatedBy]，[Extent1].[ORG_CreatedOn] AS [ORG_CreatedOn]来自[dbo].] AS [Extent1] WHERE [Extent1].[ORG_ID] = @ EntityKeyValue1'，N'@ EntityKeyValue1 uniqueidentifier'，@ EntityKeyValue1 ='E8C3F120-AA40-445E-A8A0-2937F330D347'

exec sp_executesql N'SELECT [Extent1].[ORG_ID] AS [ORG_ID], [Extent1].[ORG_CreatedBy] AS [ORG_CreatedBy], [Extent1].[ORG_CreatedOn] AS [ORG_CreatedOn] FROM [dbo].[tblOrganization] AS [Extent1] WHERE [Extent1].[ORG_ID] = @EntityKeyValue1',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='E8C3F120-AA40-445E-A8A0-2937F330D347'

它们都只是具有单独的表select语句，而不是联接的sql语句.

They are all just having individual table select statement, not joined sql statement.

更新2-

我在Update 1中错了.我错过了join SQL语句.问题是生成的SQL太差了.有6个嵌套的select语句，11个LEFT OUTER JOIN和10个OUTER APPLY.查询过长，无法在此处发布.执行生成的SQL需要9分钟.

I was wrong in Update 1. I missed the join SQL statement. The problem is that the generated SQL is too poor. There are 6 nested select statements, 11 LEFT OUTER JOINs, and 10 OUTER APPLYs. The query is too long and can not post here. Executing the generated SQL takes 9 minutes.

推荐答案

我已将EF STE 5升级到EF6.现在性能更好.页面加载时间从12分钟减少到1.5分钟.

I have upgraded the EF STE 5 to EF6. Now the performance is better. The page loading time is cut down to 1.5 minutes from 12 minutes.

这篇关于.NET LINQ Expression< Func& T，bool& gt;性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！