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 Try Loop
'Insert each Row from the text file into the access DB Do While (Not reader.EndOfStream) Try Dim fullRow() = reader.ReadLine.Split(CChar(vbTab)) 'Build the query string to have all the columns Dim query As String = "INSERT INTO " & sAccessTableName & " VALUES (" & "'" & fullRow(0) & "'" For index As Integer = 1 To fullRow.Length - 1 If fullRow(index).Equals("") Then 'Check if the column is empty query = query & ", NULL " Else 'Check if the string has a quote in it (which causes issues to SQL) query = query & ", '" & fullRow(index).Replace("'", "''") & "'" End If Next query = query & ");" Dim cmd As OleDbCommand = New OleDbCommand(query, conn) cmd.ExecuteNonQuery() Catch ex As Exception Dim rex = ex 'TODO: Write the row and the exception somewhere End Try Loop
How can I improve my inserts?
Questions asked in comments:
What version of Access is it? Can you post your connection string?
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDBFilePath
Let me know if this could cause any issues with other machines or how to avoid incompatibility.
Also, what does the table you are inserting into look like?
It depends on the data (table) I am getting. I am trying to get the column count to be dynamic so the method would work with any table. However, I am making the assumption that the column number in the table will be the same as the column number in the text file that I am reading from.