Entity Framework Query Optimization

Question

I've got a couple of months experience working with Entity Framework and mostly writing a ton of data retrieval linq queries against it. I come from a heavy sql background, and am trying to optimize some of the sql for performance and readability if I'm trying to debug performance issues.

I am noticing some of the generated sql does things like this for a tableA with columns {col1,col2,col3}

select
    Extent1.col1
from
(
   select col1, col2, col3 from tableA
) AS Extent1

My question is, how do I prevent it from doing these useless derived tables, and instead just do

select col1 from tableA 

where it is needed? I can't seem to figure out why it sometimes does this and other times it doesn't...

Answer 1

Have you compared the actual query execution plans of the generated query against how you would optimize it? You might be surprised at the results, I know I was. And I gained a deep respect for the devs on the SQL server team who seem to be doing quite an excellent job at making what looks like a sub-optimal query perform just the same.

I'd be interested in hearing if your experience differs from mine; I stopped looking for ways to change the generated queries because for every query I tried looking at, there was no real difference in performance.

EDIT: My last statement isn't entirely true, there are definitely N+1 situations that you have to watch out for, and any batch operations (updates, deletes, and inserts of multiple records at the same time) aren't going to be even close in performance to writing a query by hand due to the nature of working with individual records. But the extraneous extents essentially get stripped out by the SQL Server query optimizer.