vb.net - Slow MSAccess disk writing

Question

I'm trying to write a VB.Net program that saves 1-2 million 5-field records (plus an indexed ID) to an MSAccess table every day. The saving process currently takes 13-20 hours, which obviously can't be right.

Its a flat table with minimal indexing, currently only 156MB. Except for one double field, the fields are small strings, dates, or longs. The disk itself is a 15,000 SATA which is used only for this file. The computer and the program are not doing anything else during the save routine. The save routine is a simple FOR-NEXT loop that issues a short and simple INSERT statement for each record in the dataset.

Anyone got an ideas on what I need to change to get this to work better?

Answer 1

A trick that can work on any DBMS to substantially speed up an insertion is to disable temporarily the indexes, foreign keys and constraints prior to bulk inserting the data - then enable them again after your data in the database.

Especially indexes can be performance-killers for sequential insertion, it's faster by at least an order (sometimes 2!) of magnitude to fill a table first and then create the index on the already filled data than to insert with the index in place. In this case you might need to drop the index, then recreate it.

Then, as most other posters have already said, it's really a waste of time to insert stuff a row at a time if you can do it in bunches. You'll get a minor speed improvement if you open the table with no locking at all or only optimistic locking.

And then you might get another tiny increment by using DAO recordsets instead of ADO - I noticed this back in the days when I developed in VB6, probably this is not the case anymore with ADO.NET