0

I am currently trying to update the SQLite version in our software from version 1.0.74 to the most recent 1.0.113 (As on 7th December).

I tried some of our most used scenarios in a console application with both the versions.

public class SQLitePerformance {
    public static void Main(string[] args) {
        try {
            var dataSource = @"C:\views\SpikeSqlite.db";
            var conString =
                $"Data Source = {dataSource};Journal Mode = wal;Pooling = true;Max Pool Size = 100;foreign keys = true";

            // Loading SQLite once for warm-up.
            GetSqliteVersionLoaded(conString);
            var watch = Stopwatch.StartNew();
            for (int i = 0; i < 1000; i++) {
                var connection = new SQLiteConnection(conString);
                connection.Open();
            }
            Console.WriteLine($"Opening 1000 connections took : {watch.ElapsedMilliseconds}ms");
        } catch (Exception e) {
            Console.WriteLine(e);
        }
    }

    public static void GetSqliteVersionLoaded(string connectionString) {
        using (var connection = new SQLiteConnection(connectionString)) {
            connection.Open();
            using (var cmd = connection.CreateCommand()) {
                cmd.CommandText = "select sqlite_version();";
                var version = cmd.ExecuteScalar().ToString();
                Console.WriteLine("SQLite version : " + version);
            }
            connection.Close();
        }
    }
}

When I compiled this code with both the versions, here's the result I got :

SQLite version : 3.7.7.1 Opening 1000 connections took : 580ms Press any key to continue . . .

SQLite version : 3.32.1 Opening 1000 connections took : 669ms THE END. Press any key to continue . . .

It's clearly slower than the older version. I know SQLite is not all about opening connections, but an open connection is necessary to perform any operation. It is fair to include this time taken to open a connection in the actual scenario like an INSERT or UPDATE of a row.

Kailash Balasubramanian
  • 3
  • 4
  • Stopwatch is not a benchmarking tool. Run your horses with Benchmark.NET – Fildor Dec 07 '20 at 16:02
  • BTW: a deviation of ~90ms I would totally see inside the bounds of natural jitter due to disk I/O. – Fildor Dec 07 '20 at 16:07
  • According to the [SQLite homepage](https://sqlite.org/index.html) the most recent version is **3.34.0** not 1.0.113 –  Dec 07 '20 at 16:08
  • But [`System.Data.SQLite`](https://www.nuget.org/packages/System.Data.SQLite)'s lates version is 1.0.113 @a_horse_with_no_name - there's quite some version confusion involved here, I think. – Fildor Dec 07 '20 at 17:19
  • `System.Data.SQLite` is a c# wrapper over the actual SQLite. Here we are referring to the the c# wrapper and not the actual SQLite. – Kailash Balasubramanian Dec 11 '20 at 17:55

1 Answers1

0

The major difference between these versions is the change of the default Synchronous Mode in the latest System.Data.SQlite.

Synchronous Mode in SQLite has 4 types FULL, NORMAL EXTRA and OFF. More details about the synchronous modes here => Synchronous Mode

The Synchronous mode must be set before opening the connection and there are multiple ways of doing it

  • By using PRAGMA statements
  • Via the connection string [Easier/Simpler way]

In the older version 1.0.74 the System.Data.SQLite was using Synchronous mode as NORMAL by default, i.e. If we do not set the Synchronous modes using the above mentioned methods, then the default mode with which the connection will behave is NORMAL mode.

But in between 1.0.74 and 1.0.113 Details related to check-in it was changed to FULL.

This change attributes to the degrade that we observed [Synchronous mode as FULL is very slow compared to Synchronous Mode as NORMAL]

This is because we had not explicitly mentioned the Synchronous Mode setting that we wanted and were relying on the default value.

It is advisable to explicitly mention the configurations we will require via the connection string and best to avoid reliance on the default values.

Kailash Balasubramanian
  • 3
  • 4