Why does Access 2000 slow down when inserting large groups of records?

[Ray Martin]

Using MS Access 2003 in 2000 mode. Small VB.Net application is inserting
reows from within a loop. Records 1 thru 113 approx are inserted very
quickly (perhaps less than 5 secs in total), but then it begins to slow down
so that by the time it's at records 200-300 it's taking almost 1 second per
insert, and taking longer and longer as each new row is added.

There is no functional difference in the code that executes the first 100
rows vs the rest - basically

For i = 1 to 300
SQL Insert a single row
next i

Are there any blocking parameters that should be set -is there some default
limit of say 100 that causes strange behaviour?

Any help or ideas would be appreciated.

 

[Ray Martin]

I found the problem. Inside the loop was code to assign the parameters to
the command string. This code should be outside the loop:

Dim cmdstring5 As New OleDb.OleDbCommand(strsql5, connectobj)

cmdstring5.Parameters.Add("code", OleDbType.Char)

etc, etc

with the only code INSIDE the loop being the code to set the value such as

cmdstring5.Parameters(1).Value = I

What this mistake does it uses up a lot of memory, and as the memory usage
increses performance begins to degrade. Clue was a Microsoft KB article that
showed how Jet performance decreases with multi-inserts unless you
periodically close the connection to flush memory.