optimize entity framework generated sql

rdumont 2016-11-23 13:46:52

We use Entity Framework (EF) to generate most of our application sql, we convert particularly complex or poor performing sql to stored procedures. This particular sql is used for updates and is also higher maintenance so converting to a SP is not viable.
The end result is 4 rows yet the plan diagram highest costs, Top Operations, and Table I/O show way too many rows being read.
We are not sure how to modify EF to produce more efficient SQL.
It works fine most of the time, primarly just this sql giving us problems.

Vlady Oselsky 2016-11-28 15:16:58
This is a typical nightmare that I came to expect from applications generating SQL. Instead of trying to optimize what it gave you, I would start over by understanding what is a purpose of this code. Once you know the purpose then you start gathering the pieces you need to accomplish it. Often times (which I'm willing to guess is the case this time) EF generates code that gets a whole lot of information that has absolutely no value to the operation. You should yourself a question of what you are trying to update and how can you do it with least amount of code.