Best Way to Update a Database with Multiple Records

countrdd

New member
Joined
Sep 2, 2006
Messages
2
Programming Experience
Beginner
All.

It has been a while since I programmed in VB, I have a program that needs to process and input file.
I am reading the input text file fine, and getting the database updated but it is extremely slow.

I was using tableadapters and bindingsources to update the data, but it takes about 30-40 minutes process the file. What is the best way to update a database in .net?
This is a catalog file (they send the whole catalog each time not just updates, and is about 22000 items) and I need to look at the new csv file and then either update or add the record to the database.

I have the import process down...it is just working really slow. The database is on the local drive but will be moved to a network server during deployment.

Any help/advice would be greatly appreciated.
 

countrdd

New member
Joined
Sep 2, 2006
Messages
2
Programming Experience
Beginner
I am currently using access. I think my problem is that I am using a dataset. I am confused on how to write directly to the database. I also would like to be able to use the same code to write to either access or sqlserver and have that selected at runtime, is that even possible?
 

Administrator

VB.NET Forum Admin
Joined
Jun 3, 2004
Messages
1,461
Programming Experience
10+
You first need to look at what you are updating, i.e. are you only updating what needs to be updated, or are you updating everything? Secondly, your update statements and use of timestamps (not sure about Access), and lastly, a not so well known feature in .NET 2.0 is setting the UpdateBatch (or UpdateBatchSize, whatever the property name is) property of the DataAdapter. Normally updates are done one row at a time, using the UpdateBatch setting a size of 10 for example will significantly improve update time.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
please provide the code you currently use to update the database. include the schema (including indexes) of any tables you use on the database back end
 
Top Bottom