Which is faster: filtering a DataSet in-memory or returning a result set from SQL Server?

Question

I'm working on a pretty large table, (800k records and climbing) and I'd like to filter said table. The thing is, the table is stored in SQL Server. So, I was wondering, would a

SELECT * FROM table WHERE condition1=true

query be faster than loading the table to a typed DataSet and using DataRow.Find() then sending all those to another DataTable?

I'm guessing yes, but I'll ask anyway.

Answer 1

As long as your SQL server is not paging because of RAM starvation the SQL Server should always be faster than loading the whole table via network and then filtering locally...

Answer 2

You should definitely do it in SQL Server because:

1. That's one of the things a database server is designed to do and is good at. If the condition always involves the same columns then you might want to consider adding indexes to speed up the lookup even more.
2. Performance wise it is always faster to load less data
3. Your memory consumption is going to explode when you are trying to load the whole thing into memory. Especially when the table is growing unbound. It might work ok now but can kill you unexpectedly later.