ADO faster than ADO.net ?

I would like to know why I can use ADO (ADODB record set) running in vb.net
to insert records into an MS Access database 5-6 times faster than I can
using ADO.net. We may be inserting tens of thousands of records at a time
and performance becomes an issue. Let me also say I am required to use
Access by company and clients so I am stuck with that limitation.

Most of the stuff I have read implies that ADO.net should be faster. I
find just the opposite for inserting large number or records in Access. I
don't know if I am being inefficient in the ADO.net code or what. I would
appreciate any input.

I have a small vb.net project that inserts 30,000 records into a database
using ADO as shown in the code fragment below. It also includes 3 ADO.net
methods as well. It tells you the time in seconds it took to complete the
write. On my machine the ADODB record set method inserts 30,000 records in
6 seconds. The ADO.net methods take 30-37 seconds depending on method.


If you are interested, the project is available at this URL:

http://www.kelbli.net/pub/transfer/DBtest.zip


The database is in the bin directory (make sure you extract with relative
paths enabled).


Any help would be appreciated.

Brian

*********************************************************
ADO code fragment:

'create and open record set
Dim rstData As ADODB.Recordset
rstData = New ADODB.Recordset
rstData.Open("Table1", db, ADODB.CursorTypeEnum.adOpenKeyset,
ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)


'insert records
For x = 1 To 30000
rstData.AddNew()
d = DateAdd(DateInterval.Day, x, #1/1/1925#)
rstData.Fields.Item(0).Value = "M1"
rstData.Fields.Item(1).Value = d
rstData.Fields.Item(2).Value = x
rstData.Fields.Item(3).Value = x ^ 2
rstData.Update()
Next
.

Relevant Pages

  • Re: Problem with Recordset Find method
    ... In ADO 2.5 ... > key in your database because in that case the field will be automatically ... > works for you or not.If the problem persists then probably there's something ... >> I tried to create an index on the field in the record set. ...
    (microsoft.public.vb.database.ado)
  • Re: EoeleException with TDBcheckBox
    ... Why is it that the database as different value that in my record set and ... the field should be to Null in the database. ... > requerying the database ADO can't find the record. ... > Your choices are adCriteriaKey, adCriteriaAllCols, ...
    (borland.public.delphi.database.ado)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
Loading