System.Data.SQLite 版本 1.0.74 和最新的 1.0.113 之间有什么变化? [英] What changed between System.Data.SQLite version 1.0.74 and the most recent 1.0.113?

问题描述

我目前正在尝试将我们软件中的 SQLite 版本从 1.0.74 版本更新到最新的 1.0.113(截至 12 月 7 日).

我在两个版本的控制台应用程序中尝试了一些我们最常用的场景.

公共类 SQLitePerformance {public static void Main(string[] args) {尝试 {var dataSource = @C:\views\SpikeSqlite.db";var conString =$"Data Source = {dataSource};Journal Mode = wal;Pooling = true;Max Pool Size = 100;foreign keys = true";//加载一次 SQLite 进行预热.GetSqliteVersionLoaded(conString);var watch = Stopwatch.StartNew();for (int i = 0; i <1000; i++) {var connection = new SQLiteConnection(conString);连接.打开()；}Console.WriteLine($"打开 1000 个连接花费了 : {watch.ElapsedMilliseconds}ms");} 捕获(异常 e){Console.WriteLine(e);}}public static void GetSqliteVersionLoaded(string connectionString) {使用 (var connection = new SQLiteConnection(connectionString)) {连接.打开()；使用 (var cmd = connection.CreateCommand()) {cmd.CommandText = "选择 sqlite_version();";var version = cmd.ExecuteScalar().ToString();Console.WriteLine("SQLite 版本:"+ 版本);}连接.关闭();}}}

当我用两个版本编译这段代码时，这是我得到的结果:

<块引用>

SQLite 版本:3.7.7.1打开 1000 个连接耗时:580 毫秒按任意键继续 ...

SQLite 版本:3.32.1打开 1000 个连接耗时:669 毫秒结束.按任意键继续 ...

它显然比旧版本慢.我知道 SQLite 并不完全是关于打开连接，但是执行任何操作都需要打开连接.将在实际场景中打开连接所花费的时间包括在内是公平的，例如一行的 INSERT 或 UPDATE.

解决方案

这些版本之间的主要区别在于最新System.Data.SQlite<中默认的Synchronous Mode的改变/代码>.

SQLite 中的同步模式有 4 种类型FULL、NORMAL EXTRA 和 OFF.关于这里同步模式的更多细节=>同步模式

必须在打开连接之前设置同步模式，并且有多种方法可以做到

• 通过使用 PRAGMA 语句
• 通过连接字符串[更简单/更简单的方式]

在旧版本 1.0.74 中，System.Data.SQLite 默认使用同步模式作为 NORMAL，即如果我们不使用上面提到的方法，那么连接行为的默认模式是NORMAL模式.

但在 1.0.74 和 1.0.113 之间 与入住相关的详细信息已更改为FULL.

这种变化归因于我们观察到的降级 [同步模式为 FULL 与同步模式为 NORMAL 相比非常慢]

这是因为我们没有明确提到我们想要的同步模式设置，而是依赖于默认值.

建议通过连接字符串明确提及我们将需要的配置，并最好避免依赖默认值.

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.

解决方案

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.

这篇关于System.Data.SQLite 版本 1.0.74 和最新的 1.0.113 之间有什么变化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！