CODEWITHSUNDEEP
c#.netperformancelinqlinq-to-objects
Budda
Budda

Reputation: 18343

Linq performance: does it make sense to move out condition from query?

There is a LINQ query:

int criteria = GetCriteria();
var teams = Team.GetTeams()
    .Where(team=> criteria == 0 || team.Criteria == criteria)
    .ToList();

Does it make sense from performance (or any other point of view) to convert it into the following?

var teams = Team.GetTeams();
if (criteria != 0)
{
    teams = teams.Where(team => team.Criteria == criteria);
}
teams = teams.ToList();

I have a solid set of criteria; should I separate them and apply each criteria only if necessary? Or just apply them in one LINQ query and leave up-to .NET to optimize the query?

Please advise. Any thoughts are welcome!

P.S. Guys, I don't use Linq2Sql, just LINQ, that is a pure C# code

Upvotes: 1

Views: 203

Answers (2)

Chris Pitman
Chris Pitman

Reputation: 13104

First, it is important to understand that LINQ actually comes in several varieties, which can mostly be divided by whether they use expression trees or compiled code to execute the query.

Linq2Sql (as well as any Linq provider that converts the query into another form to perform the query) use expression trees so that the query can be analyzed and converted into another form (often SQL). In this case it is possible for the provider to modify the query, potenitally performing some level of optimization. I am not aware of any providers that currently do this.

Linq to Objects uses compiled code, and will always execute the query as written. There is no opportunity for the query to be optimized other than by the developer.

Second, all Linq queries are deferred. That means that the query is not actually executed until an attempt to get results. A side effect of this is that you can build a query in several steps, and only the final query will be executed. The second example in the question still results in only one query being executed.

If you are using Linq2Sql then it is likely that both queries have roughly similar performance. However, the first example extended to handle many criteria could potentially lead to a poor overly general execution plan which ultimately degrades performance. The trimmed query does not run this risk, and will generate an execution plan for each real permutation of criteria.

If you are using Linq to Objects, then the second query is definitely preferable, since the first query will execute the predicate passed to Where once for every input even when the condition always returns true.

Upvotes: 5

Habib
Habib

Reputation: 223267

I think the 2nd option is better because it is checking the criteria value only once. Whereas in the first query , criteria is being checked for every row.
But I also believe that you will not get any significant performance gain from it. It may give you better results on tables with a high number of records (theoretically)

Upvotes: 3

Related Questions