Unclear where table scan is coming from
We use Entity Framework in our C# codebase as an ORM mapper, generating SQL from C# expressions. The plan in attachment is generating a huge clustered index scan, and we're really scratching our heads on what the underlying cause is. It's extra difficult since we're using an ORM mapper, but if someone can help us out on why this is the case on database level, we might be able to translate back to our ORM.
Any help would be greatly appreciated, we have been struggling with this for a while.
Could you post the C# code that generates this query? Don't need the entire class, just the LINQ expression.
In attachment, I added a small variant of the plan, just so the code would match 100% with the plan:
Which in turn will give the plan in attachment, which also still has a big index scan on AssaySamples in it. This table is huge in some databases, because of which memory is being pressed and our SQL Azure instances start generating time outs.
I've run into a similar situation before. The query becomes very complicated due to all the different operations in the LINQ expression.
The way I solved this was to break the linq expressions apart, so that I was dealing with smaller sets of data in memory, and simpler queries on the back end.
I see you are trying to avoid some of the deferred execution problems by converting to lists, but it may just not be enough. The simpler you can get each part of the query the better.
Breaking things down, you'll eventually get to smaller queries that are much easier to tune with indexes.