How to pull a SQL Server table into memory in order to run queries against it

Question

I'm running > 50K simple selects on a table in sql server and I'd really like to speed it up. I've read that you can pull the data into memory and use something like a dataview to query against.

Is there a way to use something in Linq to end up with -

var dv = CreateTheDataViewFromDatabase();

var result = dv.Where("CategoryId=2 And Uncle='bob'");

And would this be a lot faster than querying the database so many times? I'm concerned there's no index on the dataview so the gains of having it in memory might be lost.

Answer 1

I expect the fastest way to do this will be to generate a single query that returns results, in order, for the entire set of "50K simple selects", such that you can run the whole thing as a single query and iterate over the results sequentially.

The reason for this is that when you pull the table down to memory, you lose any index information that might have been available in sql server to help query the results. Of course, the penalty is that your query is returning 50K queries worth of data, but if you do it right vis a vis a datareader vs a data table, you should be able to avoid keeping unnecessary information in memory on the client.