Demystifying Performance With a Specialized Entity Framework Profiler
Using Entity Framework Profiler to ensure adequate database access, performance and visibility
Join the DZone community and get the full member experience.Join For Free
If you use Entity Framework in your code, this article is for you. Like most people, you probably wouldn't go to the grocery store several times, buying one item at a time. Why not just go once and buy them all? In the case of buying groceries it's a no-brainer, but what about database accesses? Is it possible that we are unaware of costly and underperforming queries in our code? Yes, of course.
Applications that use Entity Framework often contain dozens of projects and simply have too many moving parts to effectively monitor their database interactions. Also, while Entity Framework is very convenient to use, the abstraction it provides makes it hard to trace SQL server queries and identify inefficient ones.
For example, look at this trivial query:
Entity Framework code that loads a blog with all its posts and comments.
Can you imagine that behind the scenes it unfolds to this mammoth code resulting in a Cartesian product?
The generated code when using Entity Framework (see the full code here).
With potentially thousands of such queries in our code base, what are the odds that they are all well-optimized? And even if the current performance is reasonable, can we tell how all these queries will perform once we scale the application or apply changes to the code? Probably not. Database interactions are especially prone to poor visibility and underperformance when the database layer is neglected by the developers, which is not uncommon.
Gain Visibility Into Your Application's Database Interactions
Improving the visibility and performance of database interactions calls for a specialized profiler. The desired profiler would not only send an alert when underperforming queries are executed, but could also provide valuable context, such as application activity and business actions relevant to each query. Indeed, the lack of context is one of the reasons why developers are not satisfied with the database's built-in profiler, which can also be complex to use and operate.
In general, the desired profiler should not just litter the report with a pile of queries, but, rather, organize the data and make it intelligible. For example, showing how many queries were used to generate a page is much more useful as a metric than just providing out-of-context information for each individual query. Using the grocery store analogy, the desired profiler would alert us to the fact that making one cake took six visits to the store, providing context that can be applied to reduce store visits. In contrast, the built-in database profiler would only show that a lot of cake ingredients were bought, entirely missing the separate trips and their inherent latency.
Preferably, the alerts could also take the developer to the exact location in the code when they clicked on said alert. This feature is also not supported by built-in database profilers, which are aimed at DBAs rather than developers.
Valuable context: clickable statements that jump to the code from which they originated.
Developers would also prefer a user-friendly GUI to ensure a short learning curve and high productivity. Essentially, the feature set and UI of the desired profiler should be designed for developers from the ground up, unlike the built-in database profiler.
One profiler that was created with developers in mind and provides all these features is the Entity Framework Profiler by Hibernating Rhinos.
The Entity Framework Profiler's GUI and feature set have been designed for developers from the ground up.
Save on Azure Costs by Handling Expensive Queries
Moving the application to Azure has many benefits, but it is not without its downsides:
- Database interactions now cost money and are subject to Azure's pricing model.
- Underperforming queries can introduce latency and damage the user experience.
- The visibility into the database layer is greatly reduced when moving to the cloud.
Luckily, the Entity Framework Profiler supports Azure profiling integration, allowing developers to profile their Entity Framework queries from Azure Functions. Following a simple initialization of the profiler, profiling events can be uploaded as blobs to an Azure container. The profiler can then seamlessly integrate with the profiled application in a distributed fashion using the Azure Blob storage, and queries can be displayed in real-time.
Configuring the Entity Framework Profiler to listen to the enqueued messages.
Optimization Recipes for Developers by the Entity Framework Profiler
On top of providing alerts and insights, the Entity Framework Profiler also shows us how to optimize underperforming queries, as demonstrated below.
The Entity Framework Profiler carefully examines select operations to detect suboptimal Select N+1 queries, which are notorious for being potential performance hogs.
This code, for example, lazy loads some blog posts and traverses their comments inefficiently, fetching the results one row at a time.
This lazy loading causes an inefficient Select N+1 query and triggers the alert.
The lazy loading triggers an Entity Framework Profiler alert that suggests using eager loading instead. To refactor this code, developers can use the
Include method that specifies which parts of the object model to include in the initial query.
Forcing eager loading using the
Too Many Joins
The Entity Framework Profiler alerts us when it detects too many joins, as each join loads the database with additional work, increasing the complexity and cost of the query and potentially creating Cartesian products.
This query triggers the Entity Framework Profiler's "Too many joins" alert.
To remedy this, use several separate queries instead of a single query containing multiple joins.
A Query on an Unindexed Column
Querying on an unindexed column forces the database to search through all the rows in the table. When encountering this alert, consider whether to create an index, modify the query, or dismiss the alert if the table is small enough to afford the scan.
A "Query on un-indexed column" alert provided by the Entity Framework Profiler.
Unbounded Results Set
A query that does not explicitly limit the number of returned results can be a time bomb waiting to explode, and easily go undetected during development and testing. In production, however, a query with an unbounded results set can return thousands, or even millions, of rows, overloading the database.
An "Unbounded results set" alert is triggered here.
Consider the query above, for example. If the performance category has too many posts, they will all be loaded, which is probably not what was intended. This can be easily fixed using the
Take method that allows pagination, as demonstrated below.
The results set is limited to 15 posts using the
The actual code change to fix the issue is trivial, but bringing it to the developer's attention is where the Entity Framework Profiler truly shines. Instead of starting from a slow application and an investigative process that often involves multiple parties each using their own tools, developers can get an immediate answer and pinpoint the exact line of code that needs to be modified.
When Continuous Integration Meets Continuous Profiling
Continuous integration (CI) has been widely adopted across the globe. However, while any regression test can break the build, development teams are not accustomed to verifying database access performance as part of CI. Such ongoing performance monitoring can be used for breaking the build if a change is too costly, preventing unforeseen problems from sneaking into the code. Not only would it detect major problems that are accidentally checked in, but it would also detect minor ones that can pile up and make the database slower over time. To support continuous profiling, the Entity Framework Profiler has a command line interface and can output XML, HTML, or JSON.
Furthermore, by using the API provided by the Entity Framework Profiler, developers can get programmatic access to the profiler's report. This is useful both for CI and as part of unit testing to assert on database-related functionalities.
Programmatic access allows creating build rules for CI and unit testing.
Using a dedicated profiler as part of the development process is important to ensure adequate database access, performance, and visibility. The Entity Framework Profiler in particular provides valuable insights into complex applications in a user-friendly GUI, supporting Azure, CI, and a wealth of alerts. The latest Entity Framework Profiler version 6.0 brings a new GUI and improved performance, as well as newly added support for .NET Core, Async/Await, and Azure. Version 6.0 is available for a 30 day free trial and is bundled with a free Cosmos DB Profiler when purchased.
To learn more about improving the visibility and performance of database interactions, please visit the Entity Framework Profiler web page here.
Opinions expressed by DZone contributors are their own.