Efficient way to update data in a data table from another data table

Question

I have a data table in my ASP.NET web service project, this data table contains about 500 thousand records in it with 39 columns and is present in the cache. After a minute, the background thread enters the database and displays update records from the database that I want to update in the Cached data table, I use the following approach, but there is enough time for this:

foreach (DataRow dRNew in dtNew.Rows) { DataRow row = dtOriginal.Select("ID=" + dRNew["ID"]).First(); row["Column1"] = dRNew["Column1"]; row["Column2"] = dRNew["Column2"]; row["Column3"] = dRNew["Column3"]; } 

I replaced the following line:

 DataRow row = dtOriginal.Select("ID=" + dRNew["ID"]).First(); 

with

 DataRow row = dtOriginal.AsEnumerable().Where(r => ((Int64)r["ID"]).Equals(dRNew["ID"])).First(); 

but in vain, it takes about 5 minutes on my laptop.

Can someone point me where and what am I doing wrong? With which approach I can do this efficiently, I'm not sure if Dataset.Merge or any other approach can be used.

Answer 1

You can try it like this.

 dtOriginal.Merge(dtNew); 

Answer 2

I would think that using this would be much faster:

 TableToUpdate.AsEnumerable().Join ( TableToUpdateFrom.AsEnumerable(), lMaster => lMaster["COMMON_FIELD"], lChild => lChild["COMMON_FIELD"], (lMaster, lChild) => new { lMaster, lChild } ).ToList().ForEach ( o => { o.lMaster.SetField("FIELD_TO_BE_UPDATED1", o.lChild["FIELD_TO_BE_UPDATED_FROM1"].ToString()); o.lMaster.SetField("FIELD_TO_BE_UPDATED2", o.lChild["FIELD_TO_BE_UPDATED_FROM2"].ToString()); o.lMaster.SetField("FIELD_TO_BE_UPDATED3", o.lChild["FIELD_TO_BE_UPDATED_FROM3"].ToString()); o.lMaster.SetField("FIELD_TO_BE_UPDATED_ETC", o.lChild["APPROVAL_SCORE_FROM_ETC"].ToString()); } ); 

Answer 3

Try this method, DataRowCollection.Find . Assuming you have the DataTable configured correctly, it will be O (log (n)), not the O (N) that it currently has.

 foreach (DataRow dRNew in dtNew.Rows) { DataRow row = null; try { row = dtOriginal.Find(dRNew["ID"]); } catch (MissingPrimaryKeyException) { row = dtOriginal.Select("ID=" + dRNew["ID"]).First(); } if (row != null) { row["Column1"] = dRNew["Column1"]; row["Column2"] = dRNew["Column2"]; row["Column3"] = dRNew["Column3"]; } }