Updating Rows for a large DataTable

[pittsim]

Hi,

I need to find a way to update some 400,000 rows of an in-memory datatable. Looping through rows is very slow.
here's the code I'm using:

protected virtual void SetEditStamps(ref DataSet ds)
        {
            int count = 0;
            if ( ds != null )
            {
                foreach (DataTable dt in ds.Tables)
                {
                    if ( dt.Columns.Contains(_dcnameEditId) && dt.Columns.Contains(_dcnameEditDate) )
                    {
//                        foreach (DataRow dr in dt.Select(null, null, DataViewRowState.ModifiedCurrent | DataViewRowState.Added))
                        string sEditID = BusinessLogicComponent._dcnameEditId;
                        string sEditDate = BusinessLogicComponent._dcnameEditDate;
                        int iEditID = dt.Columns[sEditID].Ordinal;
                        int iEditDate = dt.Columns[sEditDate].Ordinal;
                        for (int i = 0; i<dt.Rows.Count; i++)
                        {
//                            SetRowLevelEditStamps(dt.Rows[i]);
                            dt.Rows[i][iEditID]    = _lastEditBy;
                            dt.Rows[i][iEditDate]    = _lastEditOn;
                            count++;
                        }
                    }
                }
            }
        }
 

Is there a faster way to do this?

Thanks,
Simran

[nateraaaa]

protected virtual void SetEditStamps(ref DataSet ds)
{
int count = 0;
if ( ds != null )
{
foreach (DataTable dt in ds.Tables)
{
if ( dt.Columns.Contains(_dcnameEditId) && dt.Columns.Contains(_dcnameEditDate) )
{
// foreach (DataRow dr in dt.Select(null, null, DataViewRowState.ModifiedCurrent | DataViewRowState.Added))
string sEditID = BusinessLogicComponent._dcnameEditId;
string sEditDate = BusinessLogicComponent._dcnameEditDate;
int iEditID = dt.Columns[sEditID].Ordinal;
int iEditDate = dt.Columns[sEditDate].Ordinal;
for (int i = 0; i<dt.Rows.Count; i++)
{
// SetRowLevelEditStamps(dt.Rows[i]);
dt.Rows[i][iEditID] = _lastEditBy;
dt.Rows[i][iEditDate] = _lastEditOn;
count++;
}
}
}
}
}

Have you considered passing a DataTable as a parameter to this method instead of the entire dataset? You could do your logic to identify you have the correct dataset table before calling this method then just pass the correct datatable to this method as a parameter. In the method you would then have only 1 foreach loop instead of 2. This should reduce the process time for this method. Give this a try and let us know if you run into any problems.

Nathan

[Plater]

Here's what I reduced it to:

protected virtual void SetEditStamps(ref DataSet ds)
{
    //int count = 0;//count total from all tables? 
 
    //you only need to grab the string name once, not every time
    string sEditID = BusinessLogicComponent._dcnameEditId;
    string sEditDate = BusinessLogicComponent._dcnameEditDate;
    if (ds != null)
    {
      foreach (DataTable dt in ds.Tables)
      {
        if (dt.Columns.Contains(sEditID) && dt.Columns.Contains(sEditDate))
        {
          for (int i = 0; i < dt.Rows.Count; i++)
          {
            //SetRowLevelEditStamps(dt.Rows[i]);
 
            //you can refer to columns by string name
            dt.Rows[i][sEditID] = _lastEditBy;
            dt.Rows[i][sEditDate] = _lastEditOn;
            //count++;//you don't use it?
          }
        }
      }
    }
}
 

[pittsim]

Have you considered passing a DataTable as a parameter to this method instead of the entire dataset? You could do your logic to identify you have the correct dataset table before calling this method then just pass the correct datatable to this method as a parameter. In the method you would then have only 1 foreach loop instead of 2. This should reduce the process time for this method. Give this a try and let us know if you run into any problems.

Nathan

Nathan,

Thanks for your reply. I tried it, but it didnt make too much of a difference. There are only about 4 tables in the dataset and so it doesnt make for a very big loop. Plus, its just looping through the rows of the one datatable thats very slow. The 'count' variable is there just so I can see how many rows it has churned through in a set period of time. Currently its taking around 15 secs to go through about 60 rows which you can see is painfully slow. But here's something funny I noticed. Once in a while...just once in like 10-15 tries, it'll just go through the entire table in a matter of seconds! I'm still trying to figure out why that's happening and more importantly, how I can get that to happen everytime.
Any other suggestions would be very welcome!

Thanks again,
Simran

[pittsim]

Here's what I reduced it to:

protected virtual void SetEditStamps(ref DataSet ds)
{
    //int count = 0;//count total from all tables? 
 
    //you only need to grab the string name once, not every time
    string sEditID = BusinessLogicComponent._dcnameEditId;
    string sEditDate = BusinessLogicComponent._dcnameEditDate;
    if (ds != null)
    {
      foreach (DataTable dt in ds.Tables)
      {
        if (dt.Columns.Contains(sEditID) && dt.Columns.Contains(sEditDate))
        {
          for (int i = 0; i < dt.Rows.Count; i++)
          {
            //SetRowLevelEditStamps(dt.Rows[i]);
 
            //you can refer to columns by string name
            dt.Rows[i][sEditID] = _lastEditBy;
            dt.Rows[i][sEditDate] = _lastEditOn;
            //count++;//you don't use it?
          }
        }
      }
    }
}
 

Plater,

The reason I was using ordinals to access columns because I was hoping it would make the row access faster. It didn't..
And as I explained in my other reply, the count is there just so I can see how many rows it has looped through in a set period of time.

Thanks,
Simran

[Plater]

Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.

[pittsim]

Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.

Its taking like 15 secs for going through about 60-70 rows...

Thanks,
Simran

[pittsim]

Just how long are we talking here? I mean 400,000 rows is going to take some time to sift through, regardless of speed.

Plater,

Also see my reply to Nathan above. I have noticed that once in a while, apparently randomly, it'll go through the entire 400,000 rows in a matter of seconds. I'm still trying to figure out why. I'm wondering if it is something to do with rebuilding references, or something else...but I want to find a way to recreate that.

Thanks,
Simran

[Plater]

Its taking like 15 secs for going through about 60-70 rows...

Thanks,
Simran

Oh wow, I would have thought it could do the whole thing in that amount of time.
What is done in:
SetRowLevelEditStamps
maybe that is eating up time?

[SpecialKay]

400,000 rows is really not that much. I would think that it should not take more then 30-60 second to process that. Try debugging, maybe you can see where the slow down is.

[pittsim]

Oh wow, I would have thought it could do the whole thing in that amount of time.
What is done in:
SetRowLevelEditStamps
maybe that is eating up time?

Plater,

The the SetRowLevelEditStamps method call has been commented out. All that was being done in there is now being done in the loop itself with the updating of the rows.

Thanks,
Simran

[pittsim]

400,000 rows is really not that much. I would think that it should not take more then 30-60 second to process that. Try debugging, maybe you can see where the slow down is.

Kay,

I have tried debugging :) of course! But do you have any suggestions as to where to look for potential problems?

thanks,
Simran

[SpecialKay]

Well, looking at the code you provided, it all seems very basic, nothing i can see would cause a slow down. So the problem is going to be in the BusinessLogicComponent class. Its going to be hard to narrow down. Wish i could be more help.

[pittsim]

Well, looking at the code you provided, it all seems very basic, nothing i can see would cause a slow down. So the problem is going to be in the BusinessLogicComponent class. Its going to be hard to narrow down. Wish i could be more help.

Everyone,

Thanks very much for your help. I think the problem lies somewhere in .Nets memory management and I'll keep on digging into it.

Simran