Should I do the Where clause in SQL or in LINQ?

Question

I have a method that can pass in a where clause to a query. This method then returns a DataSet.

What is faster and more efficient? To pass the where clause through so the SQL Server sends back less data (but has to do more work) or let the web server handle it through LINQ?

Edit:

This is assuming that the SQL Server is more powerful than the web server (as probably should be the case).

Answer 1

Are you using straight up ADO.Net to perform your data acccess? If so, then yes - use a WHERE clause in your SQL and limit the amount of data sent back to your application.

SQL Server is effecient at this, you can design indexes to help it access data and you are transmitting less data back to your client application.

Imagine you have 20,000 rows in a table, but you are only interested in 100 of them. Of course it is much more effecient to only grab the 100 rows from the source and send them back, rather than the whole lot which you then filter in your web application.

You have tagged linq-to-sql, if that's the case then using a WHERE clause in your LINQ statement will generate the WHERE clause on the SQL Server.

But overall rule of thumb, just get the data you are interested in. It's less data over the wire, the query will generally run faster (as long as it's optimised via indexes etc) and your client application will have less work to do, it's already got only the data it's interested in.

Answer 2

SQL Server is good at filtering data, in fact: that's what it's built for, so always make use of that. If you filter in C#; you won't be able to make use of any indexes you have on your tables. It's going to be far less efficient.

Selecting all rows only to discard many/most of them is wasteful and it will definitely show in the performance.