I am working on an issue dealing with the speed of our intranet web application and a function that gets the descendants of all items in our database.
I do not have access to the code at home, but I can describe the issue and process:
We have a database table that stores items that can be linked to a parent item in the same table. We have logic that prevents circular references, so in theory the descendant link should never circle back (so far no such thing has happened).
When a user accesses a page on the web app, currently I have functions that get the list of descendants of that item and puts it into an object. This was originally done in a normal FOR loop, but the time it took to parse through 881 entities would take 12+ seconds, which needs to change. I looked into threading and implemented the Parallel FOR loop, and it took the time from 12+ seconds to 1-2 seconds, except I randomly get errors on the page.
Some of the errors include the drop down list not being populated, the connection state not being open and available, and connection being closed and not available.
The original code is something like:
var DS = _uow.locations.AllEager().FirstOrDefault( l => l.Zone == "DS");
var hwddl = new List<HWTXSelectItem>();
var HWinDS = _uow.hw.AllEager().Where(h => h.Locations_ID == DSS.Locations_ID).OrderBy(h => h.ID).ToArray();
var dataSet =_uow.hw.AllEager().Where(h => h.IsActive && h.Parent_ID != null).Select(h => new HierarchyObject() {id=h.HW_ID, parentId = Parent_ID}).ToList();
var assets = _uow.hw.AllEager();
var arrayCount = HWinDS.Length;
for(int i = 0; i < arrayCount; i++){
var hw in HWinDS[i];
var HWDescendants = Helpers.FindAllChildren(dataSet, new HierarchyObject() {id=hw.HWID, parendId=null}).ToList();
var descendends = new List<int>();
descendends.AddRange(HWDescendants.Select(m => m.id);
var allChildren = assets.Where(h => descendends.Contains(h.HW_ID) && IsActive).ToList();
var direct = allChildren.Where(h => h.ParentID == hw.HW_ID).Select(h => new{hwid = h.HWID, id=h.HW_ID}).OrderBy(h => h.hwid).ToList();
var sub = allChildren.Where(h => h.Parent_ID != hw.HW_ID).Select(h => new{hwid = h.HWID, id=h.HW_ID}).OrderBy(h => h.hwid).ToList();
hwtxddl.Add(new HWTXSelectItem
{
...
... <obj properties>
...
});
}
HWFromDS = hwddl;
The parallelized is something like:
var DS = _uow.locations.AllEager().FirstOrDefault( l => l.Zone == "DS");
var hwddl = new List<HWSelectItem>();
var HWinDS = _uow.hw.AllEager().Where(h => h.Locations_ID == DS.Locations_ID).OrderBy(h => h.ID).ToArray();
var dataSet =_uow.hw.AllEager().Where(h => h.IsActive && h.Parent_ID != null).Select(h => new HierarchyObject() {id=h.HW_ID, parentId = Parent_ID}).ToList();
var items = _uow.hw.AllEager();
var arrayCount = HWinDS.Length;
Parallel.For(0, arrayCount,i =>
{
var hw in HWinDS[i];
var HWDescendants = Helpers.FindAllChildren(dataSet, new HierarchyObject() {id=hw.HW_ID, parendId=null}).ToList();
var descendends = new List<int>();
descendends.AddRange(HWDescendants.Select(m => m.id);
var allChildren = assets.Where(h => descendends.Contains(h.HWAssets_ID) && IsActive).ToList();
var direct = allChildren.Where(h => h.Parent_ID == hw.HW_ID).Select(h => new{hwid = h.HWID, id=h.HW_ID}).OrderBy(h => h.hwid).ToList();
var sub = allChildren.Where(h => h.Parent_ID != hw.HW_ID).Select(h => new{hwid = h.HWID, id=h.HW_ID}).OrderBy(h => h.hwid).ToList();
hwddl.Add(new HWSelectItem
{
...
... <obj properties>
...
});
}
HWFromDS = hwddl;
I have some variations of parallel for loops that work and speed up the process, getting an overall time of 6 seconds with partial data returning and no errors and 1 second with all data returning with random errors. The one above seems to work with full data returning but gives the random errors at random points. Any tips?
