Best Way to Update a Database with Multiple Records

[countrdd]

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.

 

[cjard]

What is the database system in use?

 

[countrdd]

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]

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]

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