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