Efficient query and execution plans

Question

I have a simple entity framework query. It uses grouping:

source.GroupBy(x => x.Key)
.Select(x => x.Count(z => z.IsDefaultValue == false) > 0
    ? x.FirstOrDefault(z => z.IsDefaultValue == false)
    : x.FirstOrDefault()
);

Execution plan for it looks like this:

Plan 1

Then I change the query:

source.GroupBy(x => x.Key)
.Select(x => x.Any(z => z.IsDefaultValue == false)
    ? x.FirstOrDefault(z => z.IsDefaultValue == false)
    : x.FirstOrDefault()
);

Now I use Any instead of Count. It's plan looks like this: Plan 2

My question is: what query should I use? What query is more efficient?

I don't understand anything about execution plans :( What important information do you see on these execution plans?

EDIT: Please drag pictures in a new tab, it will be more readable.

Can't really say much from the image. Would need the Actual execution Plan XML. The bottom one has double the number of scan operators and looks as though SQL Server costs it as more expensive though (based on the fact that the sort after the CI clustered scan will be a constant cost and appears as 30% of the cost of the first one and 18% of the second). Look like you are probably missing useful indexes unless the tables are very small. — Martin Smith, Jun 11 '12 at 16:29
What about EF's Any() vs Count() > 0 ? Which is more efficient? Sorry, I don't want to share execution plan XML - it is big and ugly. — denis_n, Jun 11 '12 at 16:57

score 1 · Answer 1 · edited May 23 '17 at 12:30

My question is: what query should I use? What query is more efficient?

To find the answer to that, I would simply take both of the SQL queries that have been generated (you can see the full query in the plan's xml) and run them in one SQL Server Management Studio query window together, with the "Include actual execution plan" option on.

The "Query cost (relative to the batch)" on the resulting plan will then tell you which one is more efficient on your actual schema/data.

What about EF's Any() vs Count() > 0 ? Which is more efficient?

In general if you just want to know if any of the "things" match your criteria, then it's often suggested you use Any() instead of Count() > 0 and let LINQ worry about constructing the most efficient way. In this use case I would just check the SQL and see.

See answers like this

For just IEnumerable<T>, then Any() will generally be quicker, as it only has to look at one iteration.

Efficient query and execution plans

1 Answers1