Entity framework is one of the most popular ORMs in software development, but it does not yet have a great reputation among SQL Server database professionals, especially those responsible for server performance. This collection of modules will identify some of the most common "real world" code smells and anti-patterns that keep EF-based applications from reaching optimal performance from the perspective of the SQL Server database. Many of these concepts could also apply to other relational data sources that Entity Framework is compatible with.
Currently an IT leader in Denver Colorado's financial sector Russ has focused on
database development, modelling, administration, and BI since 1997 across the Microsoft
stack. Russ is a passionate trainer and SQL community volunteer presenting regularly
at PASS SQL Saturday events and local user groups around the US.
Too Much Data Welcome to this Pluralsight course Entity Framework Database Performance Anti-Patterns. My name is Russ Thomas on Twitter @sqljudo, the web at sqljudo. com, and this is module two, Too Much Data. In this module, we'll talk about a couple of anti-patterns that are common to Entity Framework and LINQ development. Each has the effect of requesting too much data from SQL Server, and while it may be self evident why this is a problem, it's still useful to talk about some of the internal workings of SQL Server and what kind of effect pulling too much data can have on performance. We'll talk about page life expectancy, and how large data sets can have a negative effect on page life expectancy. We'll talk about IO latency in general, and then we'll talk about data set size can cause SQL Server to perform poorly in its selection of indexes during the optimization process.
Non Set-based Work Welcome to this Pluralsight course, Entity Framework Database Performance Anti-Patterns. My name is Russ Thomas on Twitter @sqljudo, and the web at sqljudo. com, and this is module three, Non Set-based Work. It's important to remember that SQL Server is a relational database. It's optimized for operations that utilize relational algebra and set-based theory. Now that may sound like a lot of scientific, hippy-dippy mumbo jumbo, so in this module, we will define what exactly that means, and then show how it applies for you in the real world, specifically within Entity Framework and Entity Framework development. We'll first discuss relational theory in general and why set-based workloads are so much better for a database performance than procedural approaches. We'll then demonstrate two Entity Framework anti-patterns that violate efficient set-based communication with SQL Server, the first, the way in which Entity Framework navigates entity relationships. While EF may traverse relationships, it is not per say relational. It often results in database communication that is far from set-based, and the second anti-pattern, nested LINQ statements, we'll see these in action, as well as a variety of other nested statements in general to identify the performance hit that these N+++ problems can cause in the real world.
Miscellaneous Items Welcome to this Pluralsight course, Entity Framework Database Performance Anti-Patterns. My name is Russ Thomas on Twitter @sqljudo, and the web at sqljudo. com, and this is the final module, Miscellaneous Items. Not all shops have a full-time database developer, or even a DBA. For many shops, the SQL Server installation is something that has been stood up with an off the shelf type approach. Plug it in, turn it on, and let Entity Framework do its thing. It's hoped that SQL Server will just take care of itself. Entity Framework does a decent job in many aspects, and it's obvious the designers are not completely oblivious to the needs of a high-performance database server. There are significant complexities, however, in building out an object oriented framework, an ORM, that can handle all communication with not one, but many types of relational and even some semi-relational databases. In this final module, we're going to take a look at performance considerations outside of coding techniques, and the T-SQL workloads that we've been focused on up to this point. We'll first tackle database server-side best practices, as well as physical database structures that you should have in place to best support your environment. This is an area where there are more than a few it depends type topics. In those cases, I'll give you some generalities that you can use as research points. Remember, however, this isn't a one-size-fits-all type of topic. After that, we'll take a look at some performance considerations on the Entity Framework application side itself, from the aspect of configurations, settings, and techniques that you should consider in a SQL Server environment.