Should the order of LINQ query clauses affect Entity Framework performance?

by

The core of the question is not “why does the order matter with LINQ?”. LINQ just translates literally without reordering. The real question is “why do the two SQL queries have different performance?”.

I was able to reproduce the problem by only inserting 100k rows. In that case a weakness in the optimizer is being triggered: it does not recognize that it can do a seek on Colour due to the complex condition. In the first query the optimizer does recognize the pattern and creates an index seek.

There is no semantic reason why this should be. A seek on an index is possible even when seeking on NULL. This is a weakness/bug in the optimizer. Here are the two plans:

enter image description here

EF tries to be helpful here because it assumes that both the column and the filter variable can be null. In that case it tries to give you a match (which according to C# semantics is the right thing).

I tried undoing that by adding the following filter:

Colour IS NOT NULL AND @p__linq__0 IS NOT NULL
AND Size IS NOT NULL AND @p__linq__1 IS NOT NULL

Hoping that the optimizer now uses that knowledge to simplify the complex EF filter expression. It did not manage to do so. If this had worked the same filter could have been added to the EF query providing an easy fix.

Here are the fixes the I recommend in the order that you should try them:

  1. Make the database columns not-null in the database
  2. Make the columns not-null in the EF data model hoping that this will prevent EF from creating the complex filter condition
  3. Create indexes: Colour, Size and/or Size, Colour. They also remove them problem.
  4. Ensure that the filtering is done in the right order and leave a code comment
  5. Try to use INTERSECT/Queryable.Intersect to combine the filters. This often results in different plan shapes.
  6. Create an inline table-valued function that does the filtering. EF can use such a function as part of a bigger query
  7. Drop down to raw SQL
  8. Use a plan guide to change the plan

All of these are workarounds, not root cause fixes.

In the end I am not happy with both SQL Server and EF here. Both products should be fixed. Alas, they likely won’t be and you can’t wait for that either.

Here are the index scripts:

CREATE NONCLUSTERED INDEX IX_Widget_Colour_Size ON dbo.Widget
    (
    Colour, Size
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Widget_Size_Colour ON dbo.Widget
    (
   Size, Colour
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

More Related Contents:

  1. Linq: help me to make this work
  2. How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?
  3. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  4. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  5. Entity Framework/Linq EXpression converting from string to int
  6. Filter/Search using Multiple Fields – ASP.NET MVC
  7. How to convert an expression tree to a partial SQL query?
  8. EF 6 – How to correctly perform parallel queries
  9. Entity-framework code is slow when using Include() many times
  10. Join and Include in Entity Framework
  11. The LINQ expression could not be translated and will be evaluated locally
  12. Dynamic query with OR conditions in Entity Framework
  13. Max return value if empty query
  14. Remove item from list based on condition
  15. How to do an “in” query in entity framework?
  16. How to Specify Primary Key Name in EF-Code-First
  17. Keep Getting ‘The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities’ Exception
  18. Linq Query keeps throwing “Unable to create a constant value of type System.Object….”, Why?
  19. How to include() nested child entity in linq
  20. LINQ Query to Convert string to datetime
  21. How to get the index of an element in an IEnumerable?
  22. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  23. High memory consumption with Enumerable.Range?
  24. How to Get XML Node from XDocument
  25. Use linq to generate direct update without select
  26. Received an invalid column length from the bcp client for colid 6
  27. Get List element position in c# using LINQ
  28. Linq style “For Each” [duplicate]
  29. Entity Framework Multiple Object Contexts
  30. How do I connect to a SQL database from C#?

Leave a Comment