Efficient way for updating 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 has around 500K records in it with 39 columns and is present in Cache. After a minute a background thread hits the database and brings update records from database, which I want to update in the Cached data table, I am using following approach but it is taking good enough time to do it:

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 have 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 is taking like around 5 minutes on my laptop.

Can anyone please guide my where and what am I doing wrong? With which approach can I do it efficiently, I am not sure if Dataset.Merge or some other approach can be used.

SCNR ... `[...] data table [...] 500K records in it with 39 columns and is present in Cache` made me laugh hard immediately... data table + cache + foreach + multithreading + ... **bloody awesome**! you must be chuck norris ... — , Jul 18 '12 at 12:44
:) Might be its funny for you but in my case it is like so, so lets laugh hard together :D — Imran Balouch, Jul 18 '12 at 12:47
actually I do laugh because the env you are hunting down _really_ hurts ... within this scenario there ain't a performant/practicable solution. i would rather start with a basic question: why do you have to store the values somewhere? what are you doing? give us a bigger picture! ... — , Jul 18 '12 at 12:49
If I put a 1GB RAM on server only for this Caching thing, i don't think so that its going to hurt. So what do you suggest to replace this damn data table with? Querying database is not an option for me. — Imran Balouch, Jul 18 '12 at 12:53
you misunderstood _hurt_: it's like watching a guy, with hardly any knowledge on DST, UTC, ..., implementing a very clever-clever system for such tasks. someone should jump right in and say "no" - that's what i've tried to achieve with my questions (which you haven't answered yet). i can not (and do not want to) give any advices yet - first of all i need answers! (one true option would be: why not use a static `Dictionary`, where `TValue` might be your PK-type and `TValue` the other 38 columns). btw: if you think adding RAM would help, go for it! — , Jul 18 '12 at 13:00
Sorry for misunderstanding your point. Well the story is that on a page in our application, we are showing a dojo grid and 2 charts, the data for the grid was being retrieved from database, but there were JOINS of like 25 tables involved in it and Stored procedure was taking time like 12-15 secs, so we came across a solution to put the data in Cache through background thread and than present it to user after getting it from Cache. This solution is working fine except the above problem. I hope I answered you question. — Imran Balouch, Jul 18 '12 at 13:26
`so we came across a solution to put the data in Cache through background thread and than present it to user after getting it from Cache.` you might not know it, but this is a simple repository-domain-pattern (repo-pattern: http://blog.lowendahl.net/?p=249 + singleton data-container) only realized with the limper aka data-table. I further assume that you are polling the data every minute and want to merge newly created or updated rows to the cache-instance - am I right? — , Jul 18 '12 at 13:33
Thanks for this new knowledge for me, I am going to go through this blog, Yeh you are right, after every minute we are fetching new or updated rows. — Imran Balouch, Jul 18 '12 at 13:46
it would be better if you centralize your data-access: read **AND** write should be done over a repo, so the polling becomes obsolete. after this you are all set, can use freaky funky linq-queries, ... :) — , Jul 18 '12 at 13:48

HatSoft · Accepted Answer · 2012-07-18T14:10:08.013

1

You can try it this way

dtOriginal.Merge(dtNew);

edited Jul 18 '12 at 14:10

answered Jul 18 '12 at 12:46

HatSoft

11,077
3
28
43

why take a copy rather than just dtOriginal.Merge(dtNew)? – M Afifi Jul 18 '12 at 14:02
@MAfifi sorry I just gave quick example that you achieve it by these methods already available, please use in the order whats suitable for you code – HatSoft Jul 18 '12 at 14:09
Thanks a lot for this answer, the only problem I faced was, Merge statement was adding new rows in the table, which I figured out that If I put a Primary Key in my data table, it will solve the problem. – Imran Balouch Jul 18 '12 at 15:06
Can you do a .Merge if one of the tables has no primary key? – East of Nowhere Mar 05 '13 at 18:18
@Imran Balouch You need to specify the primary key of the table before merging in order to achieve the required results – Cogent Sep 05 '16 at 08:53

M Afifi · Answer 2 · 2012-07-18T13:17:59.933

1

Try this method, DataRowCollection.Find. Assuming you're DataTable is set correctly, it will be O(log(n)) rather than O(N) which it currently is.

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"]; 
     }
}

edited Jul 18 '12 at 13:17

answered Jul 18 '12 at 13:12

M Afifi

4,645
2
28
48

please ... do not forget about the outer `foreach`. maybe rather a join in a dataset? – Jul 18 '12 at 13:19
That's essentially what Merge does. – M Afifi Jul 18 '12 at 14:01
yes and no ... there should get only 3 out of 39 columns updated - so essentially not a _real_ merge – Jul 18 '12 at 14:09

score 1 · Answer 3 · edited May 21 '14 at 00:52

I would have thought that using this would be much quicker:

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());
}
);

HI Naelem,Can you please tell me how to give two condition in this LINQ..exactly in above code ,how to add another common field? — JOJO, Dec 31 '15 at 04:04

Efficient way for updating data in a data table from another data table

3 Answers3