In this article we will compare the performance of both most used inheritance types – Table-per-Hierarchy (TPH) and Table-per-Type (TPT), supported by LinqConnect starting from version 2.00. For a better understanding of these inheritance types, we will use the Model First approach when the object model is developed first and after that the database scheme is generated.
To develop an object model, the Entity Developer tool will be used.
Our tests are performed on the following object model:
We have a hierarchy of three entity classes: the base class Gadget and its descendants – MobilePhone and Netbook.
After the object model is created, we can start a database scheme generation. The main differences between TPT and TPH, that influence the database scheme, appear on this stage.
Let’s consider thefirst inheritance type – TPH. Within this type, the data for class hierarchy is stored in a single table. So, after the scheme generation in the database, the following table is created:
| GadgetId | Discriminator | Vendor | FormFactor | DisplayResolution |
|---|---|---|---|---|
| 1 | M | Nokia | Monoblock | NULL |
| 2 | N | Sony | NULL | 1024*768 |
The Discriminator column is used to define the entity type for the table rows: M – mobile phone, N – netbook.
TPH provides the following advantages:
- High performance of CRUD operations, because all data are stored in one table.
- Minimum number of tables in the database.
The general disadvantages of TPH are:
- Data redundancy. Some columns for specific entity classes contain NULL values, and the number of these columns depends on the number of classes in the hierarchy.
- The first disadvantage defines the second one. Data redundancy leads to the data integrity violation if data was changed manually.
- The complexity of adding and deleting new entities to/from the object model, because it is necessary to add or delete columns to/from the result table.
Let’s consider the TPT inheritance now. This inheritance type means that each entity class in the hierarchy corresponds to the database table. So, after scheme generation, we get the following three tables:
the Gadgets table
| GadgetId | Vendor |
|---|---|
| 1 | Nokia |
| 2 | Sony |
the MobilePhones table
| MobilePhoneId | FormFactor |
|---|---|
| 1 | Monoblock |
the Netbooks table
| NetbookId | Resolution |
|---|---|
| 2 | 1280*768 |
The Gadgets table has 1:1 associations with both the MobilePhones and Netbooks tables.
The main benefits of the TPT inheritance are:
- Data integrity is preserved. All the tables in the scheme correspond to the third normal form.
- There is no data redundancy.
- Flexibility in object model modification. To add or drop an entity, add or delete the corresponding table to/from the database.
The main disadvantages of the TPT inheritance are:
- The speed of the CRUD operation decreases when the number of classes in the hierarchy grows. For example, SELECT queries will contain JOIN clauses and their number will be equal to the number of classes in hierarchy.
- A large number of tables in the database.
We have considered the main differences of the TPT and TPH hierarchies and now let’s look at performance test results. For both hierarchy types we will calculate the time of insert/update/delete and select statements. The total number of processed records in the tests is 200,000. We also performed similar tests for Entity Framework model, results are presented on the diagrams below.
As we can see, CRUD operations are performed faster even when the hierarchy has a small number of classes, when the TPH inheritance is used. Note also that LinqConnect performed CRUD operations faster than Entity Framework and in most cases even the TPT inheritance provides better performance than the TPH inheritance in Entity Framework.
