Grouping data in memory using Linq To Objects or using native SQL -- WHICH IS FASTER?

Question

I noticed that LINQ to Objects has a GroupBy method. In light of this, I was wondering if anyone can build a case for group list of objects in memory using LINQ vs having SQL Server perform the grouping?

Answer 1

On a large data set with properly indexed tables, SQL Server will be faster. Every time. Hands down. For smaller datasets, you might not even notice a difference.

Answer 2

Some reasons you might want to group using LINQ on the client are:

1. Your objects are already in memory.
2. You might want to group by different keys at different times during the lifecycle of the data, and return trips to the database might be costly.
3. You might want to group by something that's inconvenient for SQL Server to calculate, such as data.GroupBy(d => DoSomethingComplicatedWith(d)).

Answer 3

If you are dealing with a small set of data, performance on the client is not a concern, and going back to the database again to get the data into the format you want is not an option (or an undesirable option), then doing it in memory is fine.

In all other cases, your best option would be to let the database do this work, as it is optimized for exactly this kind of operation.

Answer 4

There are (not too few) situations where your objects are not contained in a database.