FirstOrSingleOrDefault

Comparing FirstOrDefault and SingleOrDefault

by

Introduction

This is an EF Core experiment measured with the popular BenchmarkDotNet library. You will find the benchmark results and the source code to reproduce everything on your machine and answer the question on the performance impact of the System.Linq methods SingleOrDefault() vs FirstOrDefault(), especially in the case of an indexed column.

This video explains it pretty well: SingleOrDefault or FirstOrDefault? When LINQ might harm you. Nick Chapsas describes how the two methods work and why one might be slower than the other. I recommend watching it.

Why do I write this article? I had a more specific question on how it might affect the performance in the scenario of an indexed column. Also, I wanted to continue what I started with Generating The Fibonacci Sequence in C# and do some experiments with BenchmarkDotNet.

Why Is SingleOrDefault Potentially Slower Than FirstOrDefault?

SingleOrDefault() ensures that only one record matches the given condition, meaning it needs to check all rows, even when it finds one at the beginning of the table.

On the other hand, FirstOrDefault() will stop looking for rows after finding a row that matches the criteria.

Please note: SingleOrDefault() will throw an exception in case there is more than one record matching your criteria found. If you do not want that behaviour, use FirstOrDefault() instead.

I also wanted to figure out with this experiment whether this is true for the indexed column. Because when it is indexed, it wouldn’t have to scan the whole table to determine the uniqueness of the given value. This assumption turned out to be true. But let us start with the experiment.

Creating the DB and Table

Before you can insert the data, you need a new database and create a schema with the following SQL script:

USE [SampleRecords]
 
CREATE TABLE [dbo].[SampleRecords]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Uid] [uniqueidentifier] NULL,
    [Name] [nvarchar](1000) NULL,

    CONSTRAINT PK_SampleRecords_Id PRIMARY KEY CLUSTERED ([Id]),
	CONSTRAINT IDX_Uid UNIQUE NONCLUSTERED ([Uid])
)

Source Code

The whole repository can be found here:

matthiasjost/SingleOrFirstBenchmark: Tests with BenchmarkDotNet (github.com)

Clone the repository and use Visual Studio 2022 to compile it.

The code contains some code to fill your table with the needed example data. We will insert 1 million rows to have something to test with.

Just look at InitialiseDbWithRecords() a method that you will find in Program.cs to get the test database filled with rows to test against.

Build a Release version and execute the benchmark without the attached debugger. Double-click the executable from Windows Explorer that has been built.

BenchmarkDotNet Results

This table is essentially what BenchmarkDotNet printed on the console, without some columns that weren’t relevant for showing what I wanted:

MethodMeanRatio
FirstOrDefault_Indexed_Last244.9 us1.03
FirstOrDefault_Indexed_First246.3 us1.01
SingleOrDefault_Indexed_Last285.9 us1.00
SingleOrDefault_Indexed_First298.0 us1.07
FirstOrDefault_NotIndexed_Last26,693.1 us96.11
FirstOrDefault_NotIndexed_First21,002.4 us75.55
SingleOrDefault_NotIndexed_Last30,168.1 us107.86
SingleOrDefault_NotIndexed_First30,230.0 us108.91

Here is a histogram automatically generated with Charts for BenchmarkDotNet:

You can see that the differences between the indexed column scenarios are not that big compared to a not indexed column scenario. This is not a surprise since we know how significant the performance impact of indexes is.

The second greenish bar from the left is FirstOrDefault_NotIndexed_First(), so when FirstOrDefault() is used, you will get a faster response in that particular case when the desired record is in the first row.

Just look at the repository with the complete source code to grasp what I am talking about here. Have fun testing it on your machine! You will not get the same results, but the ratios should point in the same direction.

What SQL Queries Get Produced By EF Core?

To get the complete picture, it makes sense to look at the SQL queries EF Core will produce and show the execution plan within the SQL Management Studio.

You can enable the logger within the application or use the SQL Management Studio’s SQL profiler to see the produced queries.

Here is the relevant code snippet that ensures the logging of the queries. See LogTo(Console.WriteLine).

    public class SampleContext : DbContext
    {
        public DbSet<SampleRecord?> SampleRecords { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.LogTo(Console.WriteLine);
            optionsBuilder.UseSqlServer(
                @"Server=...;Database=SampleRecords;Trusted_Connection=True;Encrypt=False");
        }
    }

The only difference between the two in terms of the generated SQL code is in one parameter:

SingleOrDefault()

 SELECT TOP(2) [s].[Id], [s].[Name], [s].[Uid]
 FROM [SampleRecords] AS [s]
 WHERE [s].[Uid] = '090c2fa6-5a4e-44e9-9b43-c68862463746'

FirstOrDefault()

SELECT TOP(1) [s].[Id], [s].[Name], [s].[Uid]
FROM [SampleRecords] AS [s]
WHERE [s].[Uid] = '090C2FA6-5A4E-44E9-9B43-C68862463746'

So it is either TOP(1) or TOP(2).

Please note to figure out how to query gets executed, you can enable the Query Plan from within the SQL Management studio: Display an Actual Execution Plan – SQL Server.

The query plan will show you how many rows get scanned to get the result; while benchmarking is good, this is another quick way to validate if the database does what you think it should do.

Conclusion

The most significant difference is whether a column is indexed or not. Also, it is good to know how FirstOrDefault() and SingleOrDefault() work and when to use one over the other.

While the performance drawbacks might not be significant in many cases, you can use FirstOrDefault() every time you enforce a uniqueness constraint on the database itself.

While my article explains the scenarios based on IQueryable (with the use of MS SQL and EF Core), the following article explains it for IEnumerable: Enumeration in .NET IV — Finding an item.

Scenarios with IQueryable vs. IEnummerable using SingleOrDefault() are only different in performance when we use an indexed column but are the same when the column in the predicate is not indexed. The database must scan the entire table to ensure only a single item when the column is not indexed. Where with IEnummerable we never have an index we can rely on, FirstOrDefault() is always the faster alternative.

Links