Every query on SQLite DB using EF Core in Docker is very slow, unless there is an explicit call to OpenConnection

user_9179027 0
Viewed 637

I am running a simple .NET Core 3.1 web application on localhost. The web application is running in a Docker Linux container (my host OS is Windows 10). The SQLite database used by the web application is connected using EF Core, is almost empty and very small at 60Kb, each table having 20 records or less, and is stored on the host and mounted for the container using following command:

--mount type=bind,source='SomeDummyHostPath',target=/mnt/hostFolder

The problem is that every query executed on DBContext runs very slow (~2s for reading 2 rows). When I run the application outside of the Docker container, everything runs smoothly (meaning that the same query is executed in ~1ms).

What is strange is, after invoking dbContext.Database.OpenConnectionAsync() before every call to EF's DBSets, every query in the Docker container runs quickly (a few milliseconds).

Why is the performance so bad in this scenario? Is there any way to improve it without the explicit call to OpenConnectionAsync before every query?

docker .net-core entity-framework c#
2 Answers

EF Core 3.x included one breaking change that affects this use-case, the connection is closed earlier:

Database connection is now closed if not used anymore before the TransactionScope has been completed

Mitigations:

If the connection needs to remain open explicit call to OpenConnection() will ensure that EF Core doesn't close it prematurely

The sqlite provider is still not using connection pooling: 13837;

With the new releases of EntityFrameworkCore.Sqlite (>3.0), there are some new optimizations regarding transactionality, and new .WAL file is created when the .db file is open; This, combined with lack of connection pooling results in creating and removing the .WAL file with every request; And docker volume/file mapping to windows file system gets to be very time consuming.

user_36873850

There is not much more to explain than what Lucian has already done in the accepted answer. Anyway, I will restate to contextualize the link.

The problem is all about EF Core closing and reopening the connection every statement.

One way to work around this is providing the connection (already open) by yourself. EF does not auto close connections that were already open.

As he also stated, at the time of this answer, the connection provider for SQLite still don't use a connection pool.

The best way to achieve the maximum performance with EF Core and SQLite is to provide a connection pool. The goal of the pool is maintaining some connections always open, because the problem itself is the overhead of opening new connections.

Finally, the link is a comment that I've made in the EF Core GitHub issue tracker containing a complete example of how to do that in a simple way.

https://github.com/dotnet/efcore/issues/13837#issuecomment-821717602

user_141660880
Related