Juan Carlos Oropeza - 1 year ago 43

SQL Question

**class**

`public class myItem`

{

public int ID;

public long Value;

public long Sum1;

public long Sum2;

public long Sum3;

}

`ID Value`

1 25

2 45

3 56

4 21

`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

`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`

Answer Source

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;
}
}
```