Today I’d like to dig into an area from the first post a little deeper.

FOR EACH LOOPS = CURSORS (ANOTHER LOOK)

There is a pretty cool feature of Entity Framework.  When you declare an object mapped to a table that has foreign keys, you automatically get references to those related entities.  Navigating relations in this way really is technically very clever – hats off to the folks who worked this code out at Microsoft.  Unfortunately it can have a significant hidden cost when buried within loops.

As I’ve complained before, Entity Framework abstracts the TSQL workloads that get passed back to the SQL Server.  Often, this means that the developer might not be aware of how her/his code is being received by the server.

What follows is a perfect example.

In the AdventureWorks sample database the SalesOrderDetail table is a child table of SalesOrderHeader.  So, if I wanted to see all the product id’s that a single customer ordered – I would need to join Customer table to SalesOrderHeader table and then join the SalesOrderHeader table to the SalesOrderDetail table.  In raw TSQL this is a pretty simple join, but in Entity Framework I may be tempted to write it like this and let EF navigate the relationships.

  AdventureWorks2012Entities DBContext = new AdventureWorks2012Entities();

  var custorder = from o in DBContext.SalesOrderHeaders
                  where o.CustomerID.Equals(customer)
                  select o;

  foreach (var ordheader in custorder)
  {
      foreach (var orddetail in ordheader.SalesOrderDetails)
      {
          Console.WriteLine(orddetail.ProductID.ToString());
      }
  }

In my previous post I said to avoid LINQ within FOR EACH loops.  As you can see in this example however I am not issuing a LINQ statement – so, it’s not a clear violation of that code smell.  What EF is doing is navigating relationships within the entity mappings to find related objects to the original LINQ statement.  Like I said, pretty cool, but what is the cost?

Take a look at this code in a trace.  In the following image you can see the console result of my code.  33 product IDs were returned.  To obtain the ProductID by navigating the relationships, Entity Framework issued 77 individual interactions with SQL Server.  33 of these statements were select * statements against SalesOrderDetail.  All but one of the columns from each select statement are never even used.  In short, it is basically a cursor.

If you take one of these statements and run manually you find that each statement accounts for 600 bytes.  600 bytes x 33 runs = ~19 KB.

What if we’d of written it like this?

  AdventureWorks2012Entities DBContext = new AdventureWorks2012Entities();

  var custorder = from d in DBContext.SalesOrderDetails
                  join o in DBContext.SalesOrderHeaders
                  on d.SalesOrderID equals o.SalesOrderID
                  where o.CustomerID.Equals(customer)
                  select new {d.ProductID}; // projection

  foreach (var prodord in custorder)
  {
      Console.WriteLine(prodord.ProductID);
  }

We’ve avoided burying EF constructs within the FOR EACH loop and returned to using LINQ in a set based way.  The resulting trace now looks like this.  Notice I’m also using projection to get away from the select * problem.

Further the total number of bytes retrieved from the server is now 339 BYTES.  That’s one single select statement pulling 339 BYTES vs 33 individual statements pulling 19 KB.  That is 339 vs 19000 if you put them both in terms of bytes.  The second run pulls 2% of the total data retrieved from the first run!!!  In any type of scaled environment that adds up!!

The moral of the story is this.  If you are using Entity Framework to reduce lines of code and work in an object oriented / strongly typed environment – great, I support you.  But, to get it to work quickly and efficiently don’t ever forget that SQL Server still wants set based workloads.

Entity Framework can send set based work – but you have to know how to help it out.  The purpose of this post also isn’t to dissuade you from letting EF navigate relationships – just know what the cost is – ESPECIALLY if you’re doing work inside loops that may scale to hundreds, thousands, millions of iterations.

Want your DBA to stop complaining about Entity Framework?  Make the case by writing EF code that sends better TSQL.

In my next Entity Framework performance post we’ll take a look at how EF can cause SQL Server to make poor index decisions in the query plan – and what can be done about that.

Image courtesy of: http://paul-barford.blogspot.com/2012/12/focus-on-uk-metal-detecting_3.html  creative commons

ATTENTION!!  Some time after writing this original post I joined Pluralsight as an author.  My very first course was a 2 hour collection of modules on this very topic.  If you have a Pluralsight subscription I highly recommend the much deeper dive into EF performance that you can find here.  Entity Framework Database Performance Anti-patterns

An ORM (object relational mapper) can make the task of developing code against the data tier much easier for the Software Engineer.  One of the more popular ORMs is Entity Framework.  EF has come a long way.  Each release gets a little better, in terms of performance however, is still far from perfect.  EF can be a real dog.

Slow or not, the reality remains.  The typical Software Engineer would rather write classes, functions, and code in C#, F#, Java, or other language of choice over TSQL.  They crave strongly typed environments, hip design patterns, diet coke, and bacon.  TSQL to them is like assembler of old.  Fast execution? You bet!  Also archaic, slow to develop, and often unnecessary (their words, not mine).  Those that do enjoy TSQL end up as full time DBAs, data architects, or consultants.

As a DBA working in a world where EF resides these are the 5 things I think both DBAs and Software Engineers should understand about EF.

1. Size Matters – Maybe More Than Ever

A big complaint against Entity Framework is that it brings back more data than it actually needs.  If this problem is compounded by improper column sizes and data types, you can easily be drawing exponentially more data than needed to satisfy business requirements.  If this extra data causes a query to have to go to disk vs memory, the performance impact can be orders of magnitude above that.

Software Engineers should understand this concept even if they never plan to write another TSQL statement again.  Code First doesn’t relieve anyone the responsibility of a sound understanding of relational architecture and data type/length choices.

Code first is a concept in Entity Framework where an SWE can design a table structure declaratively within the language (similar to the way you would declare variables, classes, and objects).  EF will create the database objects it’s self.  This can make the database development process more comfortable for the developer but abstraction often masks architectural weaknesses.

2. Don’t Query More Than You Need – Understand Projection

Even if your data types and column sizes are perfectly scoped and sized Entity Framework makes it simple to over-work your database server by constantly requesting more columns than needed.  DBAs regularly preach that users should avoid ” select * “.  Googling Entity Framework code samples or perusing MSDN however gives the average EF programmer an endless supply of code that is guilty of this very thing.  Consider the following EF C# code taken directly from an MSDN example on querying with EF.

   // Create the query. 
   var studentQuery =
    from student in students
    where student.Scores[0] > 90
    select student;

   // Execute the query. 
   foreach (Student student in studentQuery)
   {
      Console.WriteLine("{0}, {1}", student.Last, student.First);
   }
   // Output: 
   // Omelchenko, Svetlana 
   // Garcia, Cesar 
   // Fakhouri, Fadi;

If you were to trace this connection on the SQL Server end you’d see the query selecting every single column even though the sample business case only cares about first and last name.  This can be overcome with projection.

Projection is basically taking the members of one object and projecting them as another, often smaller, object.  Were the sample EF code above to use projection, it would be something more like this:

   var studentQuery =
    from student in students
    where student.Scores[0] > 90
    select new {fname = student.First, lname = student.Last};

The resulting generated TSQL would select lname, fname only.  EF leverages the projection and recognizes nothing else is desired when generating the TSQL that is ultimately passed to the database server.  (Image in tip 3 shows this as well.)

3. Obtaining TSQL for Tuning

To truly performance tune a query, the DBA or DBE doing the tuning isn’t going to want to look at LINQ syntax.  Converting this to the actual TSQL has gotten much easier in modern versions of EF.  Developers don’t have to rely on trace alone.

To see, for example, the TSQL that a LINQ query would generate – the dev would simply need to do a breakpoint, screen print, or log the dataset variable via it’s .ToString() method. .Net returns the EF generated TSQL as shown here.

Older EF versions could do this as well, but not through the ToString() method.  While doable, it wasn’t always very straight forward, requiring different class objects and specific circumstances to perform.

Once the DBA has tuned the TSQL statement it’s decision time.  Implement it back into EF in the form of an improved LINQ query or hit the connection with raw TSQL.  EF supports communicating in raw TSQL or calling procs in these edge cases when desired performance can’t be reached any other way.  If the decision is to turn the TSQL back into LINQ, there is a translation tool that makes this fairly easy.  It’s called StackOverflow.com.

4. LINQ Inside For Each Loops = Cursor

If there is one convention within TSQL where developers and DBAs often butt heads, it’s the cursor.  Cursors are a TSQL indicator to the DBA that someone, somewhere, gave up.  To many developers however, iterative loops is just how things are done.  SQL Server works best in sets.  Period.  Looping logic “row by agonizing row” is a sure fire recipe for performance complaints.  EF is getting better about sending set based work, but one area that still essentially creates a client side cursor is nesting LINQ Queries inside For Each (or other type) loops.

Entity Framework abstracts TSQL, so the developer often doesn’t know exactly what their code is doing on the SQL Server end.  It may be up to the DBA then to remind developers to avoid building LINQ queries inside of loops.  Notice how the following code looks in a trace for example.  (Also notice how lack of projection causes an even bigger problem by selecting every single column).

   var products = from s in DBcontext.SalesOrderDetails
                  group s by s.ProductID into g
                  select new { ProdID = g.Key, 
                  AveragePrice = g.Average(
                  p => p.UnitPrice * p.OrderQty) };

   foreach (var product in products)
   {
     if (product.AveragePrice > 1000)
     {
       // try not to do this.  linq query in loop = slow
       var rec = (from u in DBcontext.Products  
                  where u.ProductID == product.ProdID
                  select u).Single();

       Console.WriteLine(rec.Name);
     }
   }

Causes this:

A better approach is to structure your LINQ query such that it can obtain the data necessary outside of the loop in a single set based approach.  You can then access the local object containing cached data within the loop.  Further, the example above should be using projection.

Pssst, it isn’t only LINQ statements that can cause this cursor type behavior.  In this follow up post I demonstrate how objects can do it too.  Objects are just more sneaky about it.

5. Lazy Loading vs Eager Loading

Last tip.  The DBA and SWE should both understand the basic concepts of Lazy Loading vs Eager Loading and how to make EF use one or the other as the situation dictates.

Lazy Loading will only request a subset of the total data potentially required.  When implemented right, it takes a similar form to an OFFSET and FETCH statement.  Lazy Loading isn’t always bad.  If you were looking for a new phone case – you wouldn’t want to wait while Amazon returns every single phone case in the world – you’d want 10-50 at a time to look at.  So too would the DB server.  Chances are you’ll never get to the end.  Don’t pull more data than you need.

Lazy Loading CAN cause performance problems however when entire sets are needed in a process and the application is constantly pestering the SQL Server for additional rows.  Simply put, this is akin to just another cursor.  I’ll point you to the interwebs for more on this topic, but if you’re in an EF shop, both the DBA and SWEs should understand the concept well.

Remember