none
Why is Entity Framework so slow with join statements? RRS feed

  • Question

  • I have an EF 6 query that has about 6 linq join statements in it.  When I step through my code with the debugger, I can see that the query statement takes roughly 6 seconds to run.  With a SQL trace, I can tell that the actual query, which looks like I would expect it to, takes 0 ms and returns 0 rows.  I removed the join statements 1 by one.  With each join statement removed, the EF statement took 1 second less to execute.  The select portion of the query never changed, only the number of joins.

    My question is, what is happening?  What is EF doing that it requires so much time to process this query?  Oddly, it looks like most of this time is actually spent after the SQL execution is already finished, so the time is not generating the query, but whatever EF is doing afterward.

    Friday, July 17, 2015 2:43 PM

All replies

  • Hello Eric3333,

    For each time starting the application, Entity Framework would have a strategy named cold query for its first execution of each linq query:

    https://msdn.microsoft.com/en-us/data/hh949853.aspx?f=255&MSPPError=-2147217396#2

    It will cost quite a few time to generate the actual sql query statement from the linq query in the whole execution.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 20, 2015 2:32 AM
    Moderator
  • My question is, what is happening?  What is EF doing that it requires so much time to process this query?  Oddly, it looks like most of this time is actually spent after the SQL execution is already finished, so the time is not generating the query, but whatever EF is doing afterward.

    What is doing afterwards is object materialization, and it takes time to do it.

    Monday, July 20, 2015 2:54 AM
  • This is not a matter of a cold query.  I experience the slow query, then manually drag the cursor back up to execute the exact same line of code again in the debugger, and it does not speed up by even one second.
    Monday, July 20, 2015 12:19 PM
  • Should object materialization take time if there are no objects that it has to create?  The query returns 0 rows.  Also, the select statement is not altered between my 6 test runs, the only thing I removed where the joins.  If the number of joins is contributing to the cost of materialization, then I do not understand what materialization is.
    Monday, July 20, 2015 12:22 PM
  • Should object materialization take time if there are no objects that it has to create?  The query returns 0 rows.  Also, the select statement is not altered between my 6 test runs, the only thing I removed where the joins.  If the number of joins is contributing to the cost of materialization, then I do not understand what materialization is.

    Maybe, the link will help you.

    https://msdn.microsoft.com/en-us/data/hh949853.aspx?f=255&MSPPError=-2147217396

    Monday, July 20, 2015 1:52 PM