Entity Framework 4.2 exec sp_executesql does not use indexes (parameter sniffing)

by

tl;dr

update statistics

We had a delete query with one parameter (the primary key) that took ~7 seconds to complete when called through EF and sp_executesql. Running the query manually, with the parameter embedded in the first argument to sp_executesql made the query run quickly (~0.2 seconds). Adding option (recompile) also worked. Of course, those two workarounds aren’t available to us since were using EF.

Probably due to cascading foreign key constraints, the execution plan for the long running query was, uhmm…, huge. When I looked at the execution plan in SSMS I noticed that the arrows between the different steps in some cases were wider than others, possibly indicating that SQL Server had trouble making the right decisions. That led me to thinking about statistics. I looked at the steps in the execution plan to see what table was involved in the suspect steps. Then I ran update statistics Table for that table. Then I re-ran the bad query. And I re-ran it again. And again just to make sure. It worked. Our perf was back to normal. (Still somewhat worse than non-sp_executesql performance, but hey!)

It turned out that this was only a problem in our development environment. (And it was a big problem because it made our integration tests take forever.) In our production environment, we had a job running that updated all statistics on a regular basis.

More Related Contents:

  1. ASP.NET MVC 2.0 Implementation of searching in jqgrid
  2. Code First: Independent associations vs. Foreign key associations?
  3. What’s the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()?
  4. Unique Key constraints for multiple columns in Entity Framework
  5. Entity Framework 6 Code first Default value
  6. Database first create entity framework 6.1.1 model using system.data.sqlite 1.0.93
  7. EF Code First “Invalid column name ‘Discriminator'” but no inheritance
  8. Create a Dropdown List for MVC3 using Entity Framework (.edmx Model) & Razor Views && Insert A Database Record to Multiple Tables
  9. Equivalent for .HasOptional in Entity Framework Core 1 (EF7)
  10. How to exclude one table from automatic code first migrations in the Entity Framework?
  11. How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?
  12. Entity Framework : How do you refresh the model when the db changes?
  13. Entity framework code first creates “discriminator” column
  14. EF Migrations: Rollback last applied migration?
  15. Keyword not supported: “data source” initializing Entity Framework Context
  16. Entity Framework Data Annotations Set StringLength VarChar
  17. EF 4.1 loading filtered child collections not working for many-to-many
  18. How to map Table per Type (TPT) inheritance in Entity designer?
  19. EF Code First foreign key without navigation property
  20. Nullable property to entity field, Entity Framework through Code First
  21. LINQ to Entities for subtracting 2 dates
  22. Entity Framework Code First Using Guid as Identity with another Identity Column
  23. AutomaticMigrationsEnabled false or true?
  24. ADO.NET Entity Framework tutorials [closed]
  25. Entity Framework Code First Many to Many Setup For Existing Tables
  26. Compose queries across Entity Data Models
  27. What is the best way to compare two entity framework entities?
  28. Changing Inherited Types in Entity Framework
  29. Why is my Entity Framework Code First proxy collection null and why can’t I set it?
  30. Why does the entity framework need an ICollection for lazy loading?

Leave a Comment