您现在的位置是：首页 > 文章详情

[C#]SQLite执行效率优化结论

日期：2018-06-01点击：324收藏

一、如要使用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; }