Multi-Record Import to Access Is Sloooooow

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
The following code takes data from one datatable based on the parameters mapped in another datatable, then uses the two to build an Insert string with parameters.

It inserts the data into Access and starts out very fast, but after 100 or so rows it is as slow as death

Code: 
    For Each dr In dtOld.Rows
                iProg += 1
                lblProg.Text = iProg
                pbStatus.Value = iProg
                Application.DoEvents()
                sInsert = "Insert Into [" & sTable & "] ("
                sValue = " Values("

                build the parameters
                For iPar = 0 To dtNew.Rows.Count - 1
                    sInsert &= "[" & dtOld.Columns(iPar).ColumnName & "], "
                    sValue &= "?, "
                    Select Case dtNew.Rows(iPar).Item("Type")
                        Case "System.String"
                            cmd.Parameters.Add(New OleDb.OleDbParameter("@" & dtNew.Rows(iPar).Item("Name"), System.Data.OleDb.OleDbType.VarChar, dtNew.Rows(iPar).Item("Size")))
                        Case "System.DateTime"
                            cmd.Parameters.Add(New OleDb.OleDbParameter("@" & dtNew.Rows(iPar).Item("Name"), System.Data.OleDb.OleDbType.Date, dtNew.Rows(iPar).Item("Size")))
                        Case "Memo"
                            If Not dr.IsNull(dtNew.Rows(iPar).Item("Name")) Then
                                cmd.Parameters.Add(New OleDb.OleDbParameter("@" & dtNew.Rows(iPar).Item("Name"), System.Data.OleDb.OleDbType.VarWChar, dr.Item("NOtes").length))
                            Else
                                cmd.Parameters.Add(New OleDb.OleDbParameter("@" & dtNew.Rows(iPar).Item("Name"), System.Data.OleDb.OleDbType.VarWChar, 1))
                            End If
                    End Select
                    cmd.Parameters("@" & dtNew.Rows(iPar).Item("Name")).Value = dr.Item(dtNew.Rows(iPar).Item("Name"))
                Next
                sInsert = Mid(sInsert, 1, sInsert.Length - 2) & ")"
                sValue = Mid(sValue, 1, sValue.Length - 2) & ")"
                sInsert &= sValue
                insert the data to the table
                Try
                    oConn.Open()
                    cmd.Connection = oConn
                    cmd.CommandText = sInsert
                    cmd.ExecuteNonQuery()
                    oConn.Close()
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Critical, "An error has occurred!")
                    oConn.Close()
                    Exit Sub
                End Try
            Next

Am I doing something here that is binding me up, and missing it?
 
Try doing the insert by array. First fill the whole array then put it into Access.
This wont take so much processor time, but may fill up RAM.
 
Well where to begin...

1. Youre opening and closing a connection in a loop.
2. Youre adding parameters inside a nested loop rather than reusing existing parameters.
3. You never dispose your command objects or connection object which (see #1).

I cant say I particularly like the whole idea and design, but I dont know the overall purpose of the program to say much about it.
 
Back
Top