CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
A.T.
A.T.

Reputation: 26312

Efficient way to create parent-child relation

To obtain parent-child relation i use recursion,

 public List<MasterDiagnosi> GetAllDiagonsis()
        {
            IEnumerable<MasterDiagnosi> list = this.GetMasterDiagnosi();
            List<MasterDiagnosi> tree = new List<MasterDiagnosi>();
            var Nodes = list.Where(x => x.DiagnosisParentID == 0 && x.Status == 2 && x.DiagnosisLanguageID == 2);
                foreach (var node in Nodes)
                {
                    SetChildren(node, list);
                    tree.Add(node);
                }
            return tree;
        }
        private void SetChildren(MasterDiagnosi model, IEnumerable<MasterDiagnosi> diagonisList)
        {   
            var childs = diagonisList.Where(x => x.DiagnosisParentID == model.DiagnosisID && x.Status == 2 && x.DiagnosisLanguageID == 2);
            if (childs.Count() > 0)
            {
                foreach (var child in childs)
                {
                    SetChildren(child, diagonisList);
                    model.MasterDiagnosis.Add(child);
                }
            }
        }

It work great for 0-500 records, but if i have records more than 5k or 10k then i am ended up with worst case of 4-5 minutes of processing. I am looking for another way around,or any optimization. Currently i save my day using cahce, but i need this to be done as various CRUD operations may be done. Any Help ?

Upvotes: 0

Views: 245

Answers (2)

Michael
Michael

Reputation: 1473

Try to disable AutoDetectChanges, while you are inserting values into the table, to prevent EntityFramework change tracker firing every time behind on scene. It helped me when I loaded tens thousand records to the database. Look at the example below

model.Configuration.AutoDetectChangesEnabled = false;
// insert data
model.DataBaseContext.ChangeTracker.DetectChanges();
model.SubmitChanges();

Upvotes: 0

Pantelis Natsiavas
Pantelis Natsiavas

Reputation: 5369

I would suggest to save the tree data structure inside the relational database. In this presentation of advanced data structures you could find some data structures which could be used efficiently to store a tree in a database. Some of them are quite simple.

I have to warn you that these structures are not always easy to implement. The gain in performance could be great though.

I have used the nested set model which I found very efficient. This of course depends on your specific tree navigation scenarios. As Evan Petersen suggests, the nested sets hierarchical model is very sufficient when someone wants to retrieve all the children of one node, but very inefficient for deleting a node because the whole tree has to be reorganized. Therefore, you should make your own decision, by taking your specific tree navigation and update scenarios into account.

Hope I helped!

Upvotes: 1

Related Questions