Performance of EF Core Sqlite 2.1 on Xamarin.Android Platform 8.1 about xamarintodo HOT 6 CLOSED

@cwrea I couldn't find an Sqlite based micro-orm that was cross platform and lightweight so I ended up writing my own. My idea was to use a modified subset of the EF Core API, a fluent mapping configuration for entity setup and automapper for mapping. I utilized Relinq for Linq support and Microsoft.Data.Sqlite for SQL. The library utilizes DI to loosely tie in the Sqlite specific relational database support.

Please take a look at the Entities.Sqlite

I'd welcome your thoughts and would like to have you as a co-owner of the project if you are interested.

from xamarintodo.

@cwrea Thanks for your comments. I reached out to you as I believe we have similar ideas in this area. I also would have gone forward with EF Core ( for the same reasons that you mentioned ), but on mobile platforms lists must be presented to the user immediately and the app can't present a loading screen for too long.

I am currently working through connection threading/concurrency and async calls that hit the database. In a day or 2, you should be able to clone the repo and run all the tests and the sample Todo app. You will be able to see how easy it is to move from EF Core to Entities.Sqlite.

from xamarintodo.

@ToddThomson I hadn't noticed any specific issues, but I haven't been focused on performance just yet. Since you asked, though, it made me curious. I ran some tests (a few times each) with the XamarinTodo sample on a Moto G5 Plus:

• With EF Core 2.1, in a debug build with debugger attached, I'm seeing ~1550ms for the call to EnsureCreated() with an existing empty DB, and ~3030ms for the call to GetItemsAsync(), with an existing DB (with no records). This is for a debug build, with debugger attached.

• With EF Core 2.0.3, and all other things being equal, the same measurements are ~1400ms and ~2850ms, respectively.

While EF Core 2.1 is a little bit slower, the difference isn't pronounced for those two particular cases.

Here's an example of how I measured:

    var start1 = DateTime.Now; dbContext.Database.EnsureCreated(); var elapsed1 = DateTime.Now.Subtract(start1);
    Debug.WriteLine("dbContext.Database.EnsureCreated() elapsed: {0}", elapsed1.TotalMilliseconds);

Outside of using SQLite for EF Core, I have done SQLite performance optimization work for native Android (Java) and iOS (Swift) apps. Aside from DB performance optimization techniques that you might already be aware of, there's one specific SQLite thing you might find useful if you aren't using it yet. It was new to me, back when: Enable Write-Ahead Logging for SQLite. It's off by default, but WAL is "significantly faster in most scenarios". (Here's one more article on WAL).

I tried enabling WAL in the XamarinTodo sample. For both versions of EF Core tested, the EnsureCreated() time elapsed drops to ~90ms. The time elapsed for GetItemsAsync() is about the same. I expect the time for writing data under WAL would improve, but I didn't test such cases here.

If you're interested in trying WAL, this will do the trick, right after you create your initial DbContext:

    var dbContext = new TodoItemDatabase(databasePath); // or equivalent in your app
    var connection = dbContext.Database.GetDbConnection();
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "PRAGMA journal_mode=WAL;";
        command.ExecuteNonQuery();
    }

I hope this helps.

from xamarintodo.

@cwrea Although I've come to love Xamarin.Android and Xamarin.iOS, in my opinion, EF Core on mobile platforms is much too heavy. It is lovely in concept, but EF Core remains a desktop OS solution. I simply cannot hide the added app launch time and DbContext initialization/setup times. I think that EF Core is too tightly tied to .NET Core and using Azure as the cloud base storage solution.

This morning I converted my SQLite database repository layer from EF Core to SQLite-Net.

Thanks for the WAL input. I'll check it out!

Cheers, Todd

from xamarintodo.

@ToddThomson I like your approach of using a subset of the EF Core API. But, I'll confess I remain interested in following EF Core in part because it can target SQL Server and other databases when used server-side. EF Core's appeal to me is not just for cross-platform for mobile, but potentially re-using data access code on both client and server.

FWIW, before EF Core was a reality, I was looking at adapting PetaPoco to use reflection-based mapping to run in AOT environments such as iOS that couldn't use System.Reflection.Emit dynamic method generation. I never finished the work. (Billable client work came up, and when I circled back around, I lost interest in the rabbit hole.)

I'd previously evaluated a multitude of micro ORMs and came to the conclusion that none did all I wanted — or if they did what I wanted, they were also dependent on System.Reflection.Emit. EF Core was the first ORM that ticked the right boxes. It's a heavy choice right now, but I hope it gets better and/or devices catch up.

But if you do want to roll your own, API consistency is a great approach. If a user of your ORM could design a mobile-first model that performs well in-app, and then bring that source over to full EF Core on the server, that would have some appeal, even if there are some #ifdefs involved.

from xamarintodo.

@cwrea I ported the Xamarin Todo app to utilize Entities.Sqlite. I've only tested it with the Android emulator, but it looks good. Take a look if you like.

from xamarintodo.