[RESOLVED] [2008] Avoiding 6 nested for loops

[ElPresidente408]

Alright here's my problem. I had originally programmed this in Python and it ran in a few seconds although I knew it was messy. Now that I'm porting it to VB.Net, it chugs along really slow.

I get a weekly Excel file with hundreds of invoices that need to be assigned batch letters according to the following heirarchy:

Employee > Division > CustomerName > ProductCode> Expense Code

I had made each of these categories into its own for loop. On the innermost loop was a 6th that cycled through every row in the Excel spreadsheet. The program tries one combination on every row, and attaches a letter if the row matches the criteria. If it doesn't, it goes to the parent for loop, tries the next item and increments the letter by one.

This ran in a matter of seconds on Python, but VB does not like it at all. My VB program is pulling the information for the 5 categories by running 5 SQL queries on the Access database and storing each one in its own DataTable. I have 5 DataTables, each only containing the unique values which provide the foundation for the for loops.

With a test spreadsheet of 260 rows, the software ends up checking the rows 57,888 times which is a huge waste of processing. Here is a sample of the code which is causing the problem. I only did the first three categories and that's fairly slow.

Edit- Also wondering if I could stick a SQL query after the customer for loop and run something like "SELECT * FROM testQuery WHERE ASSIGNMENT=" & employee & " AND Division=" & division & " AND Customer=" & customer. I could then determine if the combination even exists. I already have the contents of the SQL query in a DataView in VB, but I'm unsure of how to run a similar query on it.

For Each employee In dbDataSet.Tables("ASSIGNMENT").Rows
            For Each division In dbDataSet.Tables("Division").Rows
                For Each customer In dbDataSet.Tables("Customer").Rows
 
 
                    'For each row in the upload file
                    For row = 2 To xlSheet.UsedRange.Rows.Count
                        'Determine where in the master table does the current row's PromoID occur. This will allow us
                        'to retrieve all the information for said PromoID such as ProductCode, etc
                        rowLoc = dbDataView.Find(xlArray(row - 2))
 
                        'If a PromoID is not in the Access database then -1 will be returned
                        If rowLoc = -1 Then
 
                            'Otherwise see if the PromoID information from spreadsheet = the current iteration of unique item
 
                        ElseIf dbDataView.Item(rowLoc).Item(5) = employee(0) Then
                            If dbDataView.Item(rowLoc).Item(1) = division(0) Then
                                If dbDataView.Item(rowLoc).Item(2) = customer(0) Then
 
                                    'If true, apply the letter and set the print flag to true
                                    xlSheet.Cells(row, 26).Value = CStr(Chr(currentLetter))
                                    didPrint = True
                                    ProgressBar1.Increment(1)
                                End If
                            End If
                        End If
 
                    Next row
                    'If a letter was printed this item then cycle the letter to the next one
                    If didPrint = True Then
                        currentLetter += 1
                    End If
                    didPrint = False
                Next customer
            Next division
            currentLetter = 65
 
 
 
        Next employee