5
\$\begingroup\$

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

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.

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ For answer completeness, can you elaborate on the Jet engine or automating the bulk insert? Links to methods you would take would be sufficient. Thank you so much for your time and input!! \$\endgroup\$ – Ammar Feb 13 '14 at 5:22
  • 1
    \$\begingroup\$ Jet was the older Access database engine that they replaced with ACE, and it was built around DAO. DAO classes are still available in .NET, but they are deprecated in preference to the ADO way of doing things. It was much faster to use with Jet, because it was using native methods. ACE is much, much better. \$\endgroup\$ – Comintern Feb 13 '14 at 5:27
  • 1
    \$\begingroup\$ ...and an edit above for a good starting place for Office automation. It's more or less like starting Access and controlling remotely through your code. \$\endgroup\$ – Comintern Feb 13 '14 at 5:36
  • \$\begingroup\$ Hopefully last question. I followed your suggestion, but I am running into an issue where the parameters are not updating when I pass in new values (line cmd2.Parameters.AddWithValue()). Any way around this? I looked around and I can't seem to find a way to clear the parameter list easily. \$\endgroup\$ – Ammar Feb 19 '14 at 23:25
  • 1
    \$\begingroup\$ Without seeing the new code, I would say that instead of using .AddWithValue() inside the loop, you should use something like cmd2.Parameters[tempP].Value = fullRow(index - 1); When you put the Parameters into the OleDbCommand before the loop, add them without values, such as: cmd2.Parameters.Add(tempP, [datatype]); \$\endgroup\$ – Comintern Feb 20 '14 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.