Juan Carlos Oropeza - 1 year ago 66
SQL Question

# Better way to calculate the SUM of one, two and three consecutive items

class

``````public class myItem
{
public int ID;
public long Value;
public long Sum1;
public long Sum2;
public long Sum3;
}
``````

data

``````ID   Value
1    25
2    45
3    56
4    21
``````

result:

``````     Sum1     Sum2         Sum3
1    25     25 + 45    25 + 45 + 56
2    45     45 + 56    45 + 56 + 21
3    56     56 + 21    56 + 21 + 0
4    21     21 + 0     21 +  0 + 0
``````

Current procedure: Works but very slow (~10 minutes) with 100k rows.

``````List<myItem> list;
for (int i = 0; i < list.Count(); i++)
{
myItem m = list[i];
m.Sum1 = list.Where(x => x.ID == i).Sum(x => x.Value);
m.Sum2 = list.Where(x => x.ID >= i && x.ID <= i + 2).Sum(x => x.Value);
m.Sum3 = list.Where(x => x.ID >= i && x.ID <= i + 3).Sum(x => x.Value);
}
``````

So I guess there should be a way to do it without the
`for`
to speed things up.

The root cause of the slowness is that your loop is O(n^2), since for each element you search the entire list for its successors. Below reduces that to O(n log n) (slowest part is the sort).

``````List<myItem> list;
list.Sort(<.. sort by id ..>);
for (int i = 0; i < list.Count(); i++)
{
myItem m = list[i];
m.Sum1 = m.Value;
if (i < list.Count() - 1)
{
m.Sum2 = m.Value+ list[i + 1].Value;
}
if (i < list.Count() - 1)
{
m.Sum3 = m.Value + list[i + 1].Value + list[i + 2].Value;
}
}
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download