c# Performance Issue while saving to DB

Question

     foreach (DictionaryEntry ID in IDs)  
        {  
            int qWorkID = int.Parse(ID.Value.ToString());  
            int SplitID = Convert.ToInt32(ID.Key.ToString());  
  
            var INSP = (from insp in Model.InstallSplitSet  
                join qwf in Model.QWorkflowSet on insp.SplitID equals qwf.SplitID  
                //where insp.SplitID == SplitID  
                select new { insp, qwf });  
      
            //QWorkIDs.Contains(qwf.QWorkID)  
            foreach (DictionaryEntry DE in IDs)  
            {  
                if (DE.Key.ToString() == ID.ToString())  
                    INSP = INSP.Where(it => it.qwf.QWorkID == qWorkID);  
            }  
  
            var _INSP = INSP.Select(it => new { INSP = it.insp }).FirstOrDefault();  
            _INSP.INSP.Locked = IsLocked;  
            _INSP.INSP.ModifiedBy = ModifiedBy;  
            _INSP.INSP.ModifiedDt = DateTime.Today;  
      
                                
            Model.SaveChanges();  
        }  
  

Above Loop is causing performance Issue....More IDs in collection it will get slower..

How do I Bring out Model.SaveChanges(); from foreach loop and push everything in Bulk.

Answer 1

Just looking at the code, move the SaveChanges call outside the foreach loop. Then it does all the work and then saves once. But I'm making lots of assumptions here. For example we have no idea what Model actually is nor do we know what SaveChanges actually does. This sort of looks like EF but SaveChanges is on DbContext and Model tends to mean the data you're working with, not the entire database, so I'm struggling with trying to figure out if Model is actually DbContext in which case I think you're going about your data access incorrectly which may impact performance as well.

The next thing I notice about you code is that you're parsing the value as an int but using Convert for the key. If the dictionary is strongly typed (and it should be) then you don't need this at all. If you are using ToString in your code you are probably doing something wrong, in my experience.

Next, each time through the loop you're joining InstallSplitSet with QWorkflowSet. You then enumerate all the IDs (which you're already enumerating) to find the one entry (I assume) that matches it. But you already have the one instance that matches it because you're in the middle of enumerating the same dictionary. So you don't need the inner foreach at all from what I can tell. It seems like you can just add the Where call to your previous join of the 2 tables since there should be only 1 match each time through the loop anyway.

Finally, in the select (which is what actually triggers the DB call if you're using EF) you are creating an anonymous type to capture a single value in the object. This is pretty much always the wrong approach. Just return the single value directly.

So I think the previous code could be simplified to this.

   foreach (DictionaryEntry ID in IDs)  
   {  
      int qWorkID = int.Parse(ID.Value.ToString());   
      int SplitID = Convert.ToInt32(ID.Key.ToString());  
         
      var INSP = (from insp in Model.InstallSplitSet  
                         join qwf in Model.QWorkflowSet on insp.SplitID equals qwf.SplitID         
                         where qwf.QWorkID == qWorkID  
                         select new { insp, qwf }).FirstOrDefault();  
             
      INSP.insp.Locked = IsLocked;  
      INSP.insp.ModifiedBy = ModifiedBy;  
      INSP.insp.ModifiedDt = DateTime.Today;  
     };  
            
    Model.SaveChanges();  

Without testing I cannot confirm but it is possible you don't even need the anonymous type in the select. Just return insp but that may cause an issue if the join doesn't return any results on the right side of that join. You'd need to test.

You could probably even get rid of the join each time through the loop by simply adjusting the where clause to grab all insp entries that have a qwf that is in qWorkID. You can do that by putting the qWorkID into a list and then passing the list to the where. Something like this perhaps (not tested).

   var qWorkIDs = IDs.Select(x => Int32.Parse(x));  
   var items = from insp in Model.InstallSplitSet  
                       join qwf in Model.QWorkflowSet on insp.SplitID equals qwf.SplitID         
                       where qWorkIDs.Contains(qwf.QWorkID)  
                       select insp;  
   foreach (var item in items)  
   {  
      item.Locked = IsLocked;  
      item.ModifiedBy = ModifiedBy;  
      item.ModifiedDt = DateTime.Today;  
   };          
   Model.SaveChanges();  

This would then take 2 DB calls: 1 to get data and 1 to save the changes. But you'd need to test the behavior for your dataset.

Finally note that making a single DB call to save changes means either all items are updated or they all fail. With the original version you had it was possible for some items to save and but other items to fail. So this is a behavior change that you may or may not want.

Answer 2

Instead of for each, you can create a view model and structure the request based on your database model. It might give some speed to processing the data

you class should be like below one

class ViewMode()  
{  
   
 public int prop{ get; set;}  
  
 public List<obj>  listOject { get; set;}  
}  

While assigning the values to listObjects, you can order the data or compare the data based on your requirement