Unnecessary Fuzzy Searches May Hurt Your Entity Framework Core Performance

After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one.

In diesem Artikel:

Unnecessary Fuzzy Searches May Hurt Your Entity Framework Core Performance
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

What do I mean by "unnecessary fuzzy searches"?

In this article, I consider any filter criteria (i.e. WHERE clause) that can be more exact/precise.
For example, when filtering for a Product with unique names (of same length) we could do it either using Contains() or the equality == operator:

				
					var name = "my product";

var product1 = Context.Products.FirstOrDefault(p => p.Name.Contains(name));
// OR
var product2 = Context.Products.FirstOrDefault(p => p.Name == name);

				
			

The same applies when comparing numbers or dates using ><==, and so on.

Why would someone do an unnecessary fuzzy search?

Hardly anyone would intentionally do something unnecessary, but unintentionally, it happens…

One of the causes for sub-optimal filters is the lack of exact domain knowledge and/or side effects in the programming model used. I’ve seen code as in the example above using Contains() instead of == multiple times and the authors could not fully explain why they took this approach because both ways work. The best thing we can do in such a situation is to sensitize users of EF Core for issues like these.

Another cause can be unfavorable code sharing. Imagine, in one of our core projects we found a method LoadProducts that takes a few parameters, and among them the parameter string name.
Now, our code could look like:

				
					var name = "my product";

var product = someRepository.LoadProducts(name).FirstOrDefault();
				
			

After a quick test we are sure that the method produces a correct response, which is true, but let us have a closer look.

				
					public List<Product> LoadProducts(
    string name = null,
    string someOtherFilter = null)
{
    var query = Context.Products.AsQueryable();

    if(name != null)
        query = query.Where(p => p.Name.Contains(name));

    if(someOtherFilter != null)
        query = query.Where(...);

    return query.ToList();
}
				
			

As we can see, there are a few issues with this method. First, this method is clearly not made for our use case but for some kind of global product search, probably for displaying the data on a user interface. And second, it resides in a core project although we are pretty sure it is not a general-purpose business logic but for a specific use case that differs from ours.

Increased database load due to fuzzy searches

In the previous examples, we saw that the LINQ queries or rather the filter criteria could be changed without affecting the outcome but there was no evidence that the comparison via == performs better than using Contains().

Now we will execute two slightly different LINQ queries, look at the SQL statements generated by EF and analyze the execution plans to determine the winner. An execution plan describes all operations the database performs to handle the SQL request.
I’m using MS SQL Server for the following tests.

Given is a table with 100 Products having columns Id and Name and an unique index on the column Name. The LINQ queries are similar to the one in the method LoadProducts and return exactly 1 product.

				
					var name = "my product";

// query 1
var product1 = Context.Products.Where(p => p.Name.Contains(name)).ToList();

// query 2
var product2 = Context.Products.Where(p => p.Name == name).ToList();
				
			

The corresponding SQL statements (simplified for the sake of readability).

				
					-- query 1
SELECT *
FROM Products
WHERE CHARINDEX(@__name_0, Name) > 0

-- query 2
SELECT *
FROM Products
WHERE Name = @__name_0
				
			

In both cases, there is just one operation (worth mentioning) being executed by the database. For the query using Contains() the database performs a so-called Index Scan, i.e. the database scans the whole data source record by record. The other query using the == comparison does an Index Seek, i.e. the database uses the index to jump right to the requested record.

Depending on how large the data source is, scanning it can lead to a considerable load on the database, but that is not all.

(Unnecessary) Scanning of the data source is just the tip of the iceberg

The performance loss due to the scanning of the whole data source is just the beginning of a chain reaction. If the query in the example above is just a small part of a bigger query, Index Scan will lead to bigger internal working sets the database has to work with. Due to bigger working sets the database may decide for sub-optimal JOIN order (from our perspective). The increased complexity may lead to parallelization and/or use of internal temp tables, which are both not for free. In the end, the database will require more memory for handling the query (high query memory grant), leaving us with very bad performance.

Summary

When writing database queries with EF Core then the filter criteria should be both as precise and as restrictive as possible. Otherwise, not just the query itself but the overall database performance could suffer greatly. If you need numbers to compare with when optimizing database requests, I recommend analyzing execution plans.

Kostenloser
Newsletter

Aktuelle Artikel, Screencasts, Webinare und Interviews unserer Experten für Sie

Verpassen Sie keine Inhalte zu Angular, .NET Core, Blazor, Azure und Kubernetes und melden Sie sich zu unserem kostenlosen monatlichen Dev-Newsletter an.

Diese Artikel könnten Sie interessieren
.NET
Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

With the version 4.3.1 of Microsoft.CodeAnalysis.* Roslyn provides a new high-level API - the method "ForAttributeWithMetadataName". Although it is just 1 method, still, it addresses one of the biggest performance issue with Source Generators.
16.05.2023
.NET
Integrating AI Power into Your .NET Applications with the Semantic Kernel Toolkit – an Early View

Integrating AI Power into Your .NET Applications with the Semantic Kernel Toolkit – an Early View

With the rise of powerful AI models and services, questions come up on how to integrate those into our applications and make reasonable use of them. While other languages like Python already have popular and feature-rich libraries like LangChain, we are missing these in .NET and C#. But there is a new kid on the block that might change this situation. Welcome Semantic Kernel by Microsoft!
03.05.2023
.NET
.NET 7 Performance: Regular Expressions – Part 2

.NET 7 Performance: Regular Expressions – Part 2

There is this popular quote by Jamie Zawinski: Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."

In this second article of our short performance series, we want to look at the latter one of those problems.
25.04.2023
.NET
.NET 7 Performance: Introduction and Runtime Optimizations – Part 1

.NET 7 Performance: Introduction and Runtime Optimizations – Part 1

.NET 7 is fast. Superfast. All the teams at Microsoft working on .NET are keen to improve the performance and do so every year with each new .NET release. Though this time the achievements are really impressive. In this series of short articles, we want to explore some of the most significant performance updates in .NET and look at how that may affect our own projects. This first article is taking a deep look under the hood of the compiler and the runtime to look for some remarkably interesting and significant updates.
28.03.2023
.NET
Incremental Roslyn Source Generators: Using Additional Files – Part 7

Incremental Roslyn Source Generators: Using Additional Files – Part 7

In the previous article the Source Generator itself needed a 3rd-party library Newtonsoft.Json in order to generate new source code. The JSON-strings were hard-coded inside the Source Generator for simplicity reasons. In this article we will see how to process not just .NET code, but also other files, like JSON or XML.
21.03.2023
Entity Framework
Entity Framework Core 7 Performance: Cartesian Explosion

Entity Framework Core 7 Performance: Cartesian Explosion

In Entity Framework Core 3 (EF 3) the SQL statement generation (re)introduced the Cartesian Explosion problem. A lot has happened since then, so it is time to revisit the issue with Entity Framework Core 7 (EF 7).
14.03.2023