Multiple sums in sql when not required
Posted: Fri 15 Apr 2016 16:19
Found a bit of a performance issue with the generated sql in some instances. I've reproduced on a sample database. The LINQ is:
The resultant sql carries out the Sums on TblBills twice each making 4 in total. The issue appears to be cuased by the ?? 0M on the end. As soon as these are removed the sql has the expected two sums (but I do need them as it must return 0 not null if there are no records).
If I then add a where clause based on the calculated variables like so
It is doubled, so that the the total amount of sums is 8 instead of 2. The example is trivial but when scaled up it is too slow even with indices. Is there any optimisation you can do your side to the SQL or is this a tree problem?
Head -> Accs is a 1 -> 1
Head -> Bills is a 1 -> *
I can send you the schema if you need it.
Thanks.
VS2013, EF6.01, Devart 7.4.592
Code: Select all
var dt = new DateTime(2005, 10, 1);
var q1 = (from mh in entity.Head
select new
{
t = (decimal?)(mh.Accs.Bal1 - mh.Bills.Where(o => o.StampDate > dt).Sum(o => o.Amount - o.Total ?? 0M)) ?? 0M,
a = (decimal?)(mh.Accs.Bal2 - mh.Bills.Where(o => o.StampDate <= dt).Sum(o => o.Amount)) ?? 0M,
mh.OIDHead
}).ToList();
If I then add a where clause based on the calculated variables like so
Code: Select all
var q1 = (from mh in entity.Head
select new
{
t = (decimal?)(mh.Accs.Bal1 - mh.Bills.Where(o => o.StampDate > dt).Sum(o => o.Amount - o.Total ?? 0M)) ?? 0M,
a = (decimal?(mh.Accs.Bal2 - mh.Bills.Where(o => o.StampDate <= dt).Sum(o => o.Amount)) ?? 0M,
mh.OIDHead
}).Where(o => o.t != 0M || o.a != 0M).ToList();
Head -> Accs is a 1 -> 1
Head -> Bills is a 1 -> *
I can send you the schema if you need it.
Thanks.
VS2013, EF6.01, Devart 7.4.592