Dodawanie dużej ilości rekordów do bazy PostgreSql

Zadaniem jest wstawienie 80 tys. rekordów do przykładowej tabeli której strukturę podano poniżej:

CREATE TABLE Table2(
  Id serial NOT NULL,
  Name1 CHARacter VARYING(60),
  Name3 CHARacter VARYING(60),
  Name2 CHARacter VARYING(60),
  Name4 CHARacter VARYING(60),
  CONSTRAINT "Table2_pkey"PRIMARY KEY (Id)
);

Kod tworzący dane testowe:

Code:

    public class Table1
    {
        public int Id { get; set; }
        public string Name1 { get; set; }
        public string Name2 { get; set; }
        public string Name3 { get; set; }
        public string Name4 { get; set; }

        public Table1(string name1, string name2, string name3, string name4)
        {
            Name1 = name1;
            Name2 = name2;
            Name3 = name3;
            Name4 = name4;
        }
    }

Code:

    public class Repository
    {
         public static List<Table1> GetData(int rowCount)
         {
             var data = new List<Table1>();
             for (int i = 0; i < rowCount; i++)
             {
                 data.Add(new Table1(Guid.NewGuid().ToString(), Guid.NewGuid().ToString(), Guid.NewGuid().ToString(),
                                     Guid.NewGuid().ToString()));
             }

             return data;
         }
    }

Podam tutaj 3 znane mi sposoby, które można użyć w tym celu.

1. Zwykłe inserty:
Standardowy sposób dodawania rekordów, który sprawdza się w przypadku dodania pojedynczego rekordu  do bazy, jednak w przypadku ich dużej ilości powoduje zamulenie całego systemu (co później zobaczymy w wynikach):

Code:

        private static long InsertAsSingleRows(List<Table1> data, NpgsqlConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.Parameters.AddWithValue(Name1, "");
                cmd.Parameters.AddWithValue(Name2, "");
                cmd.Parameters.AddWithValue(Name3, "");
                cmd.Parameters.AddWithValue(Name4, "");
                cmd.CommandText = "INSERT INTO Table2(Name1,Name2,Name3,name4) VALUES(@Name1,@Name2,@Name3,@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
W przypadku dużej ilości dodawanych rekordów lepiej skorzystać z transakcji, którą na końcu zatwierdzamy:

Code:

        private static long InsertAsTransaction(List<Table1> data, NpgsqlConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                cmd.Transaction = connection.BeginTransaction();
                cmd.Parameters.AddWithValue(Name1, "");
                cmd.Parameters.AddWithValue(Name2, "");
                cmd.Parameters.AddWithValue(Name3, "");
                cmd.Parameters.AddWithValue(Name4, "");
                cmd.CommandText = "INSERT INTO Table2(Name1,Name2,Name3,name4) VALUES(@Name1,@Name2,@Name3,@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. Insert batchowy
Mechanizm ten służy przede wszystkim ładowaniu bardzo dużej ilości rekordów z plików. Jego wydajność jest nieporównywalnie większa niż przedstawionych dwóch poprzednich:

Code:

        private static long InsertAsBatch(List<Table1> data, NpgsqlConnection connection)
        {
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            using (var cmd = connection.CreateCommand())
            {
                var dataAsFile = new StringBuilder();
                for (int i = 0; i < data.Count; i++)
                {
                    dataAsFile.AppendFormat("{0}\t{1}\t{2}\t{3}\n", data[i].Name1, data[i].Name2, data[i].Name3,
                                            data[i].Name4);
                }
                var memoryStream = new MemoryStream(new UTF8Encoding().GetBytes(dataAsFile.ToString()));
                cmd.CommandText = "COPY Table2(Name1,Name2,Name3,name4) FROM STDIN";
                var npgsqlCopyIn = new NpgsqlCopyIn(cmd, connection, memoryStream);
                npgsqlCopyIn.Start();

                stopwatch.Stop();

                return stopwatch.ElapsedMilliseconds;
            }
        }

Czas na wyniki pomiarów czasu:

bardzo dobrze widać to na wykresie:

Podsumowując:
Jeżeli wstawiamy bardzo dużą ilość rekordów warto skorzystać z transakcji. Jeżeli dysponujemy ogromnymi zbiorami danych które chcemy umieścić w bazie możemy skorzystać z inserta batchowego.