Keeping dataset in memory or multiple SQL queries

I need to loop through a gridview to find records that match items in a different table (in SQL). Is the best method to loop through the gridview and call an SQL procedure on every loop to find matching record开发者_开发问答s? Or should I pull the entire sql db into a datatable and query the dataset in my gridview loop?

'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

I think you need to define "better".

If you need to optimize for memory - then loop and query. Oh, but if you're writing for moderns hardware, optimizing for memory at this level is usually not a good idea (though if your grid has tens or hundreds of thousands or rows, that may not be true).

If you need to optimize for time, the run a single query, and loop through in memory.

Where is the data in the gridview coming from? What you should be doing is an SQL join. If that is not possible, e.g. the gridview data is from an entirely different source, it would be much faster to do a single interleaving loop against the SQL result set. This assumes there are many different values that need matching, and it's not practical to build an SQL select from the data in your gridview that would do the job directly.

1) Create an SqlDataReader from the database ordered by manufacturername, modelName, serialNo

2) Order the gridview the same way

3) Starting at the first record of each, in a loop, compare the current values in the gridview against the current values from the data reader. If they are less than (before) the current data in the data reader, move the grid view pointer forward. If they are greater than, move the data reader pointer forward. If they are equal, then take the action you need, and (if you want to keep going) move the gridview pointer forward. Continue until you reach the end of the grid view and the last record in the data reader is greater than the last record in the grid view.

This has a maximum number of iterations equal to the larger of the two sets and a single database query.