which way is the best to insert massive records into a table once?

[tezheng]

i'm using Access table with ADO via C++
and it takes more than 30 seconds to append 5000 records once in batchupdate
mode.

why is it so slow?
is there any better way?

the table contains 8 fields----3 int and 5 double, a primary key(ID)
and the data are all in memory(dynamic created)

 

[John Vinson]

i'm using Access table with ADO via C++
and it takes more than 30 seconds to append 5000 records once in batchupdate
mode.

why is it so slow?
is there any better way?

the table contains 8 fields----3 int and 5 double, a primary key(ID)
and the data are all in memory(dynamic created)

It's slow because it's got to do quite a bit of work: creating the
records, updating the indexes, writing both to disk. You might be able
to save some time by opening a connection outside of this update
process and leave it open, there's a lot of overhead in just
establishing the connection - but if that is just part of a unitary
process, it's just going to move the delay.

John W. Vinson[MVP]

 

[Guest]

Slow? Try typing that many records in manually! ;-)

You could try throwing hardware at the problem. A faster server / PC
especially with faster hard drives as there's a lot of writing going on. Even
though the records are created in memory, you could actually be using hard
drive swap space if there isn't enough real memory.

Also for testing temporarily disable your virus checker. I have a rather
complicated report that takes many minutes to run normally. With the BE on my
PC, I can disconnect the network connection and disable the virus checker. It
only takes seconds then. I have excuded .md? files from scanning yet Norton
or something with the network still slows things down.