May I use DAO 3.6 instead of ADO.NET or ADODB ?

I think DAO is the fastest way to deal with Access databases.

Regards from Madrid (Spain)
Jesús López

VB MVP

    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
   

Hi Brian,

Here you have a code using the ADO.NET OleDb Provider and StreamWriter that
is 6 to 8 times faster than your ADODB method:

   Sub Reward100(ByVal dbname As String)

       'the first person to provide me with a ADO.net based function that
will insert the 30,000 records into the database
       'as shown in the other functions of this class will receive a
cashier check for US $100.  There is only one reward
       'no other external references aloud

       'reward code goes here
       'Test inserting 30000 records into an Access database table using
StreamWriter and ADO.NET.
       'This function is 6 to 9 times faster than the ADODB method

       Dim x As Integer
       Dim d As Date

       Dim FormatProvider As IFormatProvider =
Globalization.CultureInfo.GetCultureInfo("en-US")
       Dim writer As New StreamWriter("Table1.txt")

       'insert records into a file text'
       For x = 1 To 30000
           d = DateAdd(DateInterval.Day, x, #1/1/1925#)

           writer.Write("M1") : writer.Write(vbTab)
           writer.Write(d.ToString("d", FormatProvider)) :
writer.Write(vbTab)
           writer.Write(x) : writer.Write(vbTab)
           writer.Write(x ^ 2) : writer.Write(vbNewLine)
       Next
       writer.Close()

       'create Schema.ini
       writer = New StreamWriter("Schema.ini")
       writer.WriteLine("[Table1.txt]")
       writer.WriteLine("Format = TabDelimited")
       writer.WriteLine("ColNameHeader = False")
       writer.WriteLine("Col1 = name Text Width 50")
       writer.WriteLine("Col2 = recdate DateTime")
       writer.WriteLine("Col3 = num Single")
       writer.WriteLine("Col4 = num2 Single")
       writer.Close()

       'insert records into Access Database
       Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & dbname & ";Persist Security Info=False"
       Dim cn As New OleDbConnection(conString)

       Dim query As String = "INSERT INTO Table1 " & _
           "SELECT name, recdate, num, num2 FROM [Table1#txt] " & _
           "IN '" & Application.StartupPath & "' 'Text;';"

       Dim cmdInsert As New OleDbCommand(query, cn)
       cn.Open()
       cmdInsert.ExecuteNonQuery()
       cn.Close()
   End Sub

Regards from Madrid (Spain)

Jesús López
VB MVP