Multi-Record Import to Access Is Sloooooow

[TheWizardofInt]

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

 

    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

[/Code]

 

Am I doing something here that is binding me up, and missing it?

[IxiRancid]

Try doing the insert by array. First fill the whole array then put it into Access.

This won't take so much processor time, but may fill up RAM.

[bri189a]

Well where to begin...

 

1. You're opening and closing a connection in a loop.

2. You're 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 can't say I particularly like the whole idea and design, but I don't know the overall purpose of the program to say much about it.