Re: ADO.net Code contest

Tech-Archive recommends: Fix windows errors by optimizing your registry

bdwest wrote:

> I am sponsoring a contest to see if someone can come up with ADO.net
> code to insert records into a MS Access 2003 database faster than
> ADODB methods (MS ActiveX Data Object 2.x library). My experiece has
> shown that I can write records to Access at least 4 times faster
> using ADODB methods in a Visual Studio 2003/2005 project as compared
> to any ADO.net methods. I want to see if this is due to my ignorance
> or a problem with ADO.net and Access 2003 databases.
>
> From the link below, there is a test project available that has five
> methods of writing to an Access database, an ADODB method and 4
> versions of ADO.net methods. The time to write 30,000 records is
> timed for each method. The code is written in VB.net.
>
> There is a $100 reward to the first person who can use ADO.net
> methods that beat the ADODB methods working within the confines of
> the rules. Note there is only one reward.
>
> Please see the following link for full details.
>
> http://www.kelbli.net/kb1/AdoNetContest.html

The OleDb provider's ExecuteNonQuery statement is the bottleneck,
according to my profiling.

Nevertheless, your code is also pretty flawed. If I look at the
paramsAddInsert method, you don't re-use the parameters in the command,
nor do you prepare the command for re-use.

Re-using the parameters and preparing the statement, also specifying a
length for the varchar makes the code go from 37 to 13 seconds. (adodb
is 7).

So if I use this code:
Public Function AdoDotNet_paramsAddInsert(ByVal dbname As String) As
Boolean
'Test inserting 30000 records into an Access database table using
ADO.net
'with the parameters.add method

Dim DBcon As OleDbConnection
Dim mycmd As New OleDbCommand
Dim x As Integer
Dim d As Date

' create the connection string
Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbname

'Create the insert statement with place holders for particular values
mycmd.CommandText = "insert into Table1 (RecDate, Name, num, num2)
values(?, ?, ?, ?)"

' open the connection
DBcon = New OleDbConnection(conString)
mycmd.Connection = DBcon
DBcon.Open()
mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate",
OleDb.OleDbType.Date))
mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name",
OleDb.OleDbType.VarChar, 50))
mycmd.Parameters.Add(New OleDb.OleDbParameter("@num",
OleDb.OleDbType.Integer))
mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2",
OleDb.OleDbType.Integer))
mycmd.Prepare()
'insert the records
For x = 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 ^ 2

mycmd.ExecuteNonQuery()
Next

DBcon.Close()
mycmd = Nothing
DBcon = Nothing
End Function

I get 13 seconds. (funny thing is that release builds or debug builds
don't matter much, which likely is caused by the fact that the overall
time is spend in the non-managed oledb code)

This is the fastest you can possibly get. The rest of the time is
spend in ExecuteNonQuery.

For bulk inserts, this is a pretty solid method, though bulk inserts
are a special case in data-access. In general, bulk inserts are often
better done by special programs like DTS than by general purpose
data-access methods simply because bulk inserts can take shortcuts like
the code above does, but general purpose data-access code can't do
that, which means that such code has to prepare a query for every row
to insert, which is also why a dataset insert is much slower than this
special purpose method.

btw, a simple Prepare call and a length specification for the varchar
brings down the proc method from 28 to 19 seconds.

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: ADO.net Code contest
    ... I am seeing about 8 seconds on the ADODB method ... >> code to insert records into a MS Access 2003 database faster than ... > are a special case in data-access. ... but general purpose data-access code can't do ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: I am a winner :-)
    ... There is only one reward ... >> insert records into a MS Access 2003 database faster than ADODB methods ... >> records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: I am a winner :-)
    ... There is only one reward ... >> insert records into a MS Access 2003 database faster than ADODB methods ... >> records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)
  • I am a winner :-)
    ... Sub Reward100 ... > insert records into a MS Access 2003 database faster than ADODB methods ... > records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)