Fast serarch of 2 dimensional array

I need a method of quickly searching a large 2 dimensional array. I extract the array from Excel, so 1 dimension represents the rows and the second the columns. I wish to obtain a list of the r开发者_运维知识库ows where the columns match certain criteria. I need to know the row number (or index of the array).

For example, if I extract a range from excel. I may need to find all rows where column A =”dog” and column B = 7 and column J > “a”. I only know which columns and which value to find at run time, so I can’t hard code the column index.

I could use a simple loop, but is this efficient ? I need to run it several thousand times, searching for different criteria each time.

      For r As Integer = 0 To UBound(myArray, 0) - 1
        match = True  
        For c = 0 To UBound(myArray, 1) - 1
            If not doesValueMeetCriteria(myarray(r,c) then
                match = False
                Exit For
            End If
        Next
        If match Then addRowToMatchedRows(r)
    Next

The doesValueMeetCriteria function is a simple function that checks the value of the array element against the query requirement. e.g. Column A = dog etc.

Is it more effiecent to create a datatable from the array and use the .select method ?

Can I use Linq in some way ?

Perhaps some form of dictionary or hashtable ?

Or is the simple loop the most effiecent ?

Your suggestions are most welcome.

Not knowing exactly what you're trying to achieve here, a couple of things come to mind... Since you say that it is a LARGE db; you should at least sort your array. Then you can 'bubble sort' it to locate the initial selection criteria greatly reducing the forced loop-through-everything you have now.

Additionally, since this is coming from an EXCEL spreadsheet, perhaps you could let the spreadsheet do some_or_all of the work THEN get the data.

A HASTABLE requires a unique KEY, it seems like you might duplicate cell data in rows; and I know of no way to ask a HASHTABLE to return values based upon a "key that's >= 'a'"

If you are going to be doing ALOT of searching based on all kinds of varying criteria you might just save your self a lot of work and pump the data into a database table so that you can bring SQL to bear as needed.