Andrea James Team : Web Development
27Nov2017

Case Study: Improving Performance in Entity Framework with Stored Procedures

Andrea James Team : Web Development

One of my most recent projects was updating an existing custom MVC application which used Entity Framework (model first approach) to query data. The home page retrieved articles on the stock price performance of companies for a selected stock exchange from the SQL database.

Under normal circumstances, the home page loaded efficiently, but when new data was being populated in the database, the home page timed out. It was clear that some type of lock contention was occurring in SQL Server for the home page query.

After a bit of research and some code / SQL profiling, we concluded that two things needed to be addressed with the way the system was currently working.

  1. We needed to fine-tune the query to ensure that only the data we really needed should be retrieved. The default query generated by EF was retrieving too much information.
  2. We wanted to be able to set the transaction isolation level so that we didn’t need to wait for row locks to be released, even if that meant getting a slightly stale version of article data.

Ordinarily to address issue #1, we could modify the EF method calls to ensure that only the essential data needed was retrieved. However, to achieve #2, it looked like the best route was to write a custom stored procedure to retrieve the article data required for the home page and then update Entity Framework to use our fine-tuned stored procedure instead of the default query generated by EF.

Here’s how we went about doing that.

Step 1: Create the Stored Procedures

The SELECT query we needed to run on SQL server to retrieve our data was straightforward, it just needed several inner joins to grab all related data we’d need to display our articles on the home page. There were a few key differences in the way we wrote our queries versus the way the Entity Framework query executed:

  • Setting the transaction isolation level to SNAPSHOT so that the select query doesn’t need to wait until row locks are released during data updates
  • Using a simple INNER JOIN structure instead of multiple subqueries
  • Using OFFSET and FETCH NEXT to retrieve only the rows needed for that “page” of results instead of the full result set (we didn’t need to keep track of page numbers since the pages were loaded through a simple lazy load mechanism)

Just testing out the default query vs. the stored procedure in SQL Server Management Studio we got the following average timings in our development environment:

Original Query

29 seconds

Stored Procedure

12 seconds

Additional references for reading up on transaction isolation level, and for paging data in SQL queries with OFFSET and FETCH NEXT:

Step 2: Update the Entity Framework Model

Once our stored procedure was created in SQL, we could then use the “Update Model From Database” function in Entity Framework in the EF Designer. This created the following changes in our overall EF Model:

  • In our data store, the model now showed the new stored procedure we just wrote
  • A function import was created for the stored procedure
  • A complex type was created as the return type for the function import

Examining the code generated by the update to Entity Framework, we could now see that our database context offered a new method named after our stored procedure, with a return type of our new ArticleSearchData complex type.

The key difference here was the new complex type being returned instead of a class based directly on our table model. The stored procedure was now giving us a class object with properties for every column returned by the SELECT query in our stored procedure. Instead of getting a hierarchy of related classes, we were now getting a single object type with a flattened structure that had properties for each of the related entities we needed to display our home page articles.

A detailed example of how to use EF Designer to add a stored procedure to your model is here:
https://msdn.microsoft.com/en-us/library/jj557860(v=vs.113).aspx

More information on using complex types is here:
https://msdn.microsoft.com/en-us/library/jj680147(v=vs.113).aspx

Step 3: Update Required Object Mappings

The new objects returned by the Entity Framework function import now needed to be fully integrated into the MVC application so that the correct information would appear in each view.

In the existing application, we already had a repository pattern implementation to set up structured access to the EF model. The final step of integrating the new stored procedure required the following code updates:

  1. Update the articles repository to make a new call to the stored procedure method call added to our data context, passing through any required search parameters.
  2. Update object mapping methods to convert the ArticleSearchData structures into view models required by the MVC application.

Appendix: Adding and Tuning Database Indexes

In addition to improving the performance of the query we used for fetching data, we also performed some basic analysis on the existing SQL tables to see if we could add some configuration in SQL itself to help improve query performance. In fact, this is a key step to troubleshooting or preventing any performance problems related to a SQL database. Using the Database Engine Tuning Advisor tool that comes with SQL Server, we identified some additional indexes to add to some of the tables in the database which further improve site performance.

Official documentation for using the Database Engine Tuning Advisor is here: https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor

The high level steps we followed were:

  1. Captured workload details into a trace file using SQL Server Profiler
    We created a trace in SQL server profiler and loaded the home page of the site, then navigated to key functions on the page, in order to collect database workload data.
  2. Ran a new tuning session based on the workload we captured
    After feeding the workload data into the Tuning Advisor tool, it provided us with recommendations on new indexes to add to key tables so that queried data would be returned quickly.