Poor Performance Sqlite.net extensions

Question

Hi I am developing a Xamarin app that load lots of lines ( around 10K )

I got really poor performance something like 2 minutes to insert 5K lines of an object.

I use InsertOrReplaceWithChildren:

 public  bool Insert_Update_Many<T>(List<T> obj)
    {
        try
        {
            lock (this.Lock) {
                _connection.RunInTransaction(() => {
                    _connection.InsertOrReplaceAllWithChildren(obj, true);
                });
            }
            return true;
        }
        catch (Exception ex)
        {
            Debug.WriteLine("[SQLITE_ERROR]: " + ex.ToString());
            return false;
        }
    }

Since I add "RunInTransaction" I got an improvement (before it was 5 min+).

My objects contains relations ships.

Is there any way to optimize that ?

Answer 1

It's hard to tell without seeing the actual code, but I faced few issues that may affect performance:

• InsertOrReplace performance is bad: Try calling simple Insert statements instead of InsertOrReplace. In some scenarios this may have a big impact.
• SQLite.Net performs insert operations one by one: this one complex to workaround, as it requires you to write insert queries manually to perform more than one insert on each statement.
• SQLite-Net Extensions performs update operations after insert: this is simple to workaround, you can assign foreign keys by yourself and call plain SQLite.Net Insert on database intensive operations.