Entity Framework – improve query performance – potential problems

This the first post from series where I am going to describe ways to create more performant queries using an Entity Framework 6. I will present methods and techniques that will allow you to have a more significant throughput and response time in your application.

Before you find out how to improve the performance of your queries is worth to understand which potential steps of query execution can be improved and this is the subject of the first article. I am not going to describe each step that occurs when a query is executed because it is already described on MSDN  I will focus only on steps that can be improved:

  1. Query compilation
    When the query is executed the first time, internal plan compiler compiles the query and stores it in a query plan cache. When the query is executed the second time, the compiled query should be taken from this cache. If it does not exist, then compilation must be executed once again. Unfortunately, if you have some specific syntax in your LINQ query, then the query compilation phase is made with every query execution. Microsoft claims that this stage has a medium impact on performance but from my perspective, it has an extremely high influence on CPU and memory resources.
  2. Request made to a database
    Each create and update action send a SQL query in a separate round-trip. If your application requires 30ms to connect to a database and you create for example ten new objects, then application spends just 300ms on connecting to a database. The same problem exists for every read query that you make.
  3. SQL Server process a query
    It is the most tricky part because I learnt that long and complex looking queries are not always complicated to process by a SQL Server, but I assume that smaller queries are better to handle by a SQL Server and you should know that the entity framework likes to generate crazy things.
  4. Query result is returned from a database
    When many JOIN statements are used then a Cartesian product is generated and many repeated data is returned to the application through the network. This problem not only touch a network but object materialisation as well because there is more data than needed to process by the Entity Framework.
  5. Object materialisation
    Object materialisation is always performed by the Entity Framework but using projections it is possible to speed up this process a bit. I will show you a great library that can be used with the Entity Framework on a read side of your application.

Each presented step can be improved. Of course, it depends on a query we execute, but it is possible. Every next article from this series will describe ways to improve each step.

Please stay tuned.

One Reply to “Entity Framework – improve query performance – potential problems”

  1. Pingback: dotnetomaniak.pl

Leave a Reply

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