Update Large DataTable
-
I have a large DataTable (67,000 records) and I need to perform an operation on just one column. As an example, if I wanted to set all characters to upper case, I would consider using something like:
foreach (System.Data.DataRow row in myDataTable.Rows) { string lower = row[ColumnNumber].ToString(); row[ColumnNumber] = lower.ToUpper(); }Having tested the above code, it takes approx 60mins to update the entire data table on a 1.3ghz PC with 256mb ram. Is there a more efficient method? Regards Wayne Phipps ____________ Time is the greatest teacher... unfortunately, it kills all of its students LearnVisualStudio.Net -
I have a large DataTable (67,000 records) and I need to perform an operation on just one column. As an example, if I wanted to set all characters to upper case, I would consider using something like:
foreach (System.Data.DataRow row in myDataTable.Rows) { string lower = row[ColumnNumber].ToString(); row[ColumnNumber] = lower.ToUpper(); }Having tested the above code, it takes approx 60mins to update the entire data table on a 1.3ghz PC with 256mb ram. Is there a more efficient method? Regards Wayne Phipps ____________ Time is the greatest teacher... unfortunately, it kills all of its students LearnVisualStudio.NetCouple things: Although I've never really tested it, I've heard anecdotally that
foreachis not a very efficient way to do things. You can tryfor(int i=0;i < myDataTable.Rows.Count;i++)and then refering tomyDataTable.Rows[i]inside of the loop. More significantly: I think you should reconsider pulling 67000 rows into your client application. If you need to change that large a chunk of data, doing it in SQL on the database server will likely be faster. Just my $0.02...Hope it helps, Bill -
Couple things: Although I've never really tested it, I've heard anecdotally that
foreachis not a very efficient way to do things. You can tryfor(int i=0;i < myDataTable.Rows.Count;i++)and then refering tomyDataTable.Rows[i]inside of the loop. More significantly: I think you should reconsider pulling 67000 rows into your client application. If you need to change that large a chunk of data, doing it in SQL on the database server will likely be faster. Just my $0.02...Hope it helps, BillThanks for your reply. I'll give it a try. Unfortunately I don't have much choice over pulling all that data in because it's been exported from a legacy system that used its own format for data storage. Basically we've got to pull it in before we can push it into something else more usefull. Weve developed this application because no SQL server or MS Access is available on the client PCs where data has to be manipulated. Its a case of "If it takes an hour, it takes an hour" but it would be great to reduce this as much as possible. I'm willing to try anything anyone can think of. Regards Wayne Phipps ____________ Time is the greatest teacher... unfortunately, it kills all of its students LearnVisualStudio.Net
-
I have a large DataTable (67,000 records) and I need to perform an operation on just one column. As an example, if I wanted to set all characters to upper case, I would consider using something like:
foreach (System.Data.DataRow row in myDataTable.Rows) { string lower = row[ColumnNumber].ToString(); row[ColumnNumber] = lower.ToUpper(); }Having tested the above code, it takes approx 60mins to update the entire data table on a 1.3ghz PC with 256mb ram. Is there a more efficient method? Regards Wayne Phipps ____________ Time is the greatest teacher... unfortunately, it kills all of its students LearnVisualStudio.NetThere are several things you could do. The DataTable is a damn heavy object which is relatively slow compared to other data structures. This is the price you pay for the complexity of the class. So your first thought should be about another datacontainer. If you have relationships to other tables, a primary key set or active views on the datatable: Remove them, make your operation and readd. This way many checks while you update your data will be left out. Also try to call BeginLoadData() and EndLoadData(). I dont know if it increses performance in updates (as it does while adding) but its worth a try.