Update speed in MS Access

  • Thread starter Thread starter microsoft
  • Start date Start date
M

microsoft

We are writing data into a MS Access Table, the data has come from an XML
data source, we have loaded this into a DOM object and then parsing each
value and writing into the table. there are about 15 fields in a record. To
write about 200 records it takes only 1 second but to write about 10000
records it takes about 8-10 mins why is this degradation in speed. Is there
a better way to do this that would increase the speed.
 
It may have something to do with the indexes on your
destination table, and/or the fragmentation of your hard
drive.
If you have many indexes, access has to re-order them
after every insert/update/delete. This obviously takes
longer as the table gets bigger. One trick is to remove
the indexes before a bulk insert and then re-apply them
afterwards. You do need to be careful if, for example, you
are relying on an index to prevent the addition of
duplicates.
 
Back
Top