Checking out EF Core inheritance options

Recently there was a need to use a hierarchy in a domain model, and there was a question about which of the available approaches to use - table per hierarchy (TPH), table per type (TPT) or table per concrete type (TPC). This very brief post is just a quick look at some benchmarks I created on the subject, available on GitHub.

There is some guidance in the docs, including some benchmarks (see more here and here). However, as normal Microsoft focus more on SQL Server, with Shay adding some info about PostgreSQL, but currently at work we use MySQL, so I wanted to make sure if the same assumptions applied.

While I ended up testing multiple databases, my goal wasn’t to compare them, but compare the inheritance options EF Core provides.

Setup and what’s tested

To test things, I created a hierarchy consisting of one base type, with 5 properties, plus 10 derived types, with 5 additional columns each. This means that, for each inheritance option, we have:

• TPH - a single table 56 columns (5 base, 5 per the other types, plus 1 discriminator)
• TPT - a table for the base, with 5 columns, plus 10 tables for each derived type with 6 columns (5 for the properties, 1 foreign key pointing to the base table)
• TPC - 10 tables, one per derived type, with 10 columns (5 for the base properties, 5 for the derived type properties)

With these tables in place, seeded with 1 million rows per hierarchy, so 100 thousand rows per derived type.

There are 2 different setups for SQL Server using TPH, a “regular” one, and one where the columns for the derived types properties are set as sparse columns, in order to minimize the negative storage impact of having many null values.

As for what’s tested, the benchmarks consist of:

• An offset paginated query - i.e. Skip and Take
• An index paginated query - i.e. instead of Skip, make use of an index to fetch pages more efficiently (see more in this community standup)
• A filter by index foreign key query - i.e. imagining we always query given a foreign key (e.g. if we’re loading an aggregate root’s related entities)
• Single by id - i.e. fetching an entity given its id
• Insert single - i.e. insert a single entity

Quick takeaways

• TPT doesn’t seem to ever be an interesting option
  • Performance is almost never better than other options
  • Storage is also not particularly good (in fact, other than in SQL Server, it’s actually worse than the others)
• TPC always wins in terms of storage space
• TPH storage space isn’t as bad as one would expect*
  • While it is worse than TPC, both PostgreSQL and MySQL show it’s not too bad
  • *SQL Server is completely different, with much worse TPH sizes, unless we use sparse columns, in which case it’s much more acceptable
• TPH performance almost always takes the win
  • But in general, TPC isn’t too far off, with a couple of exceptions in the pagination queries when using MySQL

The usual disclaimer: it’s entirely possible I messed something up, so be sure to let me know if you detect something 🙂

Also, keep in mind I did the tests guided by the needs of the projects I’m looking into. You may have different needs, in which case you should conduct your own experiments.

Now I’ll just leave the numbers referring to storage sizes and benchmarks.

Table Sizes

PotsgreSQL Table Sizes

MySQL Table Sizes

Side note: on different runs, got different size results, not really sure why, but these were the more common, so good enough for this quick analysis.

SQL Server Table Sizes

Side note: no idea why SQL Server sizes are so different from PostgreSQL and MySQL… maybe the way the space is counted is different. Anyway, the goal isn’t to compare databases, but focus on EF’s inheritance options in the same database, so don’t want to waste time figuring that one out (though I’m curious, if you know the reason, let me know).

Benchmark results

ℹ️ you’ll probably be able to look at this tables better on GitHub, as my blog engine doesn’t make it so easy to read them here

1
2
3
4
5
6

BenchmarkDotNet=v0.13.5, OS=Windows 11 (10.0.22621.1555/22H2/2022Update/SunValley2)
Intel Core i7-9700K CPU 3.60GHz (Coffee Lake), 1 CPU, 8 logical and 8 physical cores
.NET SDK=8.0.100-preview.3.23178.7
  [Host]     : .NET 8.0.0 (8.0.23.17408), X64 RyuJIT AVX2
  DefaultJob : .NET 8.0.0 (8.0.23.17408), X64 RyuJIT AVX2

Relevant links:

• Sample code
• Inheritance - EF Core docs
• Inheritance mapping - Modeling for Performance - EF Core docs
• Update performance docs to take TPC into account - GitHub Issue
• EF Core docs linked benchmark

Thanks for stopping by, cyaz! 👋