I have a query which it need to run 300+ loops every single call. It takes about 10 seconds to complete a call even on a new database. It's unacceptable for a WebAPI call.
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;
How can I optimize the speed by combining all the queries into one? The loop content literally the same except the day.There are total 15 queries to be call on each loop, 4500 db queries in a complete loop.
Think in terms of sets and relational data and not procedurally. It's not easy to determine exactly what you want from your code (which contradicts itself - queryable
is set by a call to db.ObjectA.Where(...)
which is an IQueryable<ObjectA>
but then it is also set by a call to db.ObjectA.First(...)
, which is an ObjectA
; I'll assume you want the IQueryable<ObjectA>
since later code references queryable.Any(...)
) but here's my guess:
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 ),
} );
The results are an IQueryable
of an anonymous type that maps an "active" Operation.Day
with your logic for IsLogicACorrect
. For your other IsLogicXCorrect
flags, if they can all be determined using the ObjecetA
group aoGroup
, simply add them to the GroupJoin
result selector (as shown in the commented-out property). If those flags need their own groupings (e.g. need to use ObjectB
group to determine IsLogicBCorrect
, then add additional calls to GroupJoin
as shown above but using their respective DbSet
and properties. For example, if you need to use db.ObjectB
for 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 ),
} );