sqlite commit performance problem with indexes

I have run into a problem where the time to do a commit starts taking longer and longer. We are talking on the orders of 250ms for a table with ~ 20k lines and a disc size of around 2-3mb. And it just keeps getting worse. I have tracked the performance problem down to something to do with indexs. It's almost as if sqlite is creating the index on every commit. The commit consists of 100 INSERTS. I have made a as small program as I could where I can reproduce the problem and have tried running this on Linux as well. There the problem doesn't seem to occur. The problem exists with both WAL and truncate journaling mode. The problem doesn't seem to exist when I use a memory database instead of a file. I have tried both version 3.6.23.1 and 3.7.6.3.

On Windows where I'm experiencing the problem I run sqlite in a C# program. I have checked the implementation of transaction support in the System.Date.Sqlite wrapper and it does absolutely nothing else than simply to a COMMIT. Sadly I don't have a C compiler for Windows so I can't check it when not running the wrapper, but it should be the same.

System.IO.File.Delete("test.db");

var db_connection = new SQLiteConnection(@"Data Source=test.db");

db_connection.Open();

using (var cmd = db_connection.CreateCommand())
{
    cmd.CommandText = "CREATE TABLE test (id integer primary key, dato integer)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "CREATE INDEX i on test(dato)";
    c开发者_运维问答md.ExecuteNonQuery();
}

SQLiteTransaction trans = null;

List<string> paths = new List<string>();

var random = new Random();

for (var j = 0; j < 150; ++j)
{
    for (var i = 0; i < 1000; ++i)
    {
        if (i % 100 == 0)
        {
            trans = db_connection.BeginTransaction();
        }

        using (var cmd = db_connection.CreateCommand())
        {
            cmd.CommandText = String.Format("INSERT INTO test (dato) values ({0})", random.Next(1, 100000000));
            cmd.ExecuteNonQuery();
        }

        if (i % 100 == 99 && trans != null)
        {
            var now = DateTime.Now;

            trans.Commit();
            trans.Dispose();

            System.Console.WriteLine("commit {0}", (DateTime.Now - now).TotalMilliseconds);
        }
    }
}

---

Did you try reducing hard disk access, for example adding this command before creating any table:

cmd.CommandText = "PRAGMA locking_mode = EXCLUSIVE";
cmd.ExecuteNonQuery();

Providing your app allows exclusive locking of the database.

Also can help:

PRAGMA Synchronous=OFF