Unclear where table scan is coming from

Dear all,

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.

Thanks,

Bart

Plan.pesession (2.5 MB)
avatar image By Bart- 1 asked Feb 10 at 09:03 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

3 answers: sort voted first

Hi Bart,

Could you post the C# code that generates this query? Don't need the entire class, just the LINQ expression.

avatar image By SQLSaurus ♦♦ 66 answered Feb 10 at 12:42 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Hey SQLSaurus!

In attachment, I added a small variant of the plan, just so the code would match 100% with the plan:

 snip

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.

plan2.pesession (196.5 kB)
avatar image By Bart- 1 answered Feb 10 at 04:32 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Thanks Bart,

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.

avatar image By SQLSaurus ♦♦ 66 answered Feb 10 at 08:05 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x554
x30
x7
x2

asked: Feb 10 at 09:03 AM

Seen: 33 times

Last Updated: Feb 20 at 07:52 AM