Efcore: Investigate perf with EF Core on Xamarin

[Metritutus]

In developing a Xamarin Android app using Entity Framework Core 2.0 with a code-first approach using a Sqlite database provider, the initialisation time seems pretty slow. For approximately 40 models (and a practically empty database) the app takes around 14 seconds to launch, 10 seconds for the call to Migrate(), and another 3-5 seconds for the first query after that. Once those are done it seems to be fine. There's barely any difference in initialisation time between Release and Debug builds.

I'm not sure if it relates to #4372 or #1906, but it may well do.

Is there anything I can do to help mitigate the initialisation time?

[bricelam]

Does keeping the connection open help? (It should help a lot if you're using encryption.)

_connection = new SqliteConnection(connectionString);
_connection.Open();
optionsBuilder.UseSqlite(_connection);

[Metritutus]

Unfortunately I'm not using encryption and the change didn't seem to make much of a difference.

[ToddThomson]

@divega @bricelam I'd be happy to help with this issue as there are certain performance issues of EF Core Sqlite 2.1 on the Xamarin.Adroid platform that are going to block the release of my app.

I've found that the initial DbContext creation takes about 5 seconds ( 3s for the EnsureCreated() call itself for an existing Db ). The first query is also slow: to read all records in a DbSet - 1.5s to read < 20 records. It appears that after the initial setup, access is quite fast.

EDIT:
Removing the call to EsureCreated() just makes the first query return await _context.Sessions.ToListAsync(); longer by the same amount of time.

@bricelam Keeping the connection open does not help.

EDIT: I setup a DebugLoggingProvider to get additional information. I found that once the EF Core infrastructure logged that it had initialized the context that it took about 1 second to complete the query. The DbCommand completed in a reported 93 ms.

Perhaps EF Core Sqlite on mono is a bit too heavy. I'll see if I can move the DbContext creation to a background thread in the startup activity.

I'd be happy to try out any ideas that might be able to help!

EDIT: With EF Core Sqlite 2.1, DbContext setup/initialization on my Android phone ( the ASUS Zenfore 4 Max is not a powerhouse! ) is too slow at 4 to 5 seconds. App launch time is also too slow for a good user experience ( my splash screen is nice, but... ). I'll continue to monitor EF Core Sqlite for mobile platforms, but for now I'm converting my database repository layer to use a minimal wrapper over SQLite.

[cwrea]

@Metritutus In the mitigation category, try enabling SQLite's Write-Ahead Logging (WAL). I've found that it does reduce the time for EnsureCreated() in particular, but YMMV as I wasn't testing with a large number of model classes. See here for code example. My past experience with WAL in non-Xamarin mobile apps that used SQLite is that WAL does improve performance in a number of scenarios.

[Metritutus]

Sadly, enabling Write-Ahead-Logging did not appear to have any noticeable impact on initialisation time.

[ajcvickers]

@divega Can I move this to 3.0 now for tracking purposes?

[tipa]

Any updates to this?
Many of my users are asking about why my app is starting so slowly compared to other apps in the store. Even with AOT enabled startup times are ~4 secs on high end phones.
This issue (together with this one) make me consider switching to a more lightweight library

[ajcvickers]

@tipa Unfortunately we do not have the resources to spend time significant time on the Xamarin experience. While I hope that we get back to it in the future there are so many other issues that are higher priority that I think if you want something in the short term that is performant you would be better off using something other than EF.

[NickA55]

@tipa Unfortunately we do not have the resources to spend time significant time on the Xamarin experience. While I hope that we get back to it in the future there are so many other issues that are higher priority that I think if you want something in the short term that is performant you would be better off using something other than EF.

This is really sad and disappointing to hear. The future Microsoft is pushing is .NET Core, EF Core being part of that. It begs the question of how does Xamarin fit into the plans? Is Xamarin that low on the priority list that you really have no one to work on issues?

This is a major issue, and makes EF Core on Xamarin totally unusable in a real world application. So if I want to create a Xamarin application, and use what Microsoft wants me to use, which would be EF Core, I can't because it's so slow it's basically useless.

This tells me a lot about what Microsoft really thinks of Xamarin as a serious development tool, and why Xamarin will never be taken seriously as a professional application.

[lnaie]

Come on guys, make every Xamarin mobile developer proud of using it. :)

[Shumigaj]

My application also has slow initialization time because of EF Core. I've collected everything I learned in the stackoverflow question. I hope Microsoft team comes up with something before the release of EF Core 5.

[roji]

Am going to do my best to work on this for 5.0, but some sort of minimal code sample would greatly increase the chance of improving the perf here.

[Shumigaj]

Thanks, I'll provide clean example before Monday

[Shumigaj]

Example with EF Core 5 Preview 6 added to GitHub. I also updated stackoverflow question with additional information.

[ajcvickers]

Note from triage: we're still planning on doing this in the near future, but we are out of time to get significant changes into 5.0.

[dfyx]

Some of our users report up to 25 seconds when opening the first database context, especially on Android and older iOS devices and that's with less than 15 tables.

[adelarsq]

Also more migrations added will turn to worst the performance to build and open the app.

[dfoulk]

Please don't punt this again. We need this fixed as EF Core migrations are invaluable in our web projects- but not currently viable in Xamarin. It's a tragedy, really... Thank you

[AndriySvyryd]

I haven't tested this, but if your project has a lot of migrations you could move them to a separate assembly and only call Migrate/EnsureCreated on install or update.

Model initialization perf will be addressed by https://github.com/dotnet/efcore/issues/1906 and https://github.com/dotnet/efcore/issues/19610

[adelarsq]

I haven't tested this, but if your project has a lot of migrations you could move them to a separate assembly and only call Migrate/EnsureCreated on install or update.

Yes, this can help. I did exactly this. Also to avoid performance issues I search for migrations that exists on the migrations assembly that aren't on migration table. If there is any I call Migrate().

[TomBruyneel]

Unfortunately the performance issues have little to do with migrations and everything with initializing the context the first time. So if your app needs some data from the database right on startup there is no way to escape from a slow startup. Reducing the number of entities also doesn't really help a lot.

See for example: https://stackoverflow.com/questions/62582954/ef-core-sqlite-slow-star-up-performance-on-xamarin

[dfyx]

I can confirm this. Taking measures to prevent the migrations from running when they aren't needed speeds up the process by about 20% but it's still a lot slower than it could be. On some Android devices and older iOS devices (especially those with A9 processors like the iPod Touch 5) it can take about 20 seconds to open an SQLCipher database while opening the database directly without EF takes less than a second.