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 ?

score 3 · Accepted Answer · answered Apr 21 '16 at 15:57

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.

I think you right I have to re write my logic for my scenario. — Ayrton Werck, Apr 22 '16 at 07:51

score 3 · Answer 2 · answered Apr 21 '16 at 16:38

3

You can improve insert performance by using WAL mode:

 var journalMode = await globalConn.ExecuteScalarAsync<string>("PRAGMA journal_mode = wal");

answered Apr 21 '16 at 16:38

Giorgi

30,270
13
89
125

It improves a little but not significantly :( – Ayrton Werck Apr 22 '16 at 07:50

score 0 · Answer 3 · answered Apr 21 '16 at 15:49

0

Here's a suggestion:

Try with raw queries, or actually, first profile the code, maybe there's a bottleneck somewhere in there.

And if you get nothing, I guess you'll just have to do it async and have user wait (or ship the database file with your app)

answered Apr 21 '16 at 15:49

nullpotent

9,162
1
31
42

I did and the problem is in the insert... When I use none sqlite extensions method it is actually fast but I lost the advantages of the extensions. shipping the database with the app is not an option as the data are set on a back office and modify many time – Ayrton Werck Apr 21 '16 at 15:51

Poor Performance Sqlite.net extensions

3 Answers3