How to improve slow query - Hardware perspective

[GPO]

Env: Office 2003, Win XP, relevant service packs.
I am regularly required to append about two million rows of data (20 cols)
to a table with several indexes Obviously it is going to take a long time
run. What I would like to clarify though is this: When I look at the
Performance tab in the Task Manager, the CPU usage history shows that
activity spikes for about 1 second or less in every 10 and then goes back
down near zero. What is likely to be happening when the CPU is not active? It
seems to me, that there is a bottleneck in moving data around. If this is the
case how can I identify it, and what can I do to address it? I have 1 gb of
ram and plenty of space on the drive (IDE slave) in question (it's half
full).

FWIW The data being appended is coming from a tab delimited text file
(270543k) in the form of a linked table and it is a straight SQL INSERT -
there are no transformations on the data coming it. The text file is on the
same drive as the receiving table, but in a different path.

Regards

GPO

 

[Douglas J. Steele]

I'm just guessing, but one possibility is that Access needs to acquire a new
page at those points. Access stores records in 2K pages (or is it 4K with
Access 2003?). This is why you may have noticed that inserting a few records
may not increase the size of the MDB, but inserting one more record may make
its size jump.

 

[GPO]

Does this mean it is taking 10 seconds just to retrieve a 2k page?
Interestingly I tried this with a cut down text extract of 50,000 records
and the CPU ran at 100% for the entire 30 seconds it took to process. Seems
much more efficient. Maybe I need to break my text file up into smaller
files?...