Using Entity Framework (or some other ORM tool)? Having performance issues? Here’s a solution.

This week I’m a booth babe for Red Gate at BASTA, a conference for Microsoft stack developers in Germany. This year the theme is ALM (Application Lifecycle Management). With so many people talking about how they deliver their updates I thought that discussions about our SQL Server version control, CI and release management plug-ins would be the topic of the day.

However, something I hadn’t realised is that in Germany ORM tools like Entity Framework are really popular. (More so than in the UK or US anyway.) If you are using Entity Framework code-first or model-first migrations you do not have as much of a need for any specific ALM tooling for your database. It’s easy to version control because all the SQL is controlled from your application tier or model and deployments are handled by Entity Framework Migrations.

BASTA! 2014 - Konferenz, Mainz

It’s a fundamentally different approach to writing SQL. While it removes most ALM problems that are common in traditional SQL Server development, which is one of the reasons .NET developers often love it, Entity Framework does pose its own problems.

Sucky performing code generated by Entity Framework

The most common problem you hear about is that the T-SQL that Entity Framework creates does not perform very well. Add to this the fact that you didn’t write the code in the first place and might not be familiar with what it looks like and you have a recipe for a headache. It is hard to performance tune your queries when you are so removed from the SQL that you are writing.

I’m not an expert in performance tuning by any stretch. If you want to read a blog by someone who is read Grant Fritchey or Brent Ozar’s blog instead of mine. In this post I’m not going to tell you how to tune the query, instead I’m going to discuss how to find where the problem is and expose the SQL that Entity Framework has created.

ANTS Performance Profiler

ANTS Performance Profiler is primarily a tool for analysing the performance of your .NET applications and (historically) it has primarily been marketed to .NET developers. I’ve not really worked with it in the past because throughout my career at Red Gate I’ve only worked with our SQL Server ALM tooling – perhaps this is why I never made the connection before?

When trying to improve the performance of some application it can often feel like working blind. You have a gut feeling about where the issue is and you do lots of work in one area in an attempt to speed things up. However, you don’t really know if you are looking in the right place until you are finished. This is bad enough in your application, where you probably have a relatively good understanding of the code-base, but when you have a database performance issue and you are unfamiliar with the exact implementation of your database by Entity Framework this problem is amplified.

The point of ANTS Performance Profiler is that it tells you exactly which methods have run, how many times and how fast (or slow) they were. You can drill down to each individual line of code and see how long it took. The benefit is that you know you are looking in the right place. There is no point tuning a method if the real problem is that it was accidentally called it three million times.

WP_20140923_16_05_25_Pro database-profiling

Where ANTS Performance Profiler gets interesting for Entity Framework users is that it exposes calls to your database and the T-SQL that is run there. This means that if you do have a performance problem in your database, and you aren’t familiar with the code Entity Framework has generated for you, ANTS Performance Profiler can show you exactly where the problem is.

Great, we have found the problem – all you need to do now is fix it… (But like I said earlier – I’m not the guy to help you do that!)

P.S. I’m sorry I don’t have any lessons to report from any of the sessions this time. I’m afraid I don’t speak German so I’ve not attended any!

  2 comments for “Using Entity Framework (or some other ORM tool)? Having performance issues? Here’s a solution.

  1. September 25, 2014 at 10:32 am

    I too like ORM tools. I think they work really well when properly applied. But, performance isn’t the only issue they can introduce. Data persistence issues don’t go away just because you have an ORM doing your deployments. In fact, data persistence becomes a much bigger problem as most ORM tools don’t do data migration well, especially with structural changes that can result in data loss. That’s where database ALM comes into play in a major way and isn’t helped at all by any of the ORM tools I’ve used. Developers can have a tendency to forget about that.

    • Alex Yates
      September 25, 2014 at 10:46 am

      Thanks Grant. I do have my views about migrations as applied by entity framework too. However, I am mindful that if someone is already using code-first, suggesting that they move away from EF is unlikely to be an option to take lightly.

      I’ve talked to one or two people who had issues with old objects hanging around on production. Using something like Red Gate SQL Lighthouse could be an easy first step to validating that your deployments are working correctly and highlighting any deployments that have unexpected results. (A public BETA should be available soon).

      In this post , however, my intention was to tackle the common problem of cruddy SQL performance from EF generated code.

Leave a Reply

Your email address will not be published. Required fields are marked *