[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

[mendhak]

Moved at request!

[wild_bill]

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.

[Jenner]

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

[ElPresidente408]

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

[ElPresidente408]

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

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