[RESOLVED] [2008] Avoiding 6 nested for loops

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.

s Code:

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

Moved at request!

The datatable object has a select method, so rather than iterating through all of your tables, you can use the select method to query for results. Also, you can set up a primary key, and use the table's row's find method, which is much faster.

You can also use LINQ to slice that data pretty easily and return lists based on complex criteria just like a database.

The original code I had posted took on average 8.335 seconds to execute.

Following the suggestions, I reduced the execution time to 1.566 seconds by adding a primary key to the master table and changing the FOR and IF statements. I also used the find command for the dataRow

dataRow = dbDataTable.Rows.Find(myArray(row - 2))

For employee = 0 To dbDataSet.Tables("ASSIGNMENT").Rows.Count - 1

IF dataRow.Item(5) = dbDataSet.Tables("ASSIGNMENT").Rows(employee).Item(0)


I reduced the average time to 1.434s by adding a primary key to each of the tables in the DataSet. Any further ideas or is this about as good as it'll get?

I will keep LINQ in mind but I'm still a novice at VB.Net and trying to stick to the basics first.

Edit- Started tinkering with Select. Seems like it'll be useful in cutting down un-necessary loops. Will let you know.

Thanks

The Select command was a HUGE help. I shaved the execution time down to about 0.450 seconds. Right after each FOR statement I added the following lines

v Code:

selectString = "ASSIGNMENT='" & dbDataSet.Tables("ASSIGNMENT").Rows(employee).Item(0) & "'"
 
If dbDataTable.Select(selectString).Length > 0 Then

The string builds on itself as it goes through the levels.

Thanks a lot :thumb: :thumb: