目录
介绍
Web应用程序基于EF3.1。针对生产数据库的SQL分析显示检索了近100,000个表行。
背景
导致返回这么多表行的LINQ查询如下所示:
var responses = (from cr in _dbContext.CarrierResponses where cr.TenantId == tenantId select cr).ToList()
var modified = responses.Where(cr => modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc));
其中modifiedLoads是从应用用户请求传递的修改负载列表。如果租户非常活跃,第一个语句会返回大量记录。
我想知道为什么原始代码作者编写这个查询来返回这么多表记录。从源代码提交历史中,我发现代码是前一段时间编写的,如下所示。
var responses = from cr in _dbContext.CarrierResponses
where cr.TenantId == tenantId && modifiedLoads.Any(m => cr.LoadId == m.LoadId && cr.DateModified < m.ModifiedDateTimeUtc)
select cr;
它应该在EF Core 3.0之前工作,默认情况下允许客户端评估。顺便说一句,即使它有效,根据EF Core 3.x中的重大更改下的文档,“这种行为可能导致意外和潜在的破坏性行为,这些行为可能只会在生产中变得明显......可能导致所有行从要从数据库服务器传输的表,以及要应用于客户端的过滤器。”,在这种情况下使用客户端评估不是一个好主意。
该应用程序应该已经用新版本的EF内核升级了一段时间,所以原来的LINQ不再工作,并且像上面显示的那样创建了一个错误修复。它只是让它工作,可能只在生产中变得明显的性能问题没有得到解决。
有关通过linq操作进行显式客户端评估的更多信息,请参阅 客户端与服务器评估。
修复问题
1. 通过动态Lambda表达式修复
创建动态表达式函数:
private Expression<Func<CarrierResponse, bool>> FilterCarrierResponsesBy(IEnumerable<OpenLoadModified> modifiedLoads)
{
var carrierResponseFilterExpression = Expression.Parameter(typeof(CarrierResponse));
CarrierResponse cr;
var expressionList = new List<Expression>();
modifiedLoads.ToList().ForEach(x =>
{
var exprLoadIdEqual = Expression.Equal(Expression.Property(carrierResponseFilterExpression, nameof(cr.LoadId)), Expression.Constant(x.LoadId));
var exprDateTimeLessThan = Expression.LessThan(Expression.Property(carrierResponseFilterExpression, nameof(cr.DateCreated)), Expression.Constant(x.ModifiedDateTimeUtc));
var andExp = Expression.And(exprLoadIdEqual, exprDateTimeLessThan);
expressionList.Add(andExp);
});
var exprModifiedLoadOr = null as Expression;
foreach (var expr in expressionList)
{
if (exprModifiedLoadOr == null)
{
exprModifiedLoadOr = expr;
continue;
}
exprModifiedLoadOr = Expression.Or(exprModifiedLoadOr, expr);
}
return Expression.Lambda<Func<CarrierResponse, bool>>(exprModifiedLoadOr, carrierResponseFilterExpression);
}
以这种方式使用此动态表达式:
_dbContext.CarrierResponses.Where(x => x.TenantId == tenantId).Where(FilterCarrierResponsesBy(modifiedLoads));
它生成如下SQL语句:
exec sp_executesql N'SELECT [c].[Id], [c].[CarrierId], [c].[DateCreated], [c].[DateModified], [c].[IsLoadModified], [c].[LoadId], [c].[PrevResponseCode], [c].[PrevUserName], [c].[ResponseCode], [c].[TenantId], [c].[UserName]
FROM [CarrierResponses] AS [c]
WHERE ([c].[TenantId] = @__tenantId_0) AND (((CASE
WHEN [c].[LoadId] = N''999'' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END) | (CASE
WHEN [c].[LoadId] = N''1000'' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END & CASE
WHEN [c].[DateCreated] < ''2021-12-20T19:13:19.6866667'' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
与使用客户端评估的现有代码的“读取:43,持续时间:35186”相比,它运行得如此之快,例如来自Profiler的“读取:57,持续时间:3662”。
2.通过存储过程修复
首先,创建一个用户定义的数据类型(自SQL Server 2012起支持):
CREATE TYPE [dbo].[OpenLoadModified] AS TABLE(
[LspId] [nvarchar](10) NOT NULL,
[LoadId] [nvarchar](30) NOT NULL,
[ModifiedDateTimeUtc] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[LspId] ASC,
[LoadId] ASC
) WITH (IGNORE_DUP_KEY = OFF)
)
然后创建存储过程:
CREATE PROC [dbo].[GetCarrierResponses]
(
@TenantId BIGINT,
@LoadsModified OpenLoadModified READONLY
)
AS
BEGIN
SELECT r.*
FROM CarrierResponses AS r WITH(NOLOCK)
JOIN @LoadsModified AS m ON r.[LoadId] = m.[LoadId]
WHERE r.TenantId = @TenantId AND r.DateModified < m.ModifiedDateTimeUtc
END
最后,在应用程序的业务部分,使用以下代码:
var dtModifiedLoads = new DataTable();
dtModifiedLoads.Columns.Add("LspId", typeof(string));
dtModifiedLoads.Columns.Add("LoadId", typeof(string));
dtModifiedLoads.Columns.Add("ModifiedDateTimeUtc", typeof(DateTime));
foreach (var load in modifiedLoads)
{
var row = dtModifiedLoads.NewRow();
row["LspId"] = load.LspId;
row["LoadId"] = load.LoadId;
row["ModifiedDateTimeUtc"] = load.ModifiedDateTimeUtc;
dtModifiedLoads.Rows.Add(row);
}
_DAL.GetDataSetBySqlParameter("[dbo].[GetCarrierResponses]", new SqlParameter[] {
new SqlParameter("@TenantId", tenantId),
new SqlParameter("@LoadsModified", SqlDbType.Structured)
{
TypeName = "dbo.OpenLoadModified",
Value = dtModifiedLoads
}
});
它生成这些SQL语句:
declare @p2 dbo.OpenLoadModified
insert into @p2 values(N'7929497',N'999','2021-12-20 19:13:19.6866667')
insert into @p2 values(N'7929497',N'1000','2021-12-20 19:13:19.6866667')
exec [dbo].[GetCarrierResponses] @TenantId=30,@LoadsModified=@p2
Profiler报告“读取次数:50,持续时间:4300”。
兴趣点
在Entity Framework的早期,我可能已经编写了一些客户端评估LINQ代码。希望它不会导致太大的性能问题,并且上面列出的修复可能对错误修复者有任何帮助。
https://www.codeproject.com/Tips/5319859/Fixes-to-LINQ-to-SQL-Explicit-Client-Evaluation-wh