[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语句,再提交事务,这样的效率是最高的。

 

posted @ 2018-06-01 15:28  cnxy  阅读(7992)  评论(3编辑  收藏  举报
著作版权归 cnxy.me 所有