|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
AcceptChanges() is very slowI 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 Show quote "weishng" <weis***@discussions.microsoft.com> schreef in bericht news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... > 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! 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. -- Show quoteJohn "weishng" wrote: > 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! "JT" <Jthayer@online.nospam> wrote in message True.news:CAEAB586-0BA8-4134-9EF2-88F860759828@microsoft.com... > 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 he datarowstate of all rows in the source table after > this call should be Unchanged. No, not true. GetChanges creates a *copy* of changed rows. It that would be true than you won't need Merge, right. -- 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/ 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. -- Show quoteJohn "Miha Markic [MVP C#]" wrote: > > "JT" <Jthayer@online.nospam> wrote in message > news:CAEAB586-0BA8-4134-9EF2-88F860759828@microsoft.com... > > 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 > > True. > > he datarowstate of all rows in the source table after > > this call should be Unchanged. > > No, not true. GetChanges creates a *copy* of changed rows. It that would be > true than you won't need Merge, right. > > -- > 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/ > > > 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. -- Show quoteMiha 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" <weis***@discussions.microsoft.com> wrote in message news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... > 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! 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 Show quote "Miha Markic [MVP C#]" <miha at rthand com> schreef in bericht news:%231SsD$p7GHA.4064@TK2MSFTNGP03.phx.gbl... > 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" <weis***@discussions.microsoft.com> wrote in message > news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... >> 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! > > 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? -- Show quoteMiha 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 [MVP]" <notmyfirstn***@planet.nl> wrote in message news:%2302EQ6u7GHA.4288@TK2MSFTNGP02.phx.gbl... > 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 [MVP C#]" <miha at rthand com> schreef in bericht > news:%231SsD$p7GHA.4064@TK2MSFTNGP03.phx.gbl... >> 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" <weis***@discussions.microsoft.com> wrote in message >> news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... >>> 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! >> >> > > Not why but that it is probably better not to use the copy but direct the
table. Cor Show quote "Miha Markic [MVP C#]" <miha at rthand com> schreef in bericht news:%23Kbvof17GHA.4408@TK2MSFTNGP02.phx.gbl... >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 [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:%2302EQ6u7GHA.4288@TK2MSFTNGP02.phx.gbl... >> 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 [MVP C#]" <miha at rthand com> schreef in bericht >> news:%231SsD$p7GHA.4064@TK2MSFTNGP03.phx.gbl... >>> 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" <weis***@discussions.microsoft.com> wrote in message >>> news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... >>>> 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! >>> >>> >> >> > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message So how would you rollback changes if there was an error during Update?news:e$LVxm47GHA.4288@TK2MSFTNGP02.phx.gbl... > Not why but that it is probably better not to use the copy but direct the > table. -- 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/ Miha,
What is the problem in that, (I prefer to use the rowerrorstate instead of a direct error action). Cor Show quote "Miha Markic [MVP C#]" <miha at rthand com> schreef in bericht news:eJK28A57GHA.3960@TK2MSFTNGP05.phx.gbl... > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:e$LVxm47GHA.4288@TK2MSFTNGP02.phx.gbl... >> Not why but that it is probably better not to use the copy but direct the >> table. > > So how would you rollback changes if there was an error during Update? > > -- > 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/ > 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. Show quote "Miha Markic [MVP C#]" wrote: > 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" <weis***@discussions.microsoft.com> wrote in message > news:AD78ED36-FA04-41E4-BA52-F910FD7CC58F@microsoft.com... > > 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! > > >  |
|||||||||||||||||||||||
Other interesting topics