MS SQL Server umożliwia szybkie wstawianie dużych ilości danych. Nazywa się ono kopią zbiorczą (ang. bulk copy) i jest wykonywane przez klasę SqlBulkCopy. Porównałem już, jak szybko działa ta klasa w porównaniu z EF Core 5 w tym poście: https://www.michalbialecki.com/2020/05/06/entity-framework-core-5-vs-sqlbulkcopy/, ale tym razem chcę sprawdzić coś innego – bibliotekę linq2db.

Czym jest Linq2db

Sprawdźmy, jak Linq2db jest opisane na swojej stronie internetowej:

LINQ to DB to najszybsza biblioteka dostępu do bazy danych LINQ oferująca prostą, lekką, szybką i bezpieczną dla typów warstwę między obiektami POCO a bazą danych.

LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCO objects and your database (wersja oryginalna)

Brzmi imponująco i niedawno odkryłem, że istnieje pakiet rozszerzeń EF Core linq2db.EntityFrameworkCore, który jest zintegrowany z EF Core i wzbogaca DbContext o kilka fajnych funkcji.

Najważniejsze możliwości linq2db to: 

• Bulk copy (bulk insert)
• szybkie wczesne ładowanie (nieporównywalnie szybsze niż wbudowane polecenieInclude)
• wsparcie dla polecenia MERGE 
• wsparcie dla tabel tymczasowych
• rozszerzenia do wyszukiwania w tekście (Full-Text search)
• oraz jeszcze kilka

Napiszmy kod

W projekcie PrimeHotel zaimplementowałem już metodę z SqlBulkCopy do wstawiania profili do bazy danych. Najpierw generuję profile za pomocą biblioteki Bogus, a następnie wstawiam je. W tym przykładzie użyję 3 metod z ProfileController:

• GenerateAndInsert – zaiplementowana przy pomocy EF Core
• GenerateAndInsertWithSqlCopy – zaimplementowana z użyciem klasy SqlBulkCopy
• GenerateAndInsertWithLinq2db – zaimplementowana z użyciem Linq2db

Pokażę szybko, jak wyglądają wspomniane metody. Pierwszą z nich jest GenerateAndInsert, zaimplementowana przy pomocy czystego Entity Framework Core 5.

[HttpPost("GenerateAndInsert")]
public async Task<IActionResult> GenerateAndInsert([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    primeDbContext.Profiles.AddRange(profiles);
    var insertedCount = await primeDbContext.SaveChangesAsync();

    return Ok(new {
            inserted = insertedCount,
            generationTime = gererationTime,
            insertTime = s.Elapsed.ToString()
        });
}

Używam klasy Stopwatch, aby zmierzyć, ile czasu zajmuje wygenerowanie profili metodą GenerateProfiles i ile czasu zajmuje ich wstawienie.

GenerateAndInsertWithSqlCopy jest zaimplementowana przy pomocy klasy SqlBulkCopy:

[HttpPost("GenerateAndInsertWithSqlCopy")]
public async Task<IActionResult> GenerateAndInsertWithSqlCopy([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    var dt = new DataTable();
    dt.Columns.Add("Id");
    dt.Columns.Add("Ref");
    dt.Columns.Add("Forename");
    dt.Columns.Add("Surname");
    dt.Columns.Add("Email");
    dt.Columns.Add("TelNo");
    dt.Columns.Add("DateOfBirth");

    foreach (var profile in profiles)
    {
        dt.Rows.Add(string.Empty, profile.Ref, profile.Forename, profile.Surname, profile.Email, profile.TelNo, profile.DateOfBirth);
    }

    using var sqlBulk = new SqlBulkCopy(connectionString);
    sqlBulk.DestinationTableName = "Profiles";
    await sqlBulk.WriteToServerAsync(dt);

    return Ok(new
    {
        inserted = dt.Rows.Count,
        generationTime = gererationTime,
        insertTime = s.Elapsed.ToString()
    });
}

Zauważ, że ta implementacja jest znacznie dłuższa i musiałem utworzyć obiekt DataTable, aby przekazać moje dane w postaci tabeli.

I wreszcie implementacja GenerateAndInsertWithLinq2db, która wykorzystuje bibliotekę linq2db.

[HttpPost("GenerateAndInsertWithLinq2db")]
public async Task<IActionResult> GenerateAndInsertWithLinq2db([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    using (var db = primeDbContext.CreateLinqToDbConnection())
    {
        await db.BulkCopyAsync(new BulkCopyOptions { TableName = "Profiles" }, profiles);
    }

    return Ok(new
    {
        inserted = profiles.Count(),
        generationTime = gererationTime,
        insertTime = s.Elapsed.ToString()
    });
}

Ta metoda jest prawie tak krótka jak implementacja z użyciem EF Core, ale tworzy obiekt DataConnection z metodą CreateLinqToDbConnection.

Wyniki

Porównałem te 3 metody z wstawianiem 1k, 10k, 50k, 100k i 500k rekordów. Jak myśliś, jak szybko udało się to osiągnąć? Sprawdźmy, poniższe dane są wyrażone w sekundach.

	EF Core	Bulk insert	linq2db bulk insert
1000	0.22	0.035	0.048
10000	1.96	0.2	0.318
50000	9.63	0.985	1.54
100000	19.35	1.79	3.18
500000	104	9.47	16.56

Oto tabela z wynikami, w sekundach. Sam EF Core nie jest wcale imponujący, ale w połączeniu z biblioteką Linq2db jest prawie tak szybki, jak klasa SqlBulkCopy.

A oto wykres, im niższa wartość, tym lepiej.

Zabawne – podczas testów zauważyłem, że generowanie danych testowych jest faktycznie wolniejsze niż wstawianie do bazy danych, wow 😀

Podsumowanie

Linq2db to imponująca biblioteka, która oferuje już całkiem sporo. Z tego co można zobaczyć na GitHub wygląda na to, że jest to dobrze ugruntowany projekt z wieloma współtwórcami. Wiedząc to, jestem zdziwiony, że wcześniej na niego nie trafiłem.

Wstawianie zbiorcze z linq2db jest prawie tak szybkie, jak użycie klasy SqlBulkCopy, ale jest znacznie czystsze i krótsze. Jest też mniej podatne na błędy i na pewno użyłbym go w swoich projektach.

Cały zamieszczony tutaj kod jest dostępny na moim GitHub.

Mam nadzieję, że ci się przyda, pozdro! 😄