System.Data.SQLite

We are able to notice a significant degrade in latest SQLite packages (1.0.113) due to some of the newly added features like .xml/Environment variable configurations. As part of migrating to latest SQLite (1.0.113) from our existing version (1.0.74) we encounter a lot of issues related to performance. We see latest version taking about 2-3 times more time than our existing version. On analysis we see the below issues that needs some attention :

* There are configuration reads that happen in the constructors. in the recent versions, there are numerous calls that are accessing Environment variables and xml files.
These reads are happening in a connection open, which results in degrading the performance of almost all the different Sqlite operations (Like Insert, Select etc)

* The way in which the connections are retrieved from a connection pool is also slow. There are binary searches happening which are taking significant time.

Please see the below table for a comparison on how the performance has degraded. [The numbers are for 100 iterations of each operations.]

Scenario	version 1.0.74	version 1.0.113
Open	8ms	25ms
Close	2ms	6ms
Insert	10720ms	27937ms
Flat Query	20ms	40ms
Update	15ms	35ms
Delete	3768ms	3598ms

We have the profiles taken during measurement which can help in better understanding where the problem is. Get in touch for more details. We are ready to help. Find the code we used for reference below.

Pre-Requisite : An Sqlite DB with 3 tables, with 2000 records each and each table having 20 columns. Compile the same code with different versions of SQLite.

Thanks in Advance!

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;

namespace SqlitePerformance {
    public static class SqlitePerformanceMeasurement {
        private static long InsertTimeInMs;
        private static long CreateTimeInMs;
        private static long UpdateTimeInMs;
        private static long DeleteTimeInMs;
        private static readonly string broadQueryCommandText = "SELECT storables from Table2";

        private static readonly string queryWithWhereClause =
            "select storables from Table2 where col1='1.3.46.670589.5.2.10.2.4.46.678.1.6072.1211920446095.5' AND deviceId='SQLiteDatabase';";

        public static string ConnectionString { get; set; } =
            "Data Source=" + Path.Combine(Path.GetTempPath(), "SpikeSqlite.db") + ";Version=3;foreign keys = True;Journal Mode = Wal;Pooling=True;Max Pool Size = 100";

        public static string InsertConnectionString { get; set; } =
            "Data Source=" + Path.Combine(Path.GetTempPath(), "SpikeInsert.db") + ";Version=3;foreign keys = True;Journal Mode = Wal;Pooling=True;Max Pool Size = 100";
       

        public static void BroadQuery(int numberOfQueries) {
            ActualQuery(numberOfQueries, broadQueryCommandText);
        }

        public static void QueryWithWhereClause(int numberOfQueries) {
            ActualQuery(numberOfQueries, queryWithWhereClause);
        }

        public static void ActualQuery(int numberOfConnections, string cmdText) {
            for (int i = 0; i < numberOfConnections; i++) {
                using (var con = new SQLiteConnection(ConnectionString)) {
                    con.Open();
                    using (var cmd = new SQLiteCommand(cmdText, con)) {
                        cmd.ExecuteReader();
                    }
                    con.Close();
                }
            }
        }

        /// 

        /// Method that prints the version of SQLite assembly loaded.
        /// 
        public static void GetSqliteVersionLoaded() {
            using (var conn = new SQLiteConnection(ConnectionString)) {
                conn.Open();
                var cmd = new SQLiteCommand("select sqlite_version();", conn);
                var version = cmd.ExecuteScalar().ToString();
                Console.WriteLine(version);
                conn.Close();
            }
        }

        private static DateTime dateTime = DateTime.Now;
        private static string updateCommandText =
            $"UPDATE Table2 SET col1='{dateTime}', storables=@storables WHERE col3='1.3.46.670589.5.2.10.2.4.46.678.1.6072.1211920446095.5' AND deviceId='SQLiteDatabase'";

        public static void UpdateColumns(int numberOfIterations) {
            for (int i = 0; i < numberOfIterations; i++) {
                using (var con = new SQLiteConnection(ConnectionString)) {
                    con.Open();
                    using (var cmd = new SQLiteCommand(updateCommandText, con)) {
                        cmd.Parameters.AddWithValue("@storables", DummyStorables.Create(i));
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        public static void DeleteRow(int numberOfIterations) {
            var conString =
                "Data Source=" + Path.Combine(Path.GetTempPath(), "SpikeSqlite-Clone.db") + ";Version=3;foreign keys = True; Pooling=True;Max Pool Size = 100;Journal Mode = Wal";
            var rows = File.ReadAllLines(Path.Combine(Path.GetTempPath(), "DeleteRows.txt"));
            for (int i = 0; i < numberOfIterations; i++) {
                var commandText =
                    $"DELETE FROM Table2 where uid='{rows[i].Trim('\"')}' AND deviceId='SQLiteDatabase';";
                ActualDelete(conString, commandText);
            }
        }

        private static void ActualDelete(string conString, string commandText) {
            using (var con = new SQLiteConnection(conString)) {
                con.Open();
                using (var cmd = new SQLiteCommand(commandText, con)) {
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
        }

        public static void Show() {
            var builder = new StringBuilder();
            builder.Append("--- SQLite Performance Spike ---");
            builder.AppendLine("Choose the operation to perform");
            builder.AppendLine("1. Connection Open.");
            builder.AppendLine("2. Broad Query.");
            builder.AppendLine("3. Query with where clause.");
            builder.AppendLine("4. Update Row.");
            builder.AppendLine("5. Delete Row.");
            builder.AppendLine("6. Insert Row.");
            Console.WriteLine(builder.ToString());
            var operation = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());
            builder.Clear();

            builder.Append("How many iterations? : ");
            Console.WriteLine(builder.ToString());
            var iterations = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());
            switch (operation) {
                case 1:
                    MultipleOpenCLoseConnections(iterations);
                    break;
                case 2:
                    BroadQuery(iterations);
                    break;
                case 3:
                    QueryWithWhereClause(iterations);
                    break;
                case 4:
                    UpdateColumns(iterations);
                    break;
                case 5:
                    DeleteRow(iterations);
                    break;
                case 6:
                    InsertOperation(iterations);
                    break;
                default: throw new InvalidOperationException("Invalid input");
            }
        }

        private static void InsertOperation(int iterations)
        {
            foreach (var level in new[] { "TABLE1", "TABLE2", "TABLE3" }) {
                InsertOperation(GetDbData(level), level);
            }
        }
        private static void InsertOperation(List> data, string level) {
            var columns = new List();
            foreach (var d in data.First()) {
                columns.Add(d.Key);
            }

            var insertCols = new StringBuilder();
            foreach (var column in columns) {
                insertCols.Append(column);
                insertCols.Append(",");
            }

            var valuesCols = new StringBuilder();
            foreach (var column in columns) {
                valuesCols.Append("@");
                valuesCols.Append(column);
                valuesCols.Append(",");
            }

            var insertCommandText = 
                $"Insert into {level} ({insertCols.ToString().TrimEnd(',')}) values ({valuesCols.ToString().TrimEnd(',')});";
            ActualInsert(insertCommandText, data);
        }

        /// 

        /// Create connection, Open it, Insert the row, Close and Dispose the connection.
        /// 
        /// 
        /// 
        private static void ActualInsert(string cmdText, List> data) {
            // Insert each row
            foreach (var eachRow in data) {
                using (var con = new SQLiteConnection(InsertConnectionString)) {
                    con.Open();
                    using (var cmd = con.CreateCommand()) {
                        cmd.CommandText = cmdText;
                        
                        // Populate column values
                        foreach (var eachItem in eachRow) {
                            cmd.Parameters.AddWithValue("@" + eachItem.Key, eachItem.Value);
                        }

                        cmd.CommandText += "; Select last_insert_rowid()";

                        // Insert 
                        cmd.ExecuteScalar();

                        cmd.Parameters.Clear();
                    }
                }
            }
        }

        /// 

        /// Utility to get the data from the table to use and delete the data from the table.
        /// 
        /// 
        /// 
        private static List> GetDbData(string level) {
            var resultList = new List>();
            using (var con = new SQLiteConnection(ConnectionString)) {
                con.Open();

                using (var cmd = new SQLiteCommand($"SELECT * from {level}", con)) {
                    var reader = cmd.ExecuteReader();
                    while (reader.Read()) {
                        var tagValue = new Dictionary();
                        for (int i = 0; i < reader.FieldCount; i++) {
                            tagValue.Add(reader.GetName(i), reader.GetValue(i));
                        }
                        resultList.Add(tagValue);
                    }
                }
                con.Close();
            }

            return resultList;
        }

        /// 

        /// Creates, Opens and Closes multiple connections.
        /// 
        /// 
        private static void MultipleOpenCLoseConnections(int iterations) {
            SQLiteConnection[] connections = new SQLiteConnection[iterations];

            for (int i = 0; i < iterations; i++) {
                connections[i] = new SQLiteConnection(ConnectionString);
            }
            for (int i = 0; i < iterations; i++) {
                using (var con = connections[i]) {
                    con.Open();
                    con.Close();
                }
            }
        }
    }

    /// 

    /// Dummy class to generate Byte array that is  stored as blob
    /// 
    internal static class DummyStorables {
        public static IReadOnlyCollection Create(int seed) {
            var rand = new Random(Seed: seed);
            var byteArray = new byte[2020];
            rand.NextBytes(byteArray);
            return byteArray;
        }
    }

}

Thanks for the detailed report.  I was able to find several places where
unnecessary work was being performed during SQLiteConnection construction.
These have been addressed via trunk check-in [438037ced66e89a2].

I'm not sure how to optimize the Open method without losing significant
features and/or breaking backwards compatibility.

As far as the connection pool, it was designed primarily for thread safety
and friendliness to the .NET garbage collector.  It is extremely sensitive
code in terms of correctness and thread safety.  I'm not sure how to make
it faster; however, I'm open to any specific suggestions you may have.

I'm curious, why create and open so many connections?  Part of the problem
here is that the SQLiteConnection constructor and Open / Close methods are
not designed to be used so frequently.  Parsing a connection string is not
cheap in the best of circumstances.

I believe most of the big performance issues described in this ticket are now
fixed.