0

I run the same query using EF 6 and every time I see that GetExecutionPlan is running:

enter image description here

It is seen that GetExecutionPlan takes 93% of time and if I expand the method further I see that it is not taken from the cache but Compile method is run. Also I see that ObjectQuery.EnablePlanCaching is set to true, like it is by default.

Why is it not using the plan execution cache but compiling the query each time?

Centro
  • 3,892
  • 2
  • 25
  • 31
  • Well - is it *really* issuing the **identical** SQL query each time? And I mean *identical* down to the last comma or space. If not -> then there cannot ever be any reuse....... only if the SQL statement is ***absolutely identical*** to another one used previously, there is a chance of an execution plan re-used from cache – marc_s Jan 14 '18 at 16:03
  • 1
    Actually it has two parameters and values of course can be changed, but the caching should work ok with parameters. As to the generated query text - it is identical. – Centro Jan 14 '18 at 16:12
  • Yes, if the queries are properly parametrized (and EF typically does that just fine), then the query text will be identical (with parameters) and only the parameter values change. That should be just fine for caching (and reusing) execution plans..... odd...... – marc_s Jan 14 '18 at 17:19
  • what you may experience is parameter sniffing. However, EF should reuse even subtrees of execution plans, so for each compilation to last a long time the parameter have to change the query structure quite a bit. – DevilSuichiro Jan 14 '18 at 20:28
  • My query has a lot of expression trees. I guess it has problems to identify whether plan caching should be used when there are method calls in expressions. – Centro Jan 15 '18 at 15:34
  • Now I see that even simple Contains method in an expression, e.g. u => allowedUsers.Contains(u) makes it not to cache. When I remove Contains I see it is cached. So my guess is it is only working for simple conditions in expressions without method calls. – Centro Jan 15 '18 at 15:52
  • 1
    One more comment, Contains is not supported in this quite intelligent scenario when the collection can vary from time to time, but which is intuitive that on EF level it could work. For more information https://stackoverflow.com/questions/25228362/how-to-avoid-query-plan-re-compilation-when-using-ienumerable-contains-in-entity – Centro Jan 15 '18 at 20:29
  • I came from a development background but now i'm working as a DBA i have have seen some of the queries EF generates and how the DB engine handles them. You may need to tune your EF code. Check if can simplify code.. Also check out https://thecodegarden.net/entity-framework-best-practices/ .You may want to look at your code and make a decision as to if this would be better done as a stored procedure within sql server. – CPearson Jan 16 '18 at 04:37

0 Answers0