I have a simple EF implementation in which I retrieve ~20K records and include two child entities:
using (InsightEntities context = new InsightEntities())
.Include(x => x.Division)
.Include(x => x.Division.Company)
.OrderBy(x => x.ID)
When I profile the EF call via SQL Profiler, the duration of the SQL command is ~1.2s, even on subsequent calls. But if I copy and paste the same SQL that EF is generating and run it directly through SSMS, the duration is half that.
Below is a screenshot of EF calls (red ovals) and direct calls (green ovals):
I understand that EF does a lot of work mapping data to objects, interpreting relationships, and so on, but why would the query alone take twice as long as the same query run directly? Are there changes to the default EF connection string that might optimize the queries?
(I should add that the query itself is completely optimized with indices for all foreign keys.)