Удаляем данные из базы быстро и решительно при помощи Entity Framework

Далеко не всегда нужно удалять данные из базы, но всё же хочется как можно сильней снизить затраты. В новой версии Entity Framework для этого были добавлены интересные методы – ExecuteDelete и ExecuteDeleteAsync. Давайте же рассмотрим как они помогут нам ускорить работу над удалениям записей?

Добавленные методы повышают производительность несколькими способами. Вместо того, чтобы сначала извлекать объекты и сохранять все объекты в памяти, прежде чем мы сможем выполнить с ними действие и уже потом передавать их в SQL, теперь мы можем сделать это с помощью всего одной операции, результатом которой является одна команда SQL.

Давайте посмотрим, как это выглядит в коде.

Настройка базы

Прежде чем мы углубимся в примеры, давайте сначала настроим нашу базу данных SQL и заполним 2 таблицы:

• Companies – тут хранятся данные компаний
• Users – тут хранятся данные пользователей

Две таблицы нам нужны чтобы посмотреть на поведение связных таблиц. Соответственно реализуем связь 1 ко многим, где у одной компании может быть множество пользователей.

Удаляем данные

Давайте посмотрим на другой пример и удалим некоторых пользователей, которые содержат ссылки на адреса и домашних животных. Удаляя пользователя, мы также удаляем адрес и домашних животных, потому что оператор delete каскадирует во внешние таблицы.

private static void DeleteWithLoadingTest()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);
    List<Company> companies = context.Companies.ToList();

    foreach (IQueryable<User> usersToDelete in companies
                 .Select(company => context.Users.Where(x => x.CompanyId == company.Id)))
    {
        context.Users.RemoveRange(usersToDelete);
    }

    context.SaveChanges();
}

Для того чтобы убедиться, что данные удалены из обеих таблиц напишем простенький метод.

private static bool CheckDbIsCleared()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);

    bool userAny = context.Users.Any();
    bool companiesAny = context.Users.Any();

    return !userAny && !companiesAny;
}

Это нам позволит быть уверенными, что данные были удалены в обеих таблицах. Если же в каком-то из случаев одна из таблиц останется неизменённой, то мы сможем понять что и замер учитывать не стоит.

Ускоряем удаление данных при помощи сторонних пакетов

Строго говоря, можно и до версии 7.0 ускорить удаление. Для этого нам потребуется подключить пакет Z.EntityFramework.Extensions.EFCore и применить метод DeleteFromQuery.

private static void DeleteWithoutLoadingZExtensionTest()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);
    List<Company> companies = context.Companies.ToList();

    foreach (Company company in companies)
    {
        context.Users.Where(x => x.CompanyId == company.Id)
            .DeleteFromQuery();
    }

    context.SaveChanges();
}

Здесь мы можем увидеть что нам уже не нужно получать запись из таблицы User, а просто вызвать DeleteFromQuery для нужной нам записи. Код выглядит чище, данные удалены, а как это повлияет на скорость выполнения мы увидим чуть позже.

Ускоряем удаление данных средствами EF

Но что, если мы не хотим подтягивать лишнюю зависимость, зато можем использовать уже новую (на данный момент) версию EF Core? Мы можем это сделать и отличия будут минимальны – всего лишь вызываем ExecuteDelete вместо DeleteFromQuery. Данные будут удалены и мы достигнем желаемого результата без каких-то сложных приёмов.

private static void DeleteWithoutLoadingNativeEfCoreTest()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);
    List<Company> companies = context.Companies.ToList();

    foreach (Company company in companies)
    {
        context.Users.Where(x => x.CompanyId == company.Id)
            .ExecuteDelete();
    }

    context.SaveChanges();
}

Круто! Минус один пакет, а результат всё равно есть. Осталось только узнать будут ли отличия в скорости работы.

Сравнение результатов

Теперь давайте запустим написанный код и посмотрим на результаты. Разумеется, они будут зависеть и от железа, но общее представление мы получить всё равно сможем.

Число строк	Традиционное удаление	Удаление с Extensions	Удаление с ExecuteDelete
50000	4.4483 сек.	0.2204 сек.	0.0873 сек.
100000	10.8219 сек.	0.2908 сек.	0.2179 сек.
150000	20.8454 сек.	0.6919 сек.	0.2575 сек.
200000	31.6396 сек.	0.6331 сек.	0.3087 сек.
250000	45.2789 сек.	0.5799 сек.	0.3285 сек.

Впечатляет, не так ли? Безусловно, измерения можно было провести и лучше и точнее, но с учётом того, что разница почти на два порядка, то я решил не запариваться. Магия же, которая позволяет получить такой результат, заключается в том, что нам больше не нужно грузить сущность в память. Просто, эффективно и красиво. Однако, остаётся два интересных вопроса. Во-первых, что же у нас под капотом?

При обычном удалении в логе запросов мы можем увидеть серию следующих запросов:

DELETE FROM "Users" WHERE "Id" = $1

Параметр здесь, как не трудно догадаться, это Id записи.

Похожий запрос мы получим и при использование EF 7.0:

DELETE FROM "Users" AS u	WHERE u."CompanyId" = $1

А вот Z.EntityFramework.Extensions.EFCore несколько отличается и результат несколько более громоздкий. Скорее всего, именно по этому результаты у данного пакета несколько хуже чем у EF Core 7.0.

DELETE FROM "Users" AS A
	USING ( SELECT u."Id"
	FROM "Users" AS u
	WHERE u."CompanyId" = $1 ) AS B WHERE A."Id" = B."Id"

Во-вторых: а что будет, если мы захотим удалить все данные из таблиц за раз? Тут ничего совсем уж неожиданного не будет, но сравнить всё равно интересно. Для Z.EntityFramework.Extensions.EFCore напишем следующий код:

private static void DeleteAllZExtensionTest()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);
    context.Users.DeleteFromQuery();
    context.Companies.DeleteFromQuery();

    context.SaveChanges();
}

Запрос выполнился примерно за 0.336 секунды и выглядеть будет так (точно такой же код будет и для таблицы Companies с разницей в названии таблицы):

DELETE FROM "Users" AS A
	USING ( SELECT u."Id"
	FROM "Users" AS u ) AS B WHERE A."Id" = B."Id"

Для EF Core 7.0 результат несколько отличается, хотя код остаётся почти таким же:

private static void DeleteAllNativeTest()
{
    DbContextOptionsBuilder<TestContext> optionsBuilder = new DbContextOptionsBuilder<TestContext>();
    optionsBuilder.UseNpgsql(Config.GetConnectionString("TestDb") ?? string.Empty);

    using TestContext context = new TestContext(optionsBuilder.Options);
    context.Users.ExecuteDelete();
    context.Companies.ExecuteDelete();

    context.SaveChanges();
}

И посмотрим код для таблицы Users:

DELETE FROM "Users" AS u

Неудивительно, что код будет работать несколько быстрее – 0.1755 секунды.

Выводы

Использование методов, которые работают без подгрузки сущностей может значительно увеличить производительность. При этом, я бы выбрал именно реализацию из нового пакета EF Core, впрочем для ситуаций где необходимо использовать более старую версию фреймворка Z.EntityFramework.Extensions.EFCore тоже может помочь.