How to use HILO with Entity Framework Core

In Entity Framework Identity DB server generated keys are most popular but as a latency optimization technique HILO keys can be used instead to generate unique IDs for entities on the client side.

When HILO is configured Entity Framework sets up a database sequence and fetches a block of values from it. These pre-fetched values are cached internally by EF on an app instance level and used as entity keys whenever a new row needs to be inserted.

As long as EF has remaining values from the sequence it no longer needs to fetch a database-generated ID when inserting records thus saving roundtrips. Once all values in the current range have been used EF will do a single roundtrip to fetch the next range of values and so on.

Note that given the sequence values are cached on an app not a DbContext level, HILO is perfectly suitable for use in web apps when we usually create a context per request.

Regardless of whether we are using the Code First or the Database First approach it’s pretty easy to configure HILO in Entity Framework…

How to use HILO in Entity Framework

When using Code First, configure an ID to use HILO in your OnModelCreating method like below. The first highlighted block will create the sequence and the second will set it to be used by the primary key of the Blog entity…

After updating the DB, Entity Framework will have made the BlogId column non Identity and will have created a sequence like below…

If you’re using the Entity Framework DB First approach you will need to create the sequence yourself and set the key of the Blog entity to use the sequence as per the second highlighted block above. Note, do this in a derived DbContext so you don’t lose your customizations every time you re-scaffold.

What does Entity Framework HILO Insert SQL look like?

Let’s say we are running this code to do 200 simple inserts…

In this case given that we have set the sequence batch size to 100 and kept Entity Frameworks default batch size of 42 our Entity Framework HILO SQL will look like below…

Click on the image for a larger view in a new tab…

We can see we have two calls to get the next value for the sequence and then we have five INSERT into statements to insert 200 records in total. Note all calls to get the sequence happen before any INSERTs as we just have one call to SaveChanges(). Also note that the PKs are passed in so we don’t have any need to select back Identity values.

Comparing this to below which is the equivalent SQL in Entity Framework 6 for when the blog ID is an Identity column we can see that the Identity approach requires an additional roundtrip after each batch insert to get the last inserted blog PK value.

We can also see that rather than using a simple insert the Identity approach uses a MERGE statement and declares a temporary table (1) and uses the OUTPUT clause (2).

Click the image for a larger view in a new tab…

Performance differences between Identity and HILO IDs on Entity Framework 6?

Here’s some BenchmarkDotNet benchmarks for when I inserted 1000 records using Identity and HILO Ids on both Entity Framework 6.0.7 and Entity Framework 7 (Preview 6). These benchmarks correspond to a low latency environment as both the benchmark app and DB were in the same Azure region (North Europe).

We can see for EF 6.0.7 HILO is 47% faster, that’s a pretty big difference and likely isn’t just latency reduction as the temporary table used in the Identity approach likely has overhead too.

Click on the image for a larger view in a new tab…

Performance differences between Identity and HILO IDs on Entity Framework 7?

BUT… how come the performance boost for HILO over Identity on Entity Framework 7 (Preview 6) is only 14% while on EF 6.0.7 it was 47%?

Good question.

This is because of awesome Entity Framework 7 performance work the team have done which means Identity inserts are a lot quicker than before, thus the timing gap between the two approaches is reduced.

Here’s the EF 7 Identity SQL. The differences are that a temporary table is no longer used and the OUTPUT clause has been simplified.

Click on the image for a larger view in a new tab…

It’s not applicable to above as we are inserting 200 records and batch size is 42 (the default) but another optimization which Identity insert in EF 7 will have is that the begin and commit (not shown above) transaction roundtrips will be removed when insert size <= batch size.

Below for example we can see two transaction roundtrips for begin and commit in Entity Framework 6 for 42 records while in the Entity Framework 7 version there are none….

Should I move to HILO?

… not without benchmarking your specific scenario. The figures above correspond to the specific benchmarks I ran only. While the benchmarks had representative latency for a cloud app, not every app is going to have the level of latency optimization which Azure can provide. And of course the entities being inserted will be much more complex in a real app too. Similarly things like batch size (42 is the default) and sequence block size (10 is the default) as well as a lot of other factors can affect timing differences.

Due to the nature of the performance changes in EF 7 the gap between the two approaches has definitely closed for simple inserts which are benchmarked in this post…

… and when multiple records are inserted once they are at or below the batch size but one optimization in EF 7 for HILO (and GUID ids) relates to the very common scenario of creating a new parent and new job at the same time. In EF 7 the SQL is optimized to send two inserts in one command saving a roundtrip…

There’s a lot of variables here and that’s why important to benchmark scenarios which are realistic for your app.

What HILO sequence size should I use?

The default is 10 and in the benchmark above I’ve used 100 which means to insert 200 records EF needed to make two sequence related roundtrips. The higher we set it the less roundtrips we need to make.

Since the sequence ranges are cached on an app level, when our app goes down, is recycled/restarted OR scales to multiple instances in the cloud a new range will be requested potentially resulting in gaps. The higher we set the sequence the larger these potential gaps will be.

Shay from the Entity Framework team recommends setting it higher than the default anyhow…