AcceptChanges() is very slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear helpers,
I have a ADO.NET table which has about 4 columns and less than 5000 rows.

table.Fill(...)
// modify table

DataTable changedTable = table.GetChanges();
// if there are any changes, update the database
if (changedTable != null)
{
rowsAffected = adapter.Update(changedTable);
if (rowsAffected == changedTable.Rows.Count)
{
table.AcceptChanges();
table.Merge(changedTable);
}
}

when the code is called, it took ~ 20 secs to finish, which is unacceptably
slow.
And stepping through it i found that it was due to AcceptChanges().
I am using .NET 2.0.
Can anyone help me on this or suggest workarounds?
Thanks very much!
 
Weishng,

Why you do it this way, not supporting the whole table, AFAIK is the
dataadapter only handling the rows with changes (and does than the
acceptchanges in that what I know for sure)..

I am curious about your result, so that I am able to leave that AFAIK from
my text.

Cor
 
Have you tried

//table.AcceptChanges();
table.Merge(changedTable, false); //Do not preserve changes in the source
table.

I THINK that since the adapter.Update method calls accept changes on the
rows in changedTable, the datarowstate of all rows in the source table after
this call should be Unchanged.
 
JT said:
Have you tried

//table.AcceptChanges();
table.Merge(changedTable, false); //Do not preserve changes in the source
table.

I THINK that since the adapter.Update method calls accept changes on the
rows in changedTable, t
Click to expand...

True.

he datarowstate of all rows in the source table after
this call should be Unchanged.
Click to expand...

No, not true. GetChanges creates a *copy* of changed rows. It that would be
true than you won't need Merge, right.
 
Seems strange to me that AcceptChanges is taking that much time.
Are you sure?
Do you have any events attached to table?
Did you try stopwatching it?
Did you use a performance profiler?
BTW, your code seems correct.
 
Miha, of course you are right that the adapter is accepting changes on the
copy in changedTable. But, the point of my post was to make use of the merge
overload that does NOT preserve changes in the source table, thus essentially
replacing the source table rows with the changeTable rows, which will have a
rowstate of unchanged.

Another suggestion - if your table is part of a dataset. You can set
EnforceConstraints to False before your update and merge call, then reset
when everything is done.
 
Miha,

In fact he is doing two times acceptchanges. In past I had always the idea
the code as the OP is using now is correct. I am very much in doubt at the
moment about that.

Have a look at my reply.

Cor

Miha Markic said:
Seems strange to me that AcceptChanges is taking that much time.
Are you sure?
Do you have any events attached to table?
Did you try stopwatching it?
Did you use a performance profiler?
BTW, your code seems correct.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

weishng said:
Dear helpers,
I have a ADO.NET table which has about 4 columns and less than 5000 rows.

table.Fill(...)
// modify table

DataTable changedTable = table.GetChanges();
// if there are any changes, update the database
if (changedTable != null)
{
rowsAffected = adapter.Update(changedTable);
if (rowsAffected == changedTable.Rows.Count)
{
table.AcceptChanges();
table.Merge(changedTable);
}
}

when the code is called, it took ~ 20 secs to finish, which is
unacceptably
slow.
And stepping through it i found that it was due to AcceptChanges().
I am using .NET 2.0.
Can anyone help me on this or suggest workarounds?
Thanks very much!
Click to expand...
Click to expand...
 
I saw your reply but didn't understood it fully.
Are you wondering why he is using Update on changed rows instead on all
rows?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
Miha,

In fact he is doing two times acceptchanges. In past I had always the idea
the code as the OP is using now is correct. I am very much in doubt at the
moment about that.

Have a look at my reply.

Cor

Miha Markic said:
Seems strange to me that AcceptChanges is taking that much time.
Are you sure?
Do you have any events attached to table?
Did you try stopwatching it?
Did you use a performance profiler?
BTW, your code seems correct.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

weishng said:
Dear helpers,
I have a ADO.NET table which has about 4 columns and less than 5000
rows.

table.Fill(...)
// modify table

DataTable changedTable = table.GetChanges();
// if there are any changes, update the database
if (changedTable != null)
{
rowsAffected = adapter.Update(changedTable);
if (rowsAffected == changedTable.Rows.Count)
{
table.AcceptChanges();
table.Merge(changedTable);
}
}

when the code is called, it took ~ 20 secs to finish, which is
unacceptably
slow.
And stepping through it i found that it was due to AcceptChanges().
I am using .NET 2.0.
Can anyone help me on this or suggest workarounds?
Thanks very much!
Click to expand...
Click to expand...
Click to expand...
 
Not why but that it is probably better not to use the copy but direct the
table.

Cor

Miha Markic said:
I saw your reply but didn't understood it fully.
Are you wondering why he is using Update on changed rows instead on all
rows?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
Miha,

In fact he is doing two times acceptchanges. In past I had always the
idea the code as the OP is using now is correct. I am very much in doubt
at the moment about that.

Have a look at my reply.

Cor

Miha Markic said:
Seems strange to me that AcceptChanges is taking that much time.
Are you sure?
Do you have any events attached to table?
Did you try stopwatching it?
Did you use a performance profiler?
BTW, your code seems correct.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Dear helpers,
I have a ADO.NET table which has about 4 columns and less than 5000
rows.

table.Fill(...)
// modify table

DataTable changedTable = table.GetChanges();
// if there are any changes, update the database
if (changedTable != null)
{
rowsAffected = adapter.Update(changedTable);
if (rowsAffected == changedTable.Rows.Count)
{
table.AcceptChanges();
table.Merge(changedTable);
}
}

when the code is called, it took ~ 20 secs to finish, which is
unacceptably
slow.
And stepping through it i found that it was due to AcceptChanges().
I am using .NET 2.0.
Can anyone help me on this or suggest workarounds?
Thanks very much!
Click to expand...
Click to expand...
Click to expand...
 
Cor Ligthert said:
Not why but that it is probably better not to use the copy but direct the
table.
Click to expand...

So how would you rollback changes if there was an error during Update?
 
Miha,


What is the problem in that, (I prefer to use the rowerrorstate instead of a
direct error action).

Cor
 
Ya, thanks very much everyone!
I had hooked RowChanged event on the table. Event though the handler does
nothing most of the time (the condition fails immediately), unhooking it
gives me back the speed.


Miha Markic said:
Seems strange to me that AcceptChanges is taking that much time.
Are you sure?
Do you have any events attached to table?
Did you try stopwatching it?
Did you use a performance profiler?
BTW, your code seems correct.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

weishng said:
Dear helpers,
I have a ADO.NET table which has about 4 columns and less than 5000 rows.

table.Fill(...)
// modify table

DataTable changedTable = table.GetChanges();
// if there are any changes, update the database
if (changedTable != null)
{
rowsAffected = adapter.Update(changedTable);
if (rowsAffected == changedTable.Rows.Count)
{
table.AcceptChanges();
table.Merge(changedTable);
}
}

when the code is called, it took ~ 20 secs to finish, which is
unacceptably
slow.
And stepping through it i found that it was due to AcceptChanges().
I am using .NET 2.0.
Can anyone help me on this or suggest workarounds?
Thanks very much!
Click to expand...
Click to expand...
 
Back
Top