VB Express 2008 -> Access 2007 Updating Items Slow

[shinypenguin]

I'm using Visual Basic Express 2008 to create an interface with a order database in MS Access 2007 (.accdb file). Everything is fully functional except updating the "Inventory" table is very slow. Updating around 2200 items takes almost 2 minutes and the window will appear as "not responding" for the duration if you click away and come back. Here is the process:

1. Tab-delimited text file is picked by user, which includes all inventory items (not just new ones)
2. Each line from text is parsed and read into variables (tempSKU, tempItemTitle, etc.)
3. Old items are updated and new ones are added. -> This is the bottleneck

I used to have a series of IF statements to check which elements were changed and report them, but that was about 2x as slow. The new code is a little faster but doesn't provide the feedback I liked (update part is commented around):

Private Sub btnImportInv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportInv.Click
        Dim strName As String
        Dim dlg As Windows.Forms.OpenFileDialog
        Dim newSKUAdded As Integer = 0
      
        dlg = New OpenFileDialog()
        dlg.Multiselect = False
        dlg.InitialDirectory = My.Settings.INVENTORY_REPORT_PATH
        dlg.Filter = "txt files (*.txt)|*.txt|" & "All files (*.*)|*.*"

        If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            ' Count all the lines of the input text file / set progress bar to length
            Dim lines As String() = IO.File.ReadAllLines(dlg.FileName)
            ProgressBar1.Maximum = lines.Length - 1

            For Each strName In dlg.FileNames
                Using MyReader As New TextFieldParser(strName)
                    MyReader.TextFieldType = FieldType.Delimited
                    MyReader.Delimiters = New String() {vbTab}
                    Dim currentRow As String()
                    Dim countRows As Integer = 0

                    'Loop through all of the fields in the file. 
                    'If any lines are corrupt, report an error and continue parsing. 
                    currentRow = MyReader.ReadFields() 'skip header line in txt
                    While Not MyReader.EndOfData
                        Try
                            currentRow = MyReader.ReadFields()
                            Dim currentField As String
                            Dim inputCounter As Long = 0

                            Dim tempSKU = ""
                            Dim tempItemTitle = ""
                            Dim tempItemNote = ""
                            Dim tempPrice As Double = 0
                            
                            For Each currentField In currentRow
                                inputCounter += 1
                                Select Case (inputCounter)
                                    Case 1
                                        tempSKU = currentField
                                    Case 2
                                        tempItemTitle = currentField
                                    Case 3
                                        tempItemNote = currentField
                                    Case 4
                                        tempPrice = currentField
                                End Select
                            Next


                            ''''''''''''''''''' UPDATE PART (SLOW) ''''''''''''''''''''''''
                            Try
                                Me.InventoryTableAdapter.UpdateBySKU(tempSKU, tempItemTitle, tempItemNote, tempItemPrice)
                            Catch ex As Exception
                                Me.InventoryTableAdapter.Insert(tempSKU, tempItemTitle, tempItemNote, tempItemPrice)
                                newSKUAdded += 1
                            End Try
                            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


                        Catch ex As MalformedLineException
                            MsgBox("Line " & ex.Message & " is invalid.  Skipping")
                        End Try

                        ''''''''' PROGRESS BAR CONTROL ''''''''''''''''''
                        If ProgressBar1.Value < (lines.Length - 1) Then
                            ProgressBar1.Value += 1
                        ElseIf ProgressBar1.Value = (lines.Length - 1) Then
                            ProgressBar1.Value = 1
                        End If
                        '''''''''''''''''''''''''''''''''''''''''''''''''

                        countRows += 1

                    End While
                    lstbxRecords.Items.Add("Added: " & newSKUAdded & " new SKUs, and updated the rest")
                    lstbxRecords.Items.Add(".....Processed " & countRows & " inventory items.....")
                End Using
            Next
        End If
        Me.InventoryTableAdapter.Fill(Me.OrderDBDataSet.inventory)
        ProgressBar1.Value = 0
    End Sub

The UpdateBySKU query is:

UPDATE       inventory
SET                [itemTitle] = ?, [itemNote] = ?, [price] = ?
WHERE        ([SKU] = ?)

I am considering switching to use a SQL express database, not sure if that would boost performance. Also, not sure if using a dataset or something would speed it up (couldn't figure out how to use them yet).

Any suggestions appreciated. Please let me know if any more information is needed, and what kind of speed I should expect doing an operation like this.

Thanks in advance!

[leo002000]

Hi,

You don't say what environment you are operating in (locally on your PC or on a dedicated server)? If you are running/testing this on the local asp.net server. Then it can be hard to determine the efficiency of your code.

I would almost certainly use sql server express over access, for a multitude of reasons (security and performance) being the 2 obvious ones.

Cheers

Leo

[shinypenguin]

Thank you for the response. I'm not sure what asp.net is, the connection was set up by assigning a system DSN to the .accdb file, and using the database connection wizard in VB (I'm fairly new to programming, and the data connection parts are the weakest for me). The program was published and installed on a PC with a local copy of the .accdb file it is accessing.

Are there any more optimized ways to handle the insert/update method? I'd like to be able to speed up the program in its current form, and also have the better code in place for when I can get around to setting up a SQL express data source.

Thanks

[lolafuertes]

Some things you can do:
To Count all the lines to process you must

For Each strName In dlg.FileNames
				' Count all the lines of the input text file / set progress bar to length
				Dim lines As String() = IO.File.ReadAllLines(dlg.FileName)
				ProgressBar1.Maximum += lines.Length - 1
			Next

Then to loop over all the files:

For Each strName In dlg.FileNames
				Dim lines As String() = IO.File.ReadAllLines(dlg.FileName)

To have the fields for each line you can:

For Each line As String In lines
					Dim currentRow As String() = line.Split(vbTab)

In order to validate if the input line is OK you can

If currentRow.Length = 4 _
					AndAlso currentRow(0).Length > 0 _
					AndAlso currentRow(1).Length > 0 _
					AndAlso currentRow(2).Length > 0 _
					AndAlso currentRow(3).Length > 0 Then

On the Me.InventoryTableAdapter there is a Connection property that defines if this is an OLEDDB or just and SQL Connection.

Assuming this is an OLEDB connection there is the tric:

Dim sqlCmd As New OleDb.OleDbCommand
						sqlCmd.Connection = Me.InventoryTableAdapter.Connection
						sqlCmd.CommandText = _
						 "IF NOT EXISTS (SELECT SKU FROM inventory WHERE SKU = '" & _
						currentRow(0) & "') INSERT INTO inventory VALUES( " & _
						"'" & currentRow(0) & "', " & _
						"'" & currentRow(1) & "', " & _
						"'" & currentRow(2) & "', " & _
						"'" & currentRow(3) & "') ELSE UPDATE inventory SET " & _
						"itemTitle = '" & currentRow(1) & "', " & _
						"itemNote = '" & currentRow(2) & "', " & _
						"price = '" & currentRow(3) & "' " & _
						"WHERE SKU = '" & currentRow(0) & "';"
						sqlCmd.ExecuteNonQuery()

Here you can

Progressbar1.PerformStep

and finish with

Else
						MsgBox("Line [" & line & "] of " & strName & " is malformed")
					End If
				Next
			Next

I am prety sure this is another way to do the same.

The best part is that you can concatenate as may SQL sentences termitated with a semicolon, as the total amount is 'reasonable' and execute all them at once.

I've put about one hundred sentences in just one command without problems (in other projects where I needed som kind of 'bulk' inserts).

Maybe you still need to play around a little bit.

Hope this helps