Loop Running VERY Slow

Question

please help me make my app a little faster, it's taking forever to loop through and give me results right now.

here is what im donig: 1. load gridview from an uploaded excel file (this would probably be about 300 records or so) 2. compare manufacturer, model and serial No to my MS SQL database (about 20K records) to see if there is a match.

'find source ID based on make/model/serial No combination.
        Dim cSource As New clsSource()
        Dim ds As DataSet = cSource.GetSources()
        Dim found As Boolean = False

        'populate db datatables
        Dim dt As DataTable = ds.Tables(0)
        Dim rows As Integer = gwResults.Rows.Count()
        For Each row As GridViewRow In gwResults.Rows
            'move through rows and check data in each row against the dataset
            '1 - make
            For Each dataRow As DataRow In dt.Rows
                found = False
                If dataRow("manufacturerName") = row.Cells(1).Text Then
                    If dataRow("modelName") = row.Cells(2).Text Then
                        If dataRow("serialNo") = row.Cells(3).Text Then
                            found = True
                        End If
                    End If
                End If

                'display results
                If found Then
                    lblResults.Text += row.Cells(1).Text & "/" & row.Cells(2).Text & "/" & row.Cells(3).Text & " found"
                Else
                    lblResults.Text += row.Cells(1).Text & "/" & row.Cells(2).Text & "/" & row.Cells(3).Text & " not found "
                End If

            Next
        Next

is there a better way to find a match between the two? i'm dying here.

score 2 · Accepted Answer · answered Apr 20 '11 at 13:51

For each of your 300 gridview rows, you are looping through all 20k datarows. That makes 300 * 20k = 6 million loop iterations. No wonder your loop is slow. :-)

Let me suggest the following algorithm instead (pseudo-code):

For Each gridviewrow
    Execute a SELECT statement on your DB with a WHERE clause that compares all three components
    If the SELECT statement returns a row
        --> found
    Else
        --> not found
    End If
Next

With this solution, you only have 300 loop iterations. Within each loop iteration, you make a SELECT on the database. If you have indexed your database correctly (i.e., if you have a composite index on the fields manufacturerName, modelName and serialNo), then this SELECT should be very fast -- much faster than looping through all 20k datarows.

From a mathematical point of view, this would reduce the time complexity of your algorithm from O(n * m) to O(n * log m), with n denoting the number of rows in your gridview and m the number of records in your database.

i thought it's bad to sql within the loop? i'll give it a try! thanks! — Madam Zu Zu, Apr 20 '11 at 13:58
@xrum: As always: It depends. Of course, making one (1) access to a DataTable in memory is faster than executing one (1) SQL to the database. But accessing a DataTable 20k times might well be much slower than executing one single SQL. — Heinzi, Apr 20 '11 at 14:00

score 1 · Answer 2 · answered Apr 20 '11 at 15:50

While Heinzi's answer is correct; it may be more beneficial to carry out the expensive SQL query before the loop and filter using data views so you aren't hitting the DB 300 times

Execute a SELECT statement on your DB 
For Each gridviewrow
    if my datagridview.Select(String.format("manufacturerName={0}", row.item("ManufacturerName"))
    If the dataview has a row
        --> found
    Else
        --> not found
    End If
Next

NOTE: I only compared a single criteria to illustrate the point, you could filter on all three in here

score 0 · Answer 3 · answered Apr 20 '11 at 14:12

Hmm... how about loading the data from the spreadsheet into a table in tempdb and then writing a select that compares the rows in the way that you want to compare them? This way, all of the data comparisons happen server-side and you'll be able to leverage all of the power of your SQL instance.

Loop Running VERY Slow

3 Answers3

Linked