C# SQLite执行效率的优化教程

关于SQLite

SQLite是一款轻型的嵌入式的遵守ACID的关系型数据库管理系统，诞生已有15个年头了。随着移动互联的发展，现在得到了更广泛的使用。

在使用SQLite之前，我们势必要先了解它一些“个性”的地方。下面是它的一些特点：

1、 自包含。SQLite很大层度上是独立的，他只需要非常小的外部库支持。任何程序能够访问磁盘就可以使用SQLite数据库。这使它适用于嵌入式设备，缺乏桌面计算机支持的基础设施。这也使得SQLite适用于不作任何修改就可运行在不同配置电脑上的程序。

2、 无服务器。大多数SQL数据库引擎被实现为一个单独的服务器进程。程序要访问数据库与服务器通信使用某种形式的进程间通信(通常是TCP / IP)，向服务器发送请求并接收返回结果。SQLite则不是这种工作方式。对于SQLite，想要访问数据库直接从磁盘上的对数据库文件执行读和写操作。没有中间的服务器进程。

3、 零配置。使用SQLite不需要“安装”。没有“设置”程序。没有服务器进程需要启动，停止，或配置。不需要管理员来创建一个新的数据库实例或访问权限分配给用户。SQLite不使用配置文件。

4、 支持事务。事务数据库的所有更改和查询表现出原子性、一致性、隔离性、持久性(ACID)。执行SQLite的事务操作时，要么完全执行，要么不执行，即使写入磁盘的操作被程序崩溃，断电等故障打断。

5、 开源。和前面的特点相比，这个似乎没有多大关系。之所以把它作为一个特点，是因为开源在很大层度上会成为我们选择一个解决方案的重要维度。

本文主要介绍的是关于C# SQLite执行效率优化的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧

一、如要使用SQLite，可以从Visual Studio中的“程序包管理器控制台”输入以下命令完成安装：

PM> Install-Package System.Data.SQLite.Core

SQLite则会安装到项目中，支持32位或64位，如下图所示：

二、新建一个SQLite数据库，名称命名为Test.db，其表名称及列定义如下：

三、新建一个控制台应用的解决方案，并输入以下代码，看看SQLite的执行时间：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;

namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
 SQLiteConnection connection = Run(() => new SQLiteConnection("Data Source = Test.db"), "连接对象初始化");
 Run(() => connection.Open(), "打开连接");
 SQLiteCommand command = Run(() => new SQLiteCommand(connection), "命令对象初始化");
 Run(() =>
 {
 command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';";
 command.ExecuteNonQuery();
 }, "执行DELETE命令及收缩数据库");
 Run(() =>
 {
 for (int i = 0; i < 3000; i++)
 {
  command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')";
  command.ExecuteNonQuery();
 }
 command.ExecuteScalar();
 }, "[---使用事务---]事务执行INSERT命令");
 List<Test> list1 = Run(() =>
 {
 command.CommandText = $"SELECT * FROM Info";
 List<Test> tests = new List<Test>();
 SQLiteDataReader reader = command.ExecuteReader();
 while (reader.Read())
 {
  Test t = new Test
  {
  ID = (long)reader[0],
  Name = (string)reader[1],
  Age = (long)reader[2]
  };
  tests.Add(t);
 }
 reader.Close();
 return tests;
 }, "[---不使用事务---]使用ExecuteReader方式执行SELECT命令");
 DataTable table1 = Run(() =>
 {
 command.CommandText = $"SELECT * FROM Info";
 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
 DataTable _table = new DataTable();
 adapter.Fill(_table);
 return _table;
 }, "[---不使用事务---]使用Fill Table方式执行SELECT命令");

Run(() =>
 {
 command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';";
 command.ExecuteNonQuery();
 }, "执行DELETE命令及收缩数据库");
 SQLiteTransaction transaction = Run(() => connection.BeginTransaction(), "开始事务");
 Run(() =>
 {
 for (int i = 0; i < 3000; i++)
 {
  command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')";
  command.ExecuteNonQuery();
 }
 var result = command.ExecuteScalar();
 }, "[---使用事务---]执行INSERT命令");
 List<Test> list2 = Run(() =>
 {
 command.CommandText = $"SELECT * FROM Info";
 List<Test> tests = new List<Test>();
 SQLiteDataReader reader = command.ExecuteReader();
 while (reader.Read())
 {
  Test t = new Test
  {
  ID = (long)reader[0],
  Name = (string)reader[1],
  Age = (long)reader[2]
  };
  tests.Add(t);
 }
 reader.Close();
 return tests;
 }, "[---使用事务---]使用ExecuteReader方式执行SELECT命令");
 DataTable table2 = Run(() =>
 {
 command.CommandText = $"SELECT * FROM Info";
 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
 DataTable _table = new DataTable();
 adapter.Fill(_table);
 return _table;
 }, "[---使用事务---]使用Fill Table方式执行SELECT命令");
 Run(() => transaction.Commit(), "提交事务");
 Run(() => connection.Close(), "关闭连接");
 Console.ReadKey();
}

public static void Run(Action action,string description)
{
 Stopwatch sw = Stopwatch.StartNew();
 action();
 Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms");
}

public static T Run<T>(Func<T> func, string description)
{
 Stopwatch sw = Stopwatch.StartNew();
 T result = func();
 Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms");
 return result;
}
}

class Test
{
public long ID { set; get; }
public string Name { set; get; }
public long Age { set; get; }
}
}

程序运行结果如下：

四、根据以上的程序运行结果，可以得出以下结论：

1）SQLiteConnection对象初始化、打开及关闭，其花费时间约为109ms，因此，最好不要频繁地将该对象初始化、打开与关闭，这与SQL Server不一样，在这里建议使用单例模式来初始化SQLiteConnection对象；

在网上查找了SQLiteHelper帮助类，但很多都是没执行一次SQL语句，都是使用这样的流程：初始化连接对象->打开连接对象->执行命令->关闭连接对象，如下的代码所示：

public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
{
 int affectedRows = 0;
 using (SQLiteConnection connection = new SQLiteConnection(connectionString))
 {
 using (SQLiteCommand command = new SQLiteCommand(connection))
 {
  try
  {
  connection.Open();
  command.CommandText = sql;
  if (parameters.Length != 0)
  {
   command.Parameters.AddRange(parameters);
  }
  affectedRows = command.ExecuteNonQuery();
  }
  catch (Exception) { throw; }
 }
 }
 return affectedRows;
}

根据以上的结论，如果要求执行时间比较快的话，这样的编写代码方式实在行不通。

2）使用ExecuteReader方式比使用Adapter Fill Table方式快一点点，但这不是绝对的，这取决于编写的代码；

3）无论是执行插入或查询操作，使用事务比不使用事务快，尤其是在批量插入操作时，减少得时间非常明显；

比如在不使用事务的情况下插入3000条记录，执行所花费的时间为17.252s，而使用事务，执行时间只用了0.057s，效果非常明显，而SQL Server不存在这样的问题。

4）不能每次执行一条SQL语句前开始事务并在SQL语句执行之后提交事务，这样的执行效率同样是很慢，最好的情况下，是在开始事务后批量执行SQL语句，再提交事务，这样的效率是最高的。

来源：https://www.cnblogs.com/cncc/p/9121874.html