How can I improve my inserts?
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
Logic 2
'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
Sorry about the funky vb.net highlighting.
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.
