Performance Updating a DataSet

[booksnore]

Hi,
I have a program which performs some calculations and then updates a
table. I tested my program on a few thousand rows, worked good, no
issues, update worked. When I test on larger data - up into the millions
of rows the calculation part is fine and fairly quick but the table
update is ver, very slow (we're talking hours here). I am using a data
adaptor to update a dataset and so table on SQL Server. The code for the
update is this..

da.Update(ds,tableName);

The table on SQL server is not that wide - about 20 columns with a mix
of int and varchar fields. Does anyone know of specific things I can try
to improve the performance of this step?
I read a blog where it mentioned an option to disable reload of data
after an insert or update (see below) but it does not mention where the
option is - I posted a note to the blog
http://blogs.ittoolbox.com/visualbasic/dotnet/archives/005443.asp
Any help much appreciated - getting desperate here...

Joe

 

[Nicholas Paldino [.NET/C# MVP]]

Joe,

You need to set this on the command (the update, delete and insert
command). See the UpdatedRowSource property on the command to
insert/update/delete. Set it to UpdateRowSource.None.

Also, if you are using .NET 2.0, you might want to take advantage of
batching as well.

Hope this helps.

 

[Richard Blewett [DevelopMentor]]

Hi,
I have a program which performs some calculations and then updates a
table. I tested my program on a few thousand rows, worked good, no
issues, update worked. When I test on larger data - up into the millions
of rows the calculation part is fine and fairly quick but the table
update is ver, very slow (we're talking hours here). I am using a data
adaptor to update a dataset and so table on SQL Server. The code for the
update is this..

da.Update(ds,tableName);

The table on SQL server is not that wide - about 20 columns with a mix
of int and varchar fields. Does anyone know of specific things I can try
to improve the performance of this step?
I read a blog where it mentioned an option to disable reload of data
after an insert or update (see below) but it does not mention where the
option is - I posted a note to the blog
http://blogs.ittoolbox.com/visualbasic/dotnet/archives/005443.asp
Any help much appreciated - getting desperate here...

Joe

If you're using v1.1 it will perform a roundtrip to the database for every
updated row. This I assume is where your problem lies. Your two alternatives
are:

1) create batched updates on the cleint
2) send the changes to the DB in one go as an XML file and get it to break
it apart and make the changes

Which you choose depends on the load on your db server

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

 

[booksnore]

Hi thanks for the replies. On the method that Richard outlined ,
2) send the changes to the DB in one go as an XML file and get it to
break it apart and make the changes

are there any good online examples that outline the steps? I would like
to test this method however I am unsure of how to proceed.
Thank you
Joe

 

[Richard Blewett [DevelopMentor]]

Hi thanks for the replies. On the method that Richard outlined ,
2) send the changes to the DB in one go as an XML file and get it to
break it apart and make the changes

are there any good online examples that outline the steps? I would like
to test this method however I am unsure of how to proceed.
Thank you
Joe

if you are using SQL Server 2000 you can use OpenXML. This gives a
reasonable starting point

http://www.codeproject.com/database/openxml.asp

SQL Server 2005 has a whole slew of new XML features like an XML datatype
and XQuery

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

 

[booksnore]

Thank you for the information. Using the updating with an xml document
technique the performance on updates of the table improved from about 15
updates per second which I had using a stored procedure to around 1000
updates per second. This has saved my bacon! Thank you again for your
knowledge.

Joe