VB.Net - Write Records To Access Quickly

Question

I have a large number of records to add to a Access database in realtime. The items are temperatures, water flows etc from electronic instruments.

The code I have is below, can it be improved or is there a different way to add records more quickly - currently, 10,000 records takes approx. 10secs.

I am currently simulating the data to prove the concept of adding large number of records.

Dim connetionString As String
Dim connection As OleDbConnection
Dim oledbAdapter As New OleDbDataAdapter
Dim sql As String

connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Neil Cooley\Desktop\Database1.accdb"

connection.Open()

Dim stop_watch_1 As New Stopwatch
Dim getDate As Date = Date.Now

For i As Integer = 1 To 10000 Step 1
  sql = "INSERT INTO Lines VALUES('TAG " & i.ToString & "','" & i.ToString & "','192','" & getDate & "')" 

  oledbAdapter.InsertCommand = New OleDbCommand(sql, connection)
  oledbAdapter.InsertCommand.ExecuteNonQuery()
Next
stop_watch_1.Stop()
MsgBox("Row(s) Inserted !! - " & stop_watch_1.ElapsedMilliseconds.ToString)

Answer 1

I think that the best you can hope for with that is to use SQL parameters, like this:

Dim connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Neil Cooley\Desktop\Database1.accdb"
Dim sql = "INSERT INTO Lines([tag], [num], [x], [dt]) VALUES(?, ?, '192', ?)"

Dim stop_watch_1 As New Stopwatch()

Using conn As New OleDbConnection(connString),
       cmd As New OleDbCommand(sql, conn)

    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@tag", .OleDbType = OleDbType.VarChar, .Size = 20})
    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@num", .OleDbType = OleDbType.VarChar, .Size = 20})
    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@date", .OleDbType = OleDbType.Date})

    cmd.Parameters("@date").Value = DateTime.UtcNow()

    conn.Open()

    For i As Integer = 1 To 10000 Step 1
        cmd.Parameters("@tag").Value = "TAG " & i.ToString()
        cmd.Parameters("@num").Value = i.ToString()
        cmd.ExecuteNonQuery()
    Next

End Using

stop_watch_1.Stop()
MsgBox("Row(s) Inserted !! - " & stop_watch_1.ElapsedMilliseconds)

Make sure to set the data type and sizes of the parameters to match the database columns. You should declare the column names in the INSERT statement. The parameters are represented by ? in the query; they are added in the order they are used. The parameter names in the OleDbParameter instances are for convenience.

(Make sure to use a DateTime type in the database for DateTimes, and keeping it in UTC avoids daylight savings time change problems.)

Using a faster disk drive could help, perhaps one that the OS is not run from.

If using a different database is an option, perhaps a key-value store would serve you well. Or even just a plain CSV file.

Answer 2

I would try using a data table - it should run faster.

Say, try like this:

   Dim sqlCon As New OleDbConnection(My.Settings.AccessDB)

    Dim strSql As String = "SELECT * FROM tblBig WHERE ID = 0"
    Dim cmdSQL As New OleDbCommand(strSQL, sqlCon)

    Dim rstData = New DataTable
    sqlCon.Open()
    rstData.Load(cmdSQL.ExecuteReader)

    Dim da As New OleDbDataAdapter(cmdSQL)
    Dim daU As New OleDbCommandBuilder(da)

    Dim stop_watch_1 As New Stopwatch
    Dim getDate As Date = Date.Now

    stop_watch_1.Start()

    For i As Integer = 1 To 10000

        Dim MyNewRow = rstData.NewRow

        MyNewRow("City") = "City" & i
        MyNewRow("FirstName") = "FirstName " & i
        MyNewRow("LastName") = "LastName " & i

        rstData.Rows.Add(MyNewRow)

        If (i Mod 500) = 0 Then
            'Debug.Print("data write" & stop_watch_1.ElapsedMilliseconds / 1000)
            da.Update(rstData)
            rstData.Clear()
        End If
    Next

    ' write data back to database
    da.Update(rstData)

    sqlCon.Close()

    stop_watch_1.Stop()

    MsgBox("Row(s) Inserted !! - " & stop_watch_1.ElapsedMilliseconds / 1000)

I have it set to write out in above every 500 rows.

However, you are getting about 1,000 rows per second, and I not at all sure you going to increase that rate - it is VERY good rates you already seeing, and I can't say the above is going to help much.

edit: I feel the need for speed!!! SUPER FAST!!!

Ok, if you want to bring this down from 10 seconds to 1/3 of a second?

by-pass the .net providers. Hit the database DIRECT with the native DAO object library. You get about 30 times faster.

So, try this:

Imports Microsoft.Office.Interop.Access

and then do this:

   Dim dbE As New Dao.DBEngine
    Dim db As Dao.Database

    db = dbE.OpenDatabase("C:\test\test44.accdb")

    Dim rstData As Dao.Recordset

    rstData = db.OpenRecordset("tblBig")

    Dim stop_watch_1 As New Stopwatch
    stop_watch_1.Start()

    For i As Integer = 1 To 10000

        rstData.AddNew()
        rstData("City").Value = "City" & i
        rstData("FirstName").Value = "FirstName " & i
        rstData("LastName").Value = "LastName " & i
        rstData.Update()

    Next

    db.Close()

    stop_watch_1.Stop()
    MsgBox("Row(s) Inserted !! - " & stop_watch_1.ElapsedMilliseconds / 1000)

You find a min of 30x faster - even more. So, DIRECT use of the data engine, and by-passing all of the .net providers? You EASY insert 10,000 rows in WELL UNDER 1 second.