SQL Server - JOIN does not use optimal ordering in generated Entity Framework query
I am using Entity Framework to execute a rather complex query on my DB. The request consists of a .Union()
couple of others IQueryables
.
In an attempt to optimize the above query, I split various subqueries and tested them individually to measure performance, and found that one of them (let it be called joinRes
) is rather slower than the others.
joinRes
is a join between two (filtered) tables, where one of the tables has a huge number of records and the other has only a couple of them.
var smallDataSet = RepositoryForSmallDataSet.Where(smDs => ... );
var joinRes = smallDataSet.Join(largeDataSet,
smDs => smDs.SomeID,
bigDs => bigDs.SomeID,
(smDs, bigDs) => bigDs)
.OrderByDescending(bigDs => bigDs.SomeDate);
// ... IQueryable joinRes will be used in some .Union() operations with other IQueryables into a largeQuery IQueryable...
var result = largeQuery.Take(10).ToList();
Looking at the actual execution plan in SQL Server, I can see that the step Nested Loops
in the subquery joinRes
does not choose the optimal order ( smallDataSet
first and then largeDataSet
). If I add a hint OPTION (FORCE ORDER)
to the generated SQL, the additional query is much faster.
The problem is that I can't seem to find a way to add this hint through the Entity Framework and just move a complex query (remember, it joinRes
's part of a big complex) onto the stored procedure will be (this is a highly dynamically generated query and it will most likely need a lot of dynamic SQL).
Any suggestions on how to solve this problem?
EDIT:
Evaldas Bujnauskas took me on the right track with his answer. Poor performance was also caused byKey Lookup
a bunch of other JOINS caused by TPT inheritance mechanism (not mentioned in this question). Fixed indexes (based on SQL Server execution plan) and refactored inheritance instead
source to share
Are your indexes correct? Because it Key Lookup
indicates that your index does not cover the join condition, or it outputs columns that are not included in your index.
It was recommended to eliminate them (search). This is a very detailed article explaining how to do it.
Key searches occur when you have a pointer to a table, but your query requires additional columns that are not in that index. This forces SQL Server to go back and fetch the extra columns.
source to share