Wstawianie wielu rekordów do tabeli SqLite

Poprzednio opisałem w jaki sposób można poradzić sobie z wstawianiem wielu rekordów do bazy PostgreSql. Tym razem na warsztat wziąłem SqLite. Biorąc pod uwagę brak potrzeby konfiguracji, jest ona świetną alternatywą przechowywania wszystkich ustawień aplikacji itp.

Bazę i jej schemat tworzy metoda CreateDatabase:

Code:

        private static void CreateDatabase()
        {
            SQLiteConnection.CreateFile("database.sqlite");
            using (var connection = GetSqLiteConnection())
            {
                using(var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"CREATE TABLE Table1 (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name1 VARCHAR,
Name2 VARCHAR,
Name3 VARCHAR,
Name4 VARCHAR);";
                    cmd.ExecuteNonQuery();
                }
            }
        }

Testy zostały wykonane przy 80k wierszy:
1. Zwykły insert:
Jak w poprzednim przypadku najmniej wydajne rozwiązanie - dla każdego pojedynczego INSERTA jest tworzona osobna transakcja co wydłuża proces wstawiania danych:

Code:

        private static long NormalInsert(List<Table1> data, SQLiteConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Table1(Name1,Name2,Name3,Name4) VALUES(@Name1,@Name2,@Name3,@Name4);";
                cmd.Parameters.AddWithValue("@Name1", "");
                cmd.Parameters.AddWithValue("@Name2", "");
                cmd.Parameters.AddWithValue("@Name3", "");
                cmd.Parameters.AddWithValue("@Name4", "");
                for (int i = 0; i < data.Count; i++)
                {
                    cmd.Parameters["@Name1"].Value = data[i].Name1;
                    cmd.Parameters["@Name2"].Value = data[i].Name2;
                    cmd.Parameters["@Name3"].Value = data[i].Name3;
                    cmd.Parameters["@Name4"].Value = data[i].Name4;
                    cmd.ExecuteNonQuery();
                }
            }

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }

2. Transakcja
Wykorzystując jedną transakcję do wstawienia dużej ilości rekordów przyśpieszymy całą operację wielokrotnie, co też zobaczymy w wynikach:

Code:

        private static long TransactionInsert(List<Table1> data, SQLiteConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.Transaction = connection.BeginTransaction();
                cmd.CommandText = "INSERT INTO Table1(Name1,Name2,Name3,Name4) VALUES(@Name1,@Name2,@Name3,@Name4);";
                cmd.Parameters.AddWithValue("@Name1", "");
                cmd.Parameters.AddWithValue("@Name2", "");
                cmd.Parameters.AddWithValue("@Name3", "");
                cmd.Parameters.AddWithValue("@Name4", "");
                for (int i = 0; i < data.Count; i++)
                {
                    cmd.Parameters["@Name1"].Value = data[i].Name1;
                    cmd.Parameters["@Name2"].Value = data[i].Name2;
                    cmd.Parameters["@Name3"].Value = data[i].Name3;
                    cmd.Parameters["@Name4"].Value = data[i].Name4;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            stopwatch.Stop();

            return stopwatch.ElapsedMilliseconds;
        }

3. Transakcja wraz z przygotowaniem zapytania

Code:

        private static long TransactionInsertWithPrepare(List<Table1> data, SQLiteConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.Transaction = connection.BeginTransaction();
                cmd.CommandText = "INSERT INTO Table1(Name1,Name2,Name3,Name4) VALUES(@Name1,@Name2,@Name3,@Name4);";
                cmd.Parameters.AddWithValue("@Name1", "");
                cmd.Parameters.AddWithValue("@Name2", "");
                cmd.Parameters.AddWithValue("@Name3", "");
                cmd.Parameters.AddWithValue("@Name4", "");
                cmd.Prepare();
                for (int i = 0; i < data.Count; i++)
                {
                    cmd.Parameters["@Name1"].Value = data[i].Name1;
                    cmd.Parameters["@Name2"].Value = data[i].Name2;
                    cmd.Parameters["@Name3"].Value = data[i].Name3;
                    cmd.Parameters["@Name4"].Value = data[i].Name4;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            stopwatch.Stop();

            return stopwatch.ElapsedMilliseconds;
        }

4. Synchronous = OFF
Podczas zapisu danych do bazy tworzone są punkty kontrolne, przy których sprawdzany jest stan zapisu danych w pliku bazy. Jeżeli wyłączymy synchronizację, unikniemy sprawdzania, jednak musimy się wtedy liczyć z tym, iż w razie awarii sprzętu (np. wyłączenia prądu) może dojść do uszkodzenia pliku bazy danych.

Code:

        private static long TransactionPrepareSynchronousOff(List<Table1> data, SQLiteConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "PRAGMA synchronous = OFF";
                cmd.ExecuteNonQuery();
                cmd.Transaction = connection.BeginTransaction();
                cmd.CommandText = "INSERT INTO Table1(Name1,Name2,Name3,Name4) VALUES(@Name1,@Name2,@Name3,@Name4);";
                cmd.Parameters.AddWithValue("@Name1", "");
                cmd.Parameters.AddWithValue("@Name2", "");
                cmd.Parameters.AddWithValue("@Name3", "");
                cmd.Parameters.AddWithValue("@Name4", "");
                for (int i = 0; i < data.Count; i++)
                {
                    cmd.Parameters["@Name1"].Value = data[i].Name1;
                    cmd.Parameters["@Name2"].Value = data[i].Name2;
                    cmd.Parameters["@Name3"].Value = data[i].Name3;
                    cmd.Parameters["@Name4"].Value = data[i].Name4;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            stopwatch.Stop();

            return stopwatch.ElapsedMilliseconds;
        }

Zobaczmy na wyniki testu:

Czas wstawiania dla zwykłych insertów jest wręcz nie do zaakceptowania. Lepszy obraz całości przedstawia wykres pokazujący ilość wstawianych rekordów na sekundę: