我有一个查询，每次调用都需要运行 300 多个循环。即使在新数据库上完成调用也需要大约 10 秒的时间。WebAPI 调用是不可接受的。
var isAbnormal = false;
var list = new List<String>();
//Check date range, log them & return if there is any abnormal.
foreach (DateTime day in DateHelper.EachDay(startDate, endDate))
{
var isActive = db.Operations.Any(x=>x.IsActive && x.Day == day);
var object;
var queryable = db.ObjectA.Where(x=>x.Day == day);
if(isActive){
queryable = db.ObjectA.First(x=>x.Day == day);
LogUtil.Info($"{object.Name}");
var isLogicACorrect = queryable.Any(x=>x.ObjectACount == 5);
var isLogicBCorrect = queryable.Any(x=>x.ObjectBCount == 3);
var isLogicCCorrect = queryable.Any(x=>x.ObjectCCount == 2);
var isLogicDCorrect = queryable.Any(x=>x.ObjectDCount == 8);
var isLogicECorrect = queryable.Any(x=>x.ObjectECount == 1);
if(!isLogicACorrect){
list.Add("Logic A is incorrect");
isAbnormal = true;
}
//More logic codes & db calls here, which is just to select & validate.
}
return list;
如何通过将所有查询合并为一个来优化速度？除了一天之外，循环内容几乎相同。每个循环总共要调用 15 个查询，在一个完整循环中调用 4500 个 db 查询。
从集合和关系数据的角度考虑，而不是从程序上考虑。从代码中准确确定您想要什么并不容易（这与自身相矛盾 -queryable由对db.ObjectA.Where(...)which is an的调用设置，IQueryable<ObjectA>但随后也由对 的调用设置db.ObjectA.First(...)，即 an ObjectA；我假设您IQueryable<ObjectA>稍后想要代码参考queryable.Any(...)）但这是我的猜测：
var days = DateHelper.EachDay( startDate, endDate );
var activeDaysIsLogicCorrectFlags = db.Operations
// get days that are "active"
.Where( op => op.IsActive && days.Contains( op.Day ) )
// join with ObjectA's to filter for active ObjectA's
// is there a nav property you could use instead?
// use GroupJoin for use with `Any(...)` in results
.GroupJoin( db.ObjectA, op => op.Day, oa => oa.Day, ( op, oaGroup ) => new
{
//Operation = op,
// projecting Operation.Day since that's what your foreach loop is using
Day = op.Day,
IsLogicACorrect = oaGroup.Any( oa => oa.ObjectACount == 5 ),
// if IsLogicBCorrect can be determined from the collection of ObjectA's:
//IsLogicBCorrect = oaGroup.Any( oa => oa.ObjectBCount == 3 ),
} );
结果是一个IQueryable匿名类型，将“活动”映射到Operation.Day您的逻辑IsLogicACorrect。对于您的其他IsLogicXCorrect标志，如果它们都可以使用ObjecetAgroup确定aoGroup，只需将它们添加到GroupJoin结果选择器（如注释掉的属性中所示）。如果这些标志需要它们自己的分组（例如，需要使用ObjectBgroup 来确定IsLogicBCorrect，然后添加其他调用，GroupJoin如上所示，但使用它们各自的DbSet和 属性。例如，如果您需要使用db.ObjectBfor IsLogicBCorrect：
var activeDaysIsLogicCorrectFlags =
<existing logic from above>
.GroupJoin( db.ObjectB, at => at.Day, ob => ob.Day, ( at, obGroup ) => new
{
// project all previous results
at.Day,
at.IsLogicACorrect,
// new flag
IsLogicBCorrecet = obGroup.Any( ob => ob.ObjectBCount == 3 ),
} );
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句