ADO Row Insert slows radically??? (from 0.014 s/row to 22.872 s/row)

[Dave Zook]

I am running a VBScript script to unload a small Event file to a MS Access
97 (v. 3.0) table. The table is empty when run starts, and is only table in
database.

The first 1800 rows insert at a rate of 0.014 seconds per row, measured
every 100 rows. Database is only 300-400 KB at that point.
The next 600 rows take 4.884, 8.697, 12.267, 15.828, 19.370, and 22.872
seconds per row respectively.

When the job is finished, it has read over one billion bytes while updating
a database of about 600 KB.

The MS Access 97 table is sorted on a timestamp in descending order, the
same order as the data being added.

There are a few thousand page faults; disk I/O is low, judging from disk I/O
light on computer.

My first thought was that a missing COMMIT was using up all available
memory. However, 128 MB should be enough to hold the entire database without
problems. And, running standalone on a dedicated computer, I assume Jet
commits frequently, if not on every row.

Any suggestions (or pointers to a beginner's guide to getting at the
counters in the Jet engine) will be appreciated.

 

[david epsom dot com dot au]

Suggestions:

1) Drop all indexes before the insert, and re-index after the insert
is complete.

2) Drop all connections every 1000 rows, to let the background
processes catch up with the foreground processes.

3) Make the timestamp the primary key.

4) Use DAO instead of ADO

5) Use the Idle method of the dbEngine object.

6) Increase the size of the Jet data cache.

(david)