Оптимизация Entity Framework: Update

Обновление записей штатными средствами Entity Framework приводит к генерации UPDATE команды на каждую запись (как и в случае со вставкой записей в БД). В случае единовременного обновления тысяч записей, стандартный EF подход приводит к ощутимому падению производительности. Все нижеописанное верно в первую очередь для Entity Framework 5-6 (POCO + Database First) и SQL Server 2008 и выше. Изучим проблему подробнее.

 Следующий код:

var orders = context.Orders.ToList();
//.. записали новые данные
context.SaveChanges();

приведет к выполнению отдельного SQL-запроса на КАЖДЫЙ измененный объект:

UPDATE [dbo].[Order]
SET [Text] = @0
WHERE ([Id] = @1)

В простейших случаях, может помочь EntityFramework.Extended:

context.Tasks.Update(
 t => t.StatusId == 1,
 t2 => new Task { StatusId = 2 });

Более подробно о скорости EF и работе с этой библиотекой тут. Очевидно, что решение не универсальное и годится только для записи во все целевые строки одного и того же значения. К счастью, с помощью класса SqlBulkCopy и SQL-инструкции MERGE, подробно рассмотренных в предыдущей статье, мы можем считать данные из коллекции объектов EF в БД по следующему алгоритму:

1. cоздать/очистить временную таблицу;
2. вставить данные с помощью SqlBulkCopy во временную таблицу;
3. используя MERGE, добавить записи из временной таблицы в целевую.

Пройдемся по пунктам:

1. Временная таблица

Необходимо создать таблицу в БД, полностью повторяющую схему таблицы для вставки данных. Создавать копии вручную - худший вариант из возможных, так как вся дальнейшая работа по сравнению и синхронизации схем таблиц также ляжет на ваши плечи. Проще копировать схему программно и непосредственно перед вставкой. Например, с использованием SQL Server Management Objects (SMO):

Server server = new Server();
//SQL auth
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = "login";
server.ConnectionContext.Password = "password";
server.ConnectionContext.ServerInstance = "server";
 
Database database = server.Databases["database name"];
 
Table table = database.Tables["Order"];
 
ScriptingOptions options = new ScriptingOptions();
options.Default = true;
options.DriAll = true;
 
StringCollection script = table.Script(options);

Стоит обратить внимание на класс ScriptingOptions, содержащий несколько десятков параметров для тонкой настройки генерируемого SQL. Полученный StringCollection развернем в String. К сожалению, лучшего решения, чем заменить в скрипте имя исходной таблицы на имя временной а-ля String.Replace("Order", "Order_TEMP"), я не нашел. Буду благодарен за подсказку красивого решения по созданию копии таблицы в пределах одной БД. Выполним готовый скрипт:

database.ExecuteNonQuery(tempScript);

Следует отметить, что вызов Database.ExecuteNonQuery в .NET 4+, выбрасывает исключение вида:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

cвязанное с тем, что замечательная библиотека SMO есть только под .NET 2 Runtime. К счастью, есть workaround:

 <startup useLegacyV2RuntimeActivationPolicy="true">
  ...
 </startup>

Другой вариант - на свой страх и риск просто использовать Database.ExecuteWithResults (у меня отработал без ошибок). Копия таблицы создана!

2. Запись во временную таблицу

Запись в БД с использованием коллекции объектов EF, как источника была подробно освещена в предыдущей статье, поэтому останавливаться на этом не будем, ограничившись примером кода:

using (IDataReader reader = entities.GetDataReader())
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlBulkCopy bcp = new SqlBulkCopy(connection))
{
 connection.Open();
 
 bcp.DestinationTableName = "Order_TEMP";
        // Маппинг колонок таблицы 
 bcp.ColumnMappings.Add(column, column);
 
 bcp.WriteToServer(reader);
}

3. Копирование данных из временной таблицы в целевую

Осталось выполнить на стороне SQL Server инструкцию MERGE, сравнивающую содержимое временной и целевой таблиц и выполняющую апдейт или вставку (если необходимо). К примеру, для уже известной нам таблицы Order код может выглядеть следующим образом:

MERGE INTO [Order] AS [Target]
USING [Order_TEMP] AS [Source]
 ON Target.Id = Source.Id
WHEN MATCHED THEN
 UPDATE SET 
 Target.Date = Source.Date, 
 Target.Number = Source.Number,
 Target.Text = Source.Text,
 Target.CustomerId = Source.CustomerId
WHEN NOT MATCHED THEN
INSERT 
       (Date, Number, Text, CustomerId) 
VALUES 
       (Source.Date, Source.Number, Source.Text, Source.CustomerId);

Выполним запрос с помощью уже известного Database.ExecuteNonQuery/Database.ExecuteWithResults, либо средствами EF через context.Database.ExecuteSqlCommand. Готово! Не забываем очистить/удалить временную таблицу по вкусу.

Замеры производительности

Результаты измерений времени выполнения представлены в таблице (время указано в секундах):

Способ фиксации изменений в базе данных	Количество записей
	1000	10000	100000
SaveChanges	6,2	60	590
SqlBulkCopy + MERGE	0,04	0,2	1,5

SqlBulkСopy снова на высоте.

Выводы

В случае работы с контекстом, содержащим большое количество объектов (10^3 и выше), отказ от штатных средств Entity Framework и переход на SqlBulkCopy для записи в БД может обеспечить прирост производительности в десятки, а то и сотни раз.