C#： 避免 SQLite 插入性能问题

如果您是SQLite的新手，那么在尝试进行某种批量插入或更新处理时，您很可能会遇到其实现细节中最令人困惑的一个。

您会发现，除非正确实现，否则在SQLite数据库中插入或更新多条记录可能会异常缓慢。在某些情况下，慢到不适用的程度。

别担心，这与SQLite中的一些默认（并非完全不当）设计选择有关，并且有一个简单的解决方法。

ImagebyLance McCord  |  保留部分权利

SQLite是一个非常易于使用、跨平台/开源的数据库，具有出色的性能指标。它是一个成熟的产品，而且，如果我们相信SQLite.org的估计，它是世界上部署最广泛的SQL数据库。

SQLite成功地将一系列成熟、完善的功能塞进了一个紧凑且文档齐全的软件包中，包括完整的事务支持。

这种事务支持及其实现方式对SQLite的某些性能特征产生了重大影响。

• 在 Windows 上安装和使用 SQLite
• SQLite中的批量处理 - 控制台示例
• 使用BEGIN和COMMIT将事务添加到SQLite脚本中
• 在您的.NET应用程序中通过使用事务来提高SQLite性能
• 示例.NET应用程序 - 缓慢、艰难的方式
• 在您的.NET应用程序中将SQLite批量操作包装在ADO事务中
• 在SQLite中谨慎使用事务
• 其他资源和感兴趣的项目

SQLite中的默认事务

如前所述，SQLite的卖点之一是，尽管它是一个简单的基于文件的数据库，但它完全支持事务。这意味着什么？

来自维基百科

事务包含在数据库管理系统（或类似系统）中对数据库执行的某个工作单元，并以独立于其他事务的一致且可靠的方式处理。数据库环境中的事务有两个主要目的：

1. 提供可靠的工作单元，以允许从故障中正确恢复，并在系统故障、执行停止（完全或部分）且许多数据库操作未完成、状态不明的情况下保持数据库的一致性。
2. 提供并发访问数据库的程序之间的隔离。如果未提供此隔离，则程序的输出可能出错。

根据定义，数据库事务必须是原子性、一致性、隔离性和持久性的。^[1] 数据库从业者通常使用首字母缩写词ACID来指代数据库事务的这些属性。

事务提供“全有或全无”的承诺，声明在数据库中执行的每个工作单元必须要么完全完成，要么根本不产生任何效果。此外，系统必须将每个事务与其他事务隔离，结果必须符合数据库中现有的约束，并且成功完成的事务必须写入持久存储。

当然，SQLite并非唯一实现事务的数据库——事实上，事务是数据库设计的核心概念。然而，SQLite的实现方式是，除非另有说明，否则对数据库的每个单独写入操作（任何修改记录的操作）都被视为一个单独的事务。

换句话说，如果您执行多个INSERT（或UPDATE, orDELETE）在“批处理”中，每个INSERT都将被SQLite视为一个单独的事务。

问题是，事务会带来处理开销。当我们决定需要批量执行多个INSERT时，我们可能会遇到一些令人烦恼的性能瓶颈。

SQLite中的批量处理 - 控制台示例

如果我们在SQLite控制台中使用SQLite，我们可以通过运行一个简单的插入脚本并查看情况来准确地了解我在说什么。对于这个示例，我从Chinook数据库中借用了几行来创建并填充一个艺术家表。如果您的机器上没有SQLite命令行控制台，请立即安装它（有关详细信息，请参阅在Windows上安装和使用SQLite）。然后从我的Github Gist中复制SQL脚本，将其粘贴到文本文件中，并将文件保存到您的用户文件夹中，名称为*create-insert-artists.sql*。

保存前，脚本在文本文件中应如下所示

将SQL脚本粘贴到文本文件中并保存

DROP TABLE IF EXISTS [Artist];
 
CREATE TABLE [Artist]
(
    [ArtistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Artist] PRIMARY KEY  ([ArtistId])
);
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (1, 'AC/DC');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (2, 'Accept');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (3, 'Aerosmith');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (4, 'Alanis Morissette');
 
-- . . . A bunch more artist records . . .
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (273, 'C. Monteverdi, Nigel Rogers');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (274, 'Nash Ensemble');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (275, 'Philip Glass Ensemble');

如果我们在SQLite控制台中打开一个新数据库（为了我们的目的，导航到您的用户文件夹），并读取脚本，我们可以看到它需要多长时间。脚本中有275条艺术家记录需要INSERT。

运行SQLite3，打开一个新数据库，并读取艺术家脚本

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\John>sqlite3
SQLite version 3.8.7.3 2014-12-05 22:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open txdemo.db
sqlite> .read create-insert-artists.sql

我们可以看到（根据您的机器，结果可能有所不同），执行脚本大约需要10秒。插入275条记录不应该花费10秒。永远不应该。

运行脚本的控制台输出（耗时太长！）

如前所述，除非我们另行告知，否则SQLite会将每个INSERT命令视为一个单独的事务，这会大大降低速度。我们可以做得更好。我们通过明确指定我们自己的事务来告诉SQLite覆盖此行为，在INSERT批处理之前开始，并在每个INSERT批处理之后提交。

使用BEGIN和COMMIT将事务添加到SQLite脚本中

当我们执行批量INSERT, UPDATE, orDELETE脚本时，使用BEGIN和COMMITSQLite关键字将对每个表的所有写入操作都包装在一个事务中。通过在表BEGIN之前添加一个INSERT，并在表插入之后添加一个COMMIT，修改文本文件中的*create-insert-artists.sql*脚本（对于涉及多个表的脚本，对每个表的INSERT执行此操作）

修改后的脚本将INSERT操作包装在单个事务中

DROP TABLE IF EXISTS [Artist];
 
CREATE TABLE [Artist]
(
    [ArtistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Artist] PRIMARY KEY  ([ArtistId])
);
 
BEGIN;
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (1, 'AC/DC');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (2, 'Accept');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (3, 'Aerosmith');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (4, 'Alanis Morissette');
 
-- . . . A bunch more artist records . . .
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (273, 'C. Monteverdi, Nigel Rogers');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (274, 'Nash Ensemble');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (275, 'Philip Glass Ensemble');
COMMIT;

如果现在重新运行脚本，我们会看到显著的性能提升。事实上，脚本执行几乎是即时的。

在SQLite控制台中重新运行脚本（这次，使用事务）

以上内容将适用于所有INSERT, UPDATE, andDELETE当您在SQLite控制台中执行脚本时。

在您的.NET应用程序中通过使用事务来提高SQLite性能

当我们在.NET应用程序中使用SQLite时，我们也看到了类似的问题，解决方案在概念上是相同的，尽管实现方式必然会略有不同。如果您是SQLite的新手（许多.NET开发人员在某个时候会遇到这种情况），这正是那种令人困惑的怪癖，它可能会让您在给这个出色的数据库一个机会之前，就跑回另一个“集成”的微软数据库解决方案。“我尝试过SQLite，但插入和更新太慢了……”

示例.NET应用程序 - 缓慢、艰难的方式

考虑以下控制台应用程序示例。它是一个小而简单的示例，没有异常处理，但您能理解其思想。Main()方法执行一些基本设置，然后构建一个List<User>，该列表被传递给AddUsers()方法的新参数。

使用System.Data.SQLite插入用户列表的程序

class Program
{
    static string _connectionString;
    static void Main(string[] args)
    {
        // 'Data' directory in the current directory ( ..\bin\Debug\):
        string dbDirectory = Environment.CurrentDirectory;
        string dbName = "test.db";
 
        // Add System.IO to the using statements at the top of your code file:
        string dbPath = Path.Combine(dbDirectory, dbName);
        _connectionString = string.Format("Data Source = {0}", dbPath);
 
        CreateDbIfNotExists(dbPath);
        CreateUsersTable();
 
        int qtyToAdd = 100;
 
        // Load some users into a list...
        var usersToAdd = new List<User>();
        for(int i = 0; i < qtyToAdd; i++)
        {
            usersToAdd.Add(new User { Name = "User #" + i });
        }
 
        // And THEN add them:
        var sw = new System.Diagnostics.Stopwatch(); ;
        sw.Start();
        int qtyAdded = AddUsers(usersToAdd);
        sw.Stop();
 
        Console.WriteLine("Added {0} Users successfully in {1} ms", 
            qtyAdded, sw.ElapsedMilliseconds);
 
        var allUsers = ReadUsers();
 
        Console.WriteLine("Read {0} Users from SQLite", allUsers.Count());
        Console.Read();
    }
 
 
    static void CreateDbIfNotExists(string dbPath)
    {
        string directory = Path.GetDirectoryName(dbPath);
        if (!File.Exists(dbPath))
        {
            // Creates directory if it doesn't already exist:
            Directory.CreateDirectory(directory);
 
            // Creates file if it doesn't already exist:
            SQLiteConnection.CreateFile(dbPath);
        }
    }
 
 
    static SQLiteConnection CreateConnection()
    {
        return new SQLiteConnection(_connectionString);
    }
 
 
    static void CreateUsersTable()
    {
        string sqlTestTable =
            @"CREATE TABLE IF NOT EXISTS Users 
            ( 
                Id INTEGER PRIMARY KEY AUTOINCREMENT, 
                Name TEXT NOT NULL 
            )";
 
        using (var cn = new SQLiteConnection(_connectionString))
        {
            using (var cmd = new SQLiteCommand(sqlTestTable, cn))
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
    }
 
 
    class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
 
 
    static int AddUsers(IEnumerable<User> users)
    {
        var results = new List<int>();
        string sqlInsertUsers =
            @"INSERT INTO Users (Name) VALUES (@0);";
 
        using (var cn = new SQLiteConnection(_connectionString))
        {
            cn.Open();
            using (var cmd = cn.CreateCommand())
            {
                cmd.CommandText = sqlInsertUsers;
                cmd.Parameters.AddWithValue("@0", "UserName");
                foreach (var user in users)
                {
                    cmd.Parameters["@0"].Value = user.Name;
                    results.Add(cmd.ExecuteNonQuery());
                }
            }
        }
        return results.Sum();
    }
}

2014年12月17日 注意：CP 用户 FZelle 正确指出，上述原始代码以及接下来的示例中，没有重用SQLiteCommand。这里的代码已经更新以正确执行此操作。此外，对Close()的调用是多余的，因为SQLiteConnection被包装在using块中。 

该AddUsers()方法创建一个连接和命令，打开连接，然后遍历IEnumerable<User>，依次将每个用户的data插入到SQLite数据库中。我们正在使用System.Diagnostics.Stopwatch来计时对AddUsers()fromMain().

的调用执行。看起来我们在这里做的一切都是正确的——我们只设置一次连接，只打开一次（每次循环迭代都打开和关闭连接会造成其自身的性能损失）。然而，插入100个用户仍然需要四秒钟以上。我们可以在控制台输出中看到结果。

插入100个用户的示例程序的控制台输出

相当糟糕，但并不令人惊讶，鉴于我们已经了解了SQLite中的事务默认设置。但是，再一次，我们可以做得更好。

在您的.NET应用程序中将SQLite批量操作包装在ADO事务中

与使用SQLite控制台类似，这里的解决方案也是使用事务。我们可以如下修改AddUsers()方法中的代码：

修改后的AddUsers()方法代码，将命令执行包装在事务中

static int AddUsers(IEnumerable<User> users)
{
    var results = new List<int>();
    string sqlInsertUsers = @"INSERT INTO [Users] ([Name]) VALUES (@Name);";
  
    using (var cn = new SQLiteConnection(_connectionString))
    {
        cn.Open();
        using(var transaction = cn.BeginTransaction())
        {
            using (var cmd = cn.CreateCommand())
            {
               cmd.CommandText = sqlInsertUsers;
               cmd.Parameters.AddWithValue("@Name", "UserName");
  
               foreach (var user in users)
               {
                   cmd.Parameters["@Name"] = user.Name;
                   results.Add(cmd.ExecuteNonQuery());
               }
            }
            transaction.Commit();
        }
    }
    return results.Sum();
}

这样，如果我们再次运行应用程序，我们会看到一个数量级的性能提升。

在.NET中使用事务提高SQLite插入性能

是的。52毫秒，从4000多毫秒降下来。

在SQLite中谨慎使用事务

我们已经看到了如何通过使用事务来封装批量操作，从而在SQLite中获得显著的性能提升。然而，我们不能不假思索地本末倒置。有时，您实际上需要更细粒度的事务来确保数据完整性。

如果事务仅在批量操作的顶层实现，那么最大化银行应用程序的性能是行不通的。毕竟，关系数据库世界中的事务首要目标是确保操作要么完全成功，要么完全失败。

其他资源和感兴趣的项目

• 在 Windows 上安装和使用 SQLite
• 在ASP.NET MVC 5和Visual Studio 2013中为Identity帐户配置Db连接和Code-First迁移
• 为Windows用户在虚拟机中设置Linux
• C#：使用 LinqToExcel 查询 Excel 和 .CSV 文件
• C#：使用 DocX 以编程方式创建和操作 Word 文档

Google上的John CodeProject