This is my scenario

I have a cached datatable of all Items in my database.

When placing an order, depending on which branch or office you are entering
the order for certain items can be available to be placed on the order.

Right now I get a list of items available for LocationA, and in a loop I
find that row in the cached table, then create another table and import each
row into that new table to be used to bind to the textbox so i.e.

Dim itemCache as DataTable
.....'load the table, etc at start up

'Get the items that are available for locationA and put it into a dataTable
Dim locationSpecificItemSet as DataTable =
GetLocationSpecificItemSet(LocationA)

Dim resultTable as DataTable = New DataTable()

For a as integer = 0 to locationSpecificItemSet.Rows.Count -1

resultTable.ImportRow(ItemCache.Find(locationSpecificItemSet.Rows(a)("Item_ID")
End For

Return resultTable.

I've tried using a datarelation then going through each of the ParentRows
and returning the ChildRows but my problem is when my ItemCache is 200,000+
items the looping takes very long!

I thought of adding a column to the ItemCache and have a comma delimited
string of location id's that it supports and use a Like statement in the
rowfilter to return a
view but I'm not sure if that would work well since the SQL statement to
load the ItemCache would become more complex and possibly slower than it
already is, and my experience with rowfilters is that its not the fastest.

Is there any other ways anybody can think of implementing this?

Thanks for the replies in advance

ben

Multiple Active Result Sets (MARS)
How to bend ADO.Net to my liking...
performance of single database vs multiple databases
Fill strongly type dataset from stored procedure
Looking for an alternative