Entity Framework Core 5 to świetny ORM do używania i łączenia się z bazą danych. Jest łatwy w użyciu i łatwy do zrozumienia. Oferuje wszystko co potrzebne aby poradzić sobie z większością wyzwań programistycznych. A co z wstawieniem dużej ilości danych za jednym razem? Czy byłoby to wystarczająco szybkie?

Zerknijmy na kod

Jako przykładu użyję prostej encji – Profile oraz repozytorium PrimeHotel dostępne na moim koncie na GitHub. 

Mój DbContext jest bardzo prosty i wygląda tak:

    public class PrimeDbContext : DbContext
    {
        public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Room> Rooms { get; set; }

        public virtual DbSet<Profile> Profiles { get; set; }

        public virtual DbSet<Reservation> Reservations { get; set; }
    }

A encja Profile prezentuje się następująco:

    public class Profile
    {
        public int Id { get; set; }

        public string Ref { get; set; }

        public string Forename { get; set; }

        public string Surname { get; set; }

        public string TelNo { get; set; }

        public string Email { get; set; }

        public DateTime? DateOfBirth { get; set; }
    }

W tym przykładzie użyję aplikacji typu WebApi, aby w jak najprosztszy sposób wywołać kod. Aby to zrobić stworzyłem ProfileController.

    [ApiController]
    [Route("[controller]")]
    public class ProfileController : ControllerBase
    {
        private readonly PrimeDbContext primeDbContext;
        private readonly string connectionString;

        public ProfileController(PrimeDbContext _primeDbContext, IConfiguration _configuration)
        {
            connectionString = _configuration.GetConnectionString("HotelDB");
            primeDbContext = _primeDbContext;
        }
    }

Na razie jest dość pusty, ale będzie to dobra baza, od której możemy zacząć.

Stwórzmy zatem profile – dużo! 

Aby przetestować dodawanie wielu encji naraz, musimy wygenerować wiele danych testowych. Lubię mieć moje dane testowe zbliżone do prawdziwych jak to możliwe, więc aby je uzyskać, użyję pakietu NuGet Bogus.

Bogus to rozbudowany i bardzo łatwy w użyciu generator fałszywych danych. Wygeneruje losowe wartości, które pasują do danego kontekstu, takie jak nazwisko, wiek, adres, adres e-mail, nazwa firmy i tak dalej. Istnieją dziesiątki opcji. Idź i przekonaj się sam w dokumentacji. 

Generowanie dowolnej liczby profili, będzie wyglądało następująco:

    private IEnumerable<Profile> GenerateProfiles(int count)
    {
        var profileGenerator = new Faker<Profile>()
            .RuleFor(p => p.Ref, v => v.Person.UserName)
            .RuleFor(p => p.Forename, v => v.Person.FirstName)
            .RuleFor(p => p.Surname, v => v.Person.LastName)
            .RuleFor(p => p.Email, v => v.Person.Email)
            .RuleFor(p => p.TelNo, v => v.Person.Phone)
            .RuleFor(p => p.DateOfBirth, v => v.Person.DateOfBirth);

        return profileGenerator.Generate(count);
    }

Dodawanie profili z Entity Framework Core 5

Nie chcę wysyłać wszystkich tych profili w żądaniu, ponieważ byłaby to ogromna ilość danych. Przeniesienie tego do kontrolera i deserializacja po stronie ASP.NET Core 5 może trochę potrwać i nie jest to tak naprawdę część, którą chcę przetestować. Właśnie dlatego zdecydowałem się wygenerować moje profile w kontrolerze i wstawić je zaraz potem.

Kod całego rozwiązania jest naprawdę prosty:

    [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()
            });
    }

Dodatkowo dodałem klasę Stopwatch, aby zmierzyć, jak długo trwa generowanie profili, a także ich wstawianie. W końcu zwracam anonimowy typ, aby łatwo zwrócić więcej niż jeden wynik na raz.

Na koniec przetestujmy to. Wstawiając 1000 profili dostałem odpowiedź:

Nieźle, ale spróbujmy czegoś większego, np. 100000 profili:

 

Aż 25 sekund? Serio? Nie zwala z nóg.

Co w takim razie dzieje się pod spodem? Sprawdźmy przy użyciu SQL Server Profiler:

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Profiles] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, 0),
(@p7, @p8, @p9, @p10, @p11, @p12, @p13, 1),
(@p14, @p15, @p16, @p17, @p18, @p19, @p20, 2),
(@p21, @p22, @p23, @p24, @p25, @p26, @p27, 3),
...
) AS i ([DateOfBirth], [Email], [Forename], [Ref], [ReservationId], [Surname], [TelNo], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([DateOfBirth], [Email], [Forename], [Ref], [ReservationId], [Surname], [TelNo])
VALUES (i.[DateOfBirth], i.[Email], i.[Forename], i.[Ref], i.[ReservationId], i.[Surname], i.[TelNo])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [Profiles] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];

',N'@p0 datetime2(7),
@p1 nvarchar(4000),
@p2 nvarchar(4000),
@p3 nvarchar(4000),
@p4 int,
@p5 nvarchar(4000),
...
@p0='1995-02-22 09:40:44.0952799',
@p1=N'Sherri_Orn@gmail.com',
@p2=N'Sherri',
...

SqlBulkCopy na ratunek

SqlBulkCopy to klasa, która została wprowadzona jakiś czas temu, a dokładnie w .Net Framework 2.0 – 18 lat temu! SqlBulkCopy będzie działać tylko w celu zapisywania danych w bazie danych SQL Server, ale jego źródłem może być wszystko, o ile wyniki mogą być ładowane do DataTable lub odczytywane przez IDataReader.

A jak zastosować SqlBulkCopy do dodawania profili? Zerknijmy na kod.

    [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()
        });
    }

Najpierw musimy zdefiniować DataTable. Musi ona reprezentować tabelę profili, ponieważ jest to nasza tabela docelowa, której będziemy używać.

Następnie za pomocą metody WriteToServerAsync ładujemy profile do bazy danych. A co się dzieje od strony bazy danych? Zerknijmy na wyniki w SQL Server Profiler.

select @@trancount; 
SET FMTONLY ON select * from [Profiles] 
SET FMTONLY OFF exec ..sp_tablecollations_100 N'.[Profiles]'

insert bulk [Profiles] (
   [Ref] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, 
   [Forename] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, 
   [Surname] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, 
   [TelNo] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, 
   [Email] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, 
   [DateOfBirth] DateTime2(7))

Wygenerowanych SQL jest minimalny. Co w takim razie dzieje się pod spodem? Na StackOverflow znalazłem następującą odpowiedź:

SqlBulkCopy does not create a data file. It streams the data table directly from the .Net DataTable object to the server using the available communication protocol (Named Pipes, TCP/IP, etc…) and insert the data to the destination table in bulk using the same technique used by BCP.

 W skrócie: SqlBulkCopy streamuje dane do servera i wpisuje je do wskazanej tabeli. Dwa zupełnie inne podejścia. A jak w tym przypadku wygląda wydajność? Porównajmy oba podejścia.

Wydajność

Wynik porównania może być dużym zaskoczeniem. SqlBulkCopy jest stworzone do szybkiego wstawiania danych do SQL Servera i jest przy tym niewiarygodnie wydajne. 

Duże różnice zaczynają się pojawiać, gdy wstawia się jednocześnie ponad 10 tysięcy encji. Powyżej tej liczby może być konieczne ponowne zaimplementowanie kodu w celu użycia SqlBulkCopy zamiast Entity Framework Core 5.

A co z innymi operacjami?

Jeśli chodzi o obsługę dużych ilości danych, sprawy zaczynają być nieco trudniejsze. Warto zapoznać się z ulepszeniami po stronie bazy danych i danymi, na których faktycznie trzeba operować. Pamiętaj, że operacje na dużych porcjach danych są znacznie szybsze, gdy są wykonywane po stronie bazy danych.

Raz implementowałem zadanie, w którym musiałem czytać i aktualizować około miliona encji, raz dziennie. Aktualizacja po stronie .Net nie była wystarcająco wydajna, więc cały proces przeniosłem do bazy danych. Połączyłem kilka rzeczy i wyszło całkiem nieźle.

• stworzenie tabeli tymczasowej, i.e. T1
• przesłanie danych za pomocą SqlBulkCopy
• wykonanie aktualizacji po stronie bazy danych
• pobranie danych zwrotnych, których możesz potrzebować, np. do logowania
• usunięcie tabeli tymczasowej

Zdaję sobie sprawę, że przenoszenie logiki biznesowej do bazy danych to anty wzorzec, ale jeśli ta operacja aktualizacji musi być szybka, musimy zdecydować się na pewne ustępstwa.

Cały cytowany kod znajdziedzi na moim GitHub.

Do zobaczenia!