what's faster rs.addnew or db.execute

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

Which of the two methods below is faster/better/safer when
adding say 50 new records to a table:

1)
For i = 1 to 50
rs.addnew
rs!field1 = x
rs!field2 = Y
rs.update
Next

2)
For i = 1 to 50
db.execute("SELECT INTO..."), dbFailOnError
Next

It seems to me they do the same and I use them both but
which is preferable if any?
 
The first one is a zillion times faster....

Why?

When you open the reocrdset, a huge number of things have to occur.

Does the table exist? (all kind of code is executed to check if the table
actually exists)
Open the table. (a huge amount code, processing, and often network waiting
time as the system comes back and says..yes..the table is now opened).
Ok, the table is opened...now, lets start checking the permissions..and
start allowing memory for this.

I could probably write about 20, to 100 pages as the amount of stuff that
happens to get that file and table finally opened, setup the file
handles..go through security etc. All of this stuff has to occur before ONE
record is added. In fact, you likely can add 5000 or more records in the
time it takes to just OPEN up the table.

Since the 2nd example has to do all of the above stuff each time..then you
have a HUGE overhead. This almost like the difference between starting up a
helicopter...and finally getting it up in the air and flying along....
 
Back
Top