CODEWITHSUNDEEP
c#performancedatatabledatarow
Amit
Amit

Reputation: 1184

Performance of setting DataRow values in a large DataTable

I have a large DataTable - around 15000 rows and 100 columns - and I need to set the values for some of the columns in every row.

// Creating the DataTable
DataTable dt = new DataTable();
for (int i = 0; i < COLS_NUM; i++)
{
    dt.Columns.Add("COL" + i);
}    
for (int i = 0; i < ROWS_NUM; i++)
{
    dt.Rows.Add(dt.NewRow());
}

// Setting several values in every row
Stopwatch sw2 = new Stopwatch();
sw2.Start();
foreach (DataRow row in dt.Rows)
{
    for (int j = 0; j < 15; j++)
    {
        row["Col" + j] = 5;
    }
}
sw2.Stop();

The measured time above is about 4.5 seconds. Is there any simple way to improve this?

Upvotes: 1

Views: 7022

Answers (3)

user3308241
user3308241

Reputation: 364

Before you populate the data, call the BeginLoadData() method on the DataTable. When you have finished loading the data, call EndLoadData(). This turns off all notifications, index maintenance, and constraints, which will improve performance.

As an alternative, call BeginEdit() before updating each row, and EndEdit() when the editing for that row is complete.

Here is a link with more information on improving DataSet performance: http://www.softwire.com/blog/2011/08/04/dataset-performance-in-net-web-applications/

Upvotes: 2

Alaa Alweish
Alaa Alweish

Reputation: 9084

this depends on your business logic which is not clear in your question, however, If you want to set the values for some of the columns in every row, try the following,

  • Create a separated temp column(s), you might create it in the same loop when creating the original data table

  • Fill the new values into this column,

  • delete the old column and insert the new one in its place instead.

This solution will be logical if you can expect the new values or if you have the same value for all rows (like in your example) or if you have some kind of repeat, in that case adding a new column with loaded will be much more faster than looping all rows.

Upvotes: 0

hattenn
hattenn

Reputation: 4399

One improvement that I can think of is editing columns by their indices, rather than their names.

foreach (DataRow row in dt.Rows)
{
    for (int j = 0; j < 15; j++)
    {
        row[j] = 5;
    }
}

With an empirical test, your method seems to run in ~1500 milliseconds on my computer, and this index based version runs in ~1100 milliseconds.

Also, see Marc's answer in this post:

Set value for all rows in a datatable without for loop

Upvotes: 1

Related Questions