make your SQLite bulk Insert fast

Question

Make your SQLite bulk inserts very fast in C# .NET

16 Mar 2012

In SQLite if you insert one record at a time then they are wrapped in individual transactions. In order to for your bulk inserts to perform very fast then you need to wrap them all in a single transaction. See SQLite FAQ. You should be able to able to insert very quickly without having to worry about PRAGMAs

I was able to insert 1 million rows in about 4 seconds.

I completed this example with a c# console application in Visual Studio 2010. I installed the NuGet package called System.Data.SQLite (x86) version 1.0.79.0. This will add a project reference to System.Data.SQLite (and System.Data.SQLite.Linq which is not needed for this example). The NuGet package is "The official SQLite database engine combined with a complete ADO.NET provider all rolled into a single mixed-mode assembly for x86."

Create your Person Table in SQLite for our test:

CREATE TABLE IF NOT EXISTS Person (FirstName TEXT, LastName TEXT);

Don't forget your using statement: using System.Data.SQLite;

// Creates new sqlite database if it is not found
using (var conn = new SQLiteConnection(
    @"Data Source=C:\Projects\sqlite\test.sqlite"))
{
  // Be sure you already created the Person Table!

  conn.Open();

  var stopwatch = new Stopwatch();
  stopwatch.Start();

  using (var cmd = new SQLiteCommand(conn))
  {
    using (var transaction = conn.BeginTransaction())
    {
        // 100,000 inserts
        for (var i = 0; i < 1000000; i++)
        {
            cmd.CommandText =
                "INSERT INTO Person (FirstName, LastName) VALUES ('John', 'Doe');";
            cmd.ExecuteNonQuery();
        }

        transaction.Commit();
    }
  }

  Console.WriteLine("{0} seconds with one transaction.",
    stopwatch.Elapsed.TotalSeconds);

  conn.Close();
}

Answer 1

// làm việc với sqlserver
var stopwatch = new Stopwatch();
            stopwatch.Start();         
            string query = $@"INSERT INTO AttLog(ipAddress,
                                                userID,
                                                dateTimeRecord)
                            SELECT  @ipAddress,
                                    @userID,
                                    @dateTimeRecord
                                        WHERE NOT EXISTS ( SELECT 0 FROM AttLog WHERE ipAddress=@ipAddress and userID=@userID and dateTimeRecord=@dateTimeRecord)
            ";
            using (var connection = new SqlConnection(SQLHelper.CONNECTION_STRINGS))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var cmd = new SqlCommand(query, connection, transaction))
                    {
                        int result_count = 0;
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = transaction;
                        try
                        {
                            foreach (var item in lstMachineInfo)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@ipAddress", _ipAddress);
                                cmd.Parameters.AddWithValue("@userID", item.IndRegID);
                                cmd.Parameters.AddWithValue("@dateTimeRecord", item.DateTimeRecord);                             
                                result_count += cmd.ExecuteNonQuery();
                            }
                            transaction.Commit();


                        }
                        catch (Exception ex)
                        {                            
                            try
                            {
                                transaction.Rollback();
                            }
                            catch (SqlException exx)
                            {
                                if (transaction.Connection != null)
                                {
                                   ConsoleHelper.ShowErrorMessage($"An exception of type {exx.GetType()} was encountered while attempting to roll back the transaction.");

                                }
                            }

                            ConsoleHelper.ShowErrorMessage($"Neither record was written to database.");


                        }
                        finally
                        {
                            connection.Close();
                            ConsoleHelper.ShowSuccessMessage($"Upload dữ liệu hoàn tất. Đã thêm mới được {result_count} tin.");
                        }
                    }
                }

            }

            ConsoleHelper.ShowDefaultMessage(String.Format("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds));