[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語句，再提交事務，這樣的效率是最高的。