ADO faster than ADO.net ?

[Guest]

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

 

[Cor Ligthert]

Brian,

Interesting. I tested it.

Your code with the parameters was not ideal.
Making that better I got the same result as with the insert SQLString

I also added a procedure with a stored procedure and could win 20%.

However it was still 4 a 5 times slower than ADODB.

I know that it does not help, however maybe was you interested if others
would try that nice test from you.

By the way, all that setting to nothing is for nothing, it goes all out of
scope.

I hope this helps something,

Cor

 

[Guest]

Cor,

Would you mind sending me back your changes so I can see what you did. Feel
free to email me at (e-mail address removed).

Thanks for your input and advice.

Brian

 

[Tym]

Any help would be appreciated.

A side note from my own experience with DAO is that when using the
..AddNew method to add 1000's of records, the app gradually got slower
and slower....

However, if I used the SQL Insert Into statement and the
MyDatabase.Execute mySQLstatement, the process shot through...

Perhaps that might be a better avenue for you to try, rather then
physically adding the records to the "recordset"


Tym

Please do not adjust your brain, there is a fault with reality.

emails - domain is correct, user isn't. I'm sure you can work it out though!

 

[Cor Ligthert]

Brian,

The other one I have not anymore, I made this one from that.

There is now some time involved with creating the sp of couse, however that
is in my opinion nothing.

\\\
'Test inserting 30000 records into an Access database table using ADO.net
'with the parameters.add method and sp
' create the connection string
Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbname
' open the connection
Dim DBcon As New OleDbConnection(conString)
Dim mycmd As New OleDbCommand("Create Procedure MyProc " & _
"(@RecDate DateTime, @Name Text(50), @num int, @num2 int)" & _
"As insert into Table1 (RecDate, Name, num, num2) values" & _
"(@RecDat, @Name, @num, @num2)", DBcon)
DBcon.Open()
mycmd.ExecuteNonQuery()
'insert the records
mycmd.CommandText = "MyProc"
mycmd.CommandType = CommandType.StoredProcedure
mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate",
OleDb.OleDbType.Date))
mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name",
OleDb.OleDbType.VarChar))
mycmd.Parameters.Add(New OleDb.OleDbParameter("nume",
OleDb.OleDbType.Integer))
mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2",
OleDb.OleDbType.Integer))
mycmd.Parameters.Add(New OleDb.OleDbParameter("",
OleDb.OleDbType.Integer))
Dim d As DateTime
For x As Integer = 1 To 30000
d = DateAdd(DateInterval.Day, x, #1/1/1925#)
mycmd.Parameters(0).Value = d
mycmd.Parameters(1).Value = "M3"
mycmd.Parameters(2).Value = x
mycmd.Parameters(3).Value = x
mycmd.Parameters(4).Value = 0
mycmd.ExecuteNonQuery()
Next
mycmd.CommandText = "Drop Procedure MyProc"
mycmd.CommandType = CommandType.Text
mycmd.ExecuteNonQuery()
DBcon.Close()
///

I hope this helps,

Cor

 

[Guest]

Cor,

Thanks for the code. In my test it was faster than all of the other .net
methods. I appreciate your input.

Thanks again

Brian

 

[Guest]

Cor,

One more question for you. Why do you have to have to

add this line

mycmd.Parameters.Add(New OleDb.OleDbParameter("", OleDb.OleDbType.Integer))

and this line

mycmd.Parameters(4).Value = 0

The Sp is defined with 4 parameters. It is obivously needed because it
won't run without the two lines. I don't understand why.

Thanks

Brian

 

[Cor Ligthert]

Brian,

The Sp is defined with 4 parameters. It is obivously needed because it
won't run without the two lines. I don't understand why.

Than at least luckily for is that we both don't understand it.

)

Cor

 

[JerryH]

If I may...the first line you ask about creates a Parameter object that
acts as the "placeholder" for the query for the command object.

In the other line, you actually assign a value to that Parameter. When
the command object is executed, the value in teh Parameter object is
passed to the query.

 

[Guest]

JerryH,

If you look at Cor's code the SP is defined with 4 parameters. However, 5
parameters must be passed to get it to run. You get an exception otherwise
and I don't understand why. I was asking him about the two extra lines.

There are only four fields in the database. Any ideas?

Thanks

Brian