[C #] SQLite Execution Efficiency Optimization conclusion

[C#]sqlite Execution Efficiency optimization conclusion

First, to use SQLite, you can complete the installation from the Package Manager console in Visual Studio by entering the following command:

Pm> Install-package System.Data.SQLite.Core

SQLite is installed in the project and supports either 32-bit or 64-bit, as shown in:

Second, create a new SQLite database, named Test.db, whose table name and column are defined as follows:

Third, create a new console application solution, and enter the following code to see the execution time of 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"), "Connection object initialization");
            Run(() => connection.Open(), "Open Connection");
            SQLiteCommand command = Run(() => new SQLiteCommand(connection), "command object initialization");
            Run(() =>
            {
                command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name =‘Info‘;";
                command.ExecuteNonQuery();
            }, "Execute DELETE command and shrink database");
            Run(() =>
            {
                For (int i = 0; i < 3000; i++)
                {
                    command.CommandText = $"INSERT INTO Info(Name, Age) VALUES (‘A{i:000}‘, ‘{i}‘)”;
                    command.ExecuteNonQuery();
                }
                command.ExecuteScalar();
            }, "[---Use transaction---] transaction to execute INSERT command");
            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;
            }, "[---Do not use transaction---] ExecuteReader method to execute SELECT command");
            DataTable table1 = Run(() =>
            {
                command.CommandText = $"SELECT * FROM Info";
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable _table = new DataTable();
                adapter.Fill(_table);
                Return _table;
            }, "[---Do not use transaction---] use the Fill Table method to execute the SELECT command");

            Run(() =>
            {
                command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name =‘Info‘;";
                command.ExecuteNonQuery();
            }, "Execute DELETE command and shrink database");
            SQLiteTransaction transaction = Run(() => connection.BeginTransaction(), "Start transaction");
            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();
            }, "[---Use transaction---] execute INSERT command");
            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;
            }, "[---Use transaction---] ExecuteReader method to execute SELECT command");
            DataTable table2 = Run(() =>
            {
                command.CommandText = $"SELECT * FROM Info";
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable _table = new DataTable();
                adapter.Fill(_table);
                Return _table;
            }, "[---Use transaction---] use the Fill Table method to execute the SELECT command");
            Run(() => transaction.Commit(), "Submit transaction");
            Run(() => connection.Close(), "Close Connection");
            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; }
    }
} 

The results of the program run as follows:

Four, according to the above procedure operation result, can draw the following conclusion:

1) Sqliteconnection object initialization, opening and closing, it takes about 109ms, so it is best not to frequently initialize, open and close the object , which is not the same as SQL Server, It is recommended to use Singleton mode to initialize the Sqliteconnection object;

The Sqlitehelper help class was found on the internet, but many of them did not execute the SQL statement all at once, using the process: Initialize Connection object, open Connection object, execute command, and close the Connection object, as shown in the following code:

  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;  
        } 

According to the above conclusion, if the execution time is faster, this way of writing code is not feasible.

2) using the ExecuteReader method is a little faster than using the adapter Fill table , but this is not absolute, depending on the code being written;

3) Whether you are performing an insert or query operation, using transactions is faster than not using transactions , especially when bulk insert operations, the reduction time is very obvious;

For example, to insert 3,000 records without a transaction, the time to execute is 17.252s, and the use of transactions, the execution time is only 0.057s, the effect is very obvious, and SQL Server does not exist such a problem.

4) It is not possible to start a transaction before executing an SQL statement and commit the transaction after the SQL statement executes, so the execution efficiency is also very slow, preferably in the case of batch execution of SQL statements after starting a transaction, and then committing the transaction , which is the highest efficiency.

[C #] SQLite Execution Efficiency Optimization conclusion