In last article, we have some some basic tips which can be useful troubleshoot any issues in the applications which use .NET EF core. Now, let’s have a look at some tricks to troubleshoot and resolve performance issues in EF Core data access layer.

How to troubleshoot ?

Troubleshooting performance issues is a three step process:

• Enable logging to figure out problematic areas
• Figure out the database commands generated from problematic areas of code
• Use appropriate tools to inspect the database commands, execution plans to figure out the root cause and probable solutions

Let’s see how these steps can be performed.

Enable Logging

There are two ways to enable logging in EF core layer. One way (and probably simplest of the two) is Simple Logging and a demo can be found in this blogpost.

Another way is to enable logging via Microsoft.Extensions.Logging mechanism. This is the default mechanism used in .NET. Two crucial interfaces in this mechanism are – ILoggerFactory and ILogger. The logger factory setup decides where the logs would be written. Below is a sample code of a DbContext which uses this approach for enabling logging.

There can also be some other ways to enable logging and trace all the interactions with underlying database. For example, you can use SQL Server Management Studio and SQL Profiler to trace the interactions.

Figure out problematic commands

Once the logging is enabled, logs can be then used to see how LINQ statements are converted to database commands. The logs would also help to understand how much time a command is taking for its execution.

When an application is being executed, it might generate enormous amount of data even in few seconds (depending on log level specified). Hence correlating the LINQ statements to database commands may be time consuming, tedious and difficult.

For this purpose, query tagging can be used to identify the database statements generated by a LINQ statement. Query tagging has been discussed in previous post.

Root Cause and Solutions

After following first two steps, you should have identified the problematic queries already. These statements can then be analyzed further.

One way to analyze those statements is to understand how database executes those statements. Generally SQL databases generate execution plan when a query is received. This plan depends on two things:

• First thing, if the tables have any indexes defined
• Second thing, how much data is currently present in the table.

Most of the databases provide some kind of tools to analyze the query execution plans. Those can be used to identify possible solutions to improve the performance.

Best Practices To Follow

In this section, let’s have a look at some best practices suggested by documentation:

Indexes

Indexes can be defined on tables to improve performance of queries. But application performance should always be measured to ensure that overall performance is boosted by an Index. This is because, Index may improve performance of read operations but it may slow down updates in some cases.

Limit Amount of Data in Resultset

Performance bottlenecks can be anywhere. Degraded performance may be due to the poor database statement. It may also be because of network latency. Network latency is the time difference between the time at which request was sent and the time at which response was received.

If the database queries return a lot of data, then the network transfer (including serialization and deserialization) would need more time to transfer it. Hence, the queries should always try to limit the amount of data. This can be done by two ways:

• Fetch only required columns from database entities – instead of reading data from all columns
• Always limit the number of records in resultset by specifying WHERE and TOP N clauses

Use Eager Loading If Possible

Sometimes, eager loading can reduce the chattiness between application’s process and database process. This would help in reducing time spent in tasks which are performed in database interaction.

Lazy loading is always assumed to be best practice. But many times it means more roundtrips from application to database, which may degrade performance of application.

Use AsNoTracking if Possible

By default, when a resultset is returned by EF core query, EF core context tracks the entities. This task of tracking entities means DbContext would have to perform some additional tasks under the hood to keep track of operations performed on that object. This certainly would eat some CPU cycles.

Hence, it is recommended to using no-tracking queries in LINQ queries if the operation intends to read the data.

Beware that AsNoTracking does not perform identity resolution. This means same database row 9from related data) can be represented by multiple objects.

Sometimes, Raw SQL Can Help

Sometimes the queries are very complex and it may be easier to use a the raw query (or stored procedure) instead.

Note that raw SQL should be used as last option. This is because it may have some side effects. If people choose to write stored procedures, it may contain some business logic, thus spreading the business logic in multiple layers. It also means additional maintenance for the application.

Optimize Interactions for Update and Delete

Generally, when multiple entities are getting updated, it is better to update the database in batches to reduce the roundtrips between database and application. Min and Max batch size can be specified in the DbContext. But before finalizing this, application should be tested thoroughly to decide the benchmark.

Another option is to use Raw SQL queries to perform bulk updates. For example, if every employee’s salary is hiked by 3 %, a single update statement can update the Salary column for all rows in Employee table.

Wrapping Up

Performance improvement is not one time work in project’s lifecycle. It should be done periodically to ensure that application’s performance is not degraded. If it is degraded, then troubleshooting steps mentioned above should help to fix the bottlenecks. And even after fixing bottlenecks, it is recommended to keep measuring performance of system to ensure that applied fixes work as expected.

I hope you find this information helpful. Let me know your thoughts.