Inserting text file rows into an Access DB

I am trying to speed up the process of inserting all of the rows in my text file into an Access DB. I originally switched to this route because of the size of those text files (that I am generating on another step of my process). I have more ROM than RAM. The overall process is working now, but I am falling into the rabbit hole of "optimizing" and can use someone else's opinion.

Limitations and specifications:

• I can't load all of the text file into one variable. I can't get more RAM.

• My logic needs to accept any text size (Column x Row).

• All the data will be inserted to an existing empty Access table.

I will try to minimize the code posted here. Feel free to ask questions if anything important seems to be missing. Here is the logic in question:

Method that contains the code:

Public Sub InsertTextFileRowsIntoAccessDB2(sAccessTableName As String, sDBFilePath As String, CashePath As String)
'...all the code here
End Sub

Logic A: Best I could do

```
'Insert each Row from the text file into the access DB

Do While (Not reader.EndOfStream)
Try
Dim fullRow() = reader.ReadLine.Split(CChar(vbTab))

'Create the parameters
Dim Prams As String = "@p1"
For i = 2 To fullRow.Count
Prams = Prams & " ,@p" & i
Next

Dim insertQuery As String = "INSERT INTO " & sAccessTableName & " VALUES (" & Prams & ");"
Dim cmd2 = New OleDbCommand(insertQuery, conn)

For index As Integer = 1 To fullRow.Length
Dim tempP As String = "@p" & index 'Array starts from zero
cmd2.Parameters.AddWithValue(tempP, fullRow(index - 1))
Next
cmd2.ExecuteNonQuery()

Catch ex As Exception
Dim rex = ex 'TODO: Write the row and the exception somewhere
End

OK, thought I might get a quick win if you were using a version of Access old enough to be using the Jet engine (in that case DAO is inherently much faster).

First thing: Don't go down the road of your "Logic 2". The fact that you realized you have to escape any quotes in the input file should be a great reminder as to why using parameters is good practice - it sanitizes input and avoids SQL injection.

Second, and this is the main one. You are looping over code that should always execute with the same results. Pull these out of you loop so you only do them once:

'Create the parameters
            Dim Prams As String = "@p1"
            For i = 2 To fullRow.Count
                Prams = Prams & " ,@p" & i
            Next

            Dim insertQuery As String = "INSERT INTO " & sAccessTableName & " VALUES (" & Prams & ");"
            Dim cmd2 = New OleDbCommand(insertQuery, conn)

The dynamic parameter generation isn't ideal, but isn't really avoidable if you need a general purpose function. I'm guessing that a large amount of the inefficiency is repeatedly creating new OleDbCommand objects (as well as the SQL string) when they can be re-used. In fact, parameterized OleDbCommand objects are optimized for looping over with multiple inserts. Add your parameter list without values before you go into the main loop, and then assign to them by index.

This should speed you up quite a bit. If that doesn't get you into the ballpark of where you need to be performance-wise, the Access object model will let you automate a bulk insert from a tab delimited file. You may want to benchmark how long it takes Access to import the file and explore this possibility too, especially if you control the datafile generation.

One last note: Don't forget to "'TODO: Write the row and the exception somewhere" and keep swallowing the exceptions.

EDIT:

Good starting place for Access automation is here - there are some community links at the bottom of the page that are pretty good. I'd only use this as a last resort as it pulls in lots of dependencies and requires starting an Access process. I'm fairly sure managing the OleDbCommand outside the loop is going to give the best performance.

StackExchange Code Review Q#41411, answer score: 3