Entity framework performance tuning

Question

We are using entity framework to query a SQL server database. The LINQ expression is IQueryable. This query takes about 10 seconds to execute. If this were in a stored procedure, I would play around with the query to make it more efficient. However if I am using IQueryable, does entity framework itself decide on how to build an efficient query or do I have to play around with the linq expression and improve performance with trial and error?

Answer 1

does entity framework itself decide on how to build an efficient query

EF will always automatically determine how to build the query, and sql -server also optimizes queries automagically.

do I have to play around with the linq expression and improve performance with trial and error?

You can try to play with the queries but typically minor changes won't affect performance (in terms of ordering of expressions)

You can always use SQL Profiler to watch what EF does and see how efficient the query is. If it takes you long you can rerun the query in SSMS and turn on Include Actual Execution Plan and determine where the query is slow.

Answer 2

If you're using EF 6, you can enable logging quite easily. You can then inspect what each call is doing. I would start there. MSDN EF6 Logging

Are you able to share a bit more about your query and the result size?

Answer 3

As a database developer more than a C# developer, and with very limited exposure to Entity Framework specifics, I can say:

My understanding is that Entity Framework decides how to build a query, probably without much ability to understand efficiency. There might be some things you can do better or worse in your Linq query or Lambda expression, but for the most part you probably aren't going to be able to really tweak the query. This is a main downside of using an ORM, at least from the perspective of the DBAs who get paged in the middle of the night when the server crawls to a halt and they can't do anything to fix the query and it's not like you can always just add an index ;-).

I can also say that you do have the option in Entity Framework to specify a Stored Procedure for each of the DML operations so that if you really needed to do something better with this particular query, then create a Stored Procedure for just this one operation and point the EF object to it for SELECT but allow EF to build the query for INSERT / UPDATE DELETE.

Does that help?