Введение
Вставка большого количества записей в базу данных с помощью Entity Framework выполняется очень медленно. Причиной этому являются как архитектурные особенности самого фреймворка, так и неоптимальный генерируемый SQL. В данной статье я рассмотрю различные пути решения данной проблемы. Все описанное верно в первую очередь для Entity Framework 5-6 (POCO + Database First) и SQL Server 2008 и выше.
План статьи:
- INSERT стандартными средствами Entity Framework
- Поиск решения. SqlBulkCopy
- Интеграция Entity Framework и SqlBulkCopy
- Замеры производительности
- Выводы
- Что почитать
1. INSERT стандартными средствами Entity Framework
Стандартным способом добавления новых записей в БД является:
orders.ForEach(order => context.Orders.Add(order)); context.SaveChanges();
Каждый вызов метода Add в свою очередь приводит к дорогостоящему в плане выполнения вызову внутреннего алгоритма DetectChanges. Данный алгоритм сканирует все сущности в контексте и сравнивает текущее значение каждого свойства с исходным значением, хранимым в контексте, обновляет связи между сущностями и т.п. Известным способом поднятия производительности, актуальным до выхода EF 6, является отключение DetectChanges во время добавления сущностей в контекст:
context.Configuration.AutoDetectChangesEnabled = false; orders.ForEach(order => context.Orders.Add(order)); context.Configuration.AutoDetectChangesEnabled = true; context.SaveChanges();
Также рекомендуется не держать в контексте сотни тысяч объектов, а вставлять данные блоками, иногда впадая в крайности и создавая новый контекст для каждой добавляемой записи.
Наконец, в EF 6 появился оптимизированный метод AddRange, поднимающий производительность до уровня связки Add+AutoDetectChangesEnabled:
context.Orders.AddRange(orders); context.SaveChanges();
К сожалению, перечисленные подходы, не решают основной проблемы, а именно: при сохранении данных в БД, на каждую новую запись генерируется отдельный INSERT запрос! И это не шутка, добавили 10^6 сущностей в контекст - получили 10^6 INSERT-ов и соответствующее падение производительности.
2. Поиск решения. SqlBulkCopy
Испытывая стойкое отвращение к написанию "велосипедов", я в первую очередь поискал best-practices для множественной вставки с помощью EF. Казалось бы, типовая задача - но способов оптимизации генерируемого фреймворком SQL найти не удалось. В то же время, SQL Server предлагает ряд техник быстрой вставки данных, таких как утилита bcp и класс SqlBulkCopy. О последнем и пойдет речь ниже.
System.Data.SqlClient.SqlBulkCopy - класс из состава ADO.NET, разработанный и предназначенный для массовой вставки записей в таблицы SQL Server. В качестве источника данных может принимать DataRow[], DataTable, либо класс, реализующий IDataReader.
Что умеет:
- Отправлять данные на сервер поблочно с поддержкой транзакций;
- Выполнять маппинг колонок из DataTable на таблицу БД;
- Игнорировать constraints, foreign keys при вставке (опционально);
Минусы:
- Атомарность вставки (опционально),
- невозможность продолжения работы после возникновения исключения;
- Слабые возможности по обработке ошибок.
Подробнее можно почитать тут, а здесь мы рассмотрим нашу насущную проблему, а именно отсутствие интеграции SqlBulkCopy и EF. Кажется естественным желание получить NuGet пакет с открытым, протестированным и оптимизированным кодом, добавляющим метод-расширение вида:
context.MyEntities.BulkAdd(myEntities);
не правда ли? Как ни странно, устоявшегося подхода к решению такой задачи нет, но есть несколько проектов различной степени пригодности, таких как:
На поверку оказавшийся полностью нерабочим. При изучении Issues я наткнулся на дискуссию с участием... небезызвестной Julie Lerman, описывающую проблему, аналогичную моей и оставшуюся без ответа авторов проекта.
Живой проект, активное коммюнити. Ведро дегтя - нет поддержки Database First, но обещают добавить.
$300? Это шутка?
3. Интеграция Entity Framework и SqlBulkCopy.
Хорошо, напишем собственный велосипед. В простейшем случае, вставка данных из коллекции объектов с помощью SqlBulkCopy выглядит следующим образом:
//entities - коллекция сущностей EntityFramework using (IDataReader reader = entities.GetDataReader()) using (SqlConnection connection = new SqlConnection(connectionString)) using (SqlBulkCopy bcp = new SqlBulkCopy(connection)) { connection.Open(); bcp.DestinationTableName = "[Order]"; bcp.ColumnMappings.Add("Date", "Date"); bcp.ColumnMappings.Add("Number", "Number"); bcp.ColumnMappings.Add("Text", "Text"); bcp.WriteToServer(reader); }Сама по себе задача реализовать IDataReader на основе коллекции объектов тривиальна, поэтому я ограничусь ссылкой и перейду к описанию способов обработки ошибок при вставке с использованием SqlBulkCopy. По умолчанию, вставка данных производится в своей собственной транзакции. При возникновении исключения выбрасывается SqlException и происходит rollback, т.е. данные в БД не будут записаны вообще. А "родные" сообщения об ошибках данного класса иначе как неинформативными не назовешь. Например, что может содержать SqlException.AdditionalInformation:
The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.
или:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
А хотелось бы получать информацию, позволяющую однозначно определить строку/сущность, вызвавшие ошибку. К сожалению, SqlBulkCopy не предоставляет такой возможности. Что кроме этого можно сделать?
3.1. Проверка данных до вставки.
В первую очередь - очевидные вещи, такие как валидация данных средствами EF (например, реализация IValidatableObject), фильтрация дубликатов по ключу. В случае корректно сгенерированных сущностей и БД становятся неактуальными проверки на соответствие типов, или длину поля в таблице, как тут. Полезней разобраться с маппингом колонок, выполняемым через свойство SqlBulkCopy.ColumnMappings:
Если источник данных и таблица назначения имеют одинаковое количество столбцов и исходная позиция каждого исходного столбца в источнике данных соответствует исходной позиции соответствующего столбца назначения, коллекция ColumnMappings не требуется. Однако если количество столбцов или их порядок различны, необходимо использовать ColumnMappings для обеспечения правильного копирования данных между столбцами.
Для EF В 99% случаев потребуется задать ColumnMappings явно (из-за navigation properties и любых дополнительных свойств). Navigation properties можно отсеять при помощи Reflection, заодно автоматизировав заполнение коллекции ColumnMappings:
var columns = typeof(Order).GetProperties() .Where(property => property.PropertyType.IsValueType || property.PropertyType.Name.ToLower() == "string") .Select(property => property.Name) .ToList();
columns.ForEach(column => sqlBulkCopy.ColumnMappings.Add(column, column));
Такой код годится для POCO класса без дополнительных свойств, в противном случае придется переходить на "ручное управление".
Получить схему таблицы тоже достаточно просто:
private static List<string> GetColumns(SqlConnection connection) { string[] restrictions = { null, null, "<TableName>", null }; var columns = connection.GetSchema("Columns", restrictions) .AsEnumerable() .Select(s => s.Field<String>("Column_Name")) .ToList(); return columns; }Далее проводим сравнение двух коллекций, каждому замапленному свойству класса-источника должна соответствовать колонка таблицы БД.
3.2. Использование свойства SqlBulkCopy.BatchSize и класса SqlBulkCopyOptions
SqlBulkCopy.BatchSize:
 | BatchSize |
SqlBulkCopyOptions - перечисление:
| Имя члена | Описание | |
|---|---|---|
| CheckConstraints | ||
| Default | ||
| FireTriggers | ||
| KeepIdentity | ||
| KeepNulls | ||
| TableLock | ||
| UseInternalTransaction |
3.3. Временная таблица и инструкция MERGE
Как быть, если в источнике присутствуют дубликаты записей, уже находящихся в БД? Напомню, при попытке вставить дубликат, SqlBulkCopy выбросит исключение и завершит работу. Но решение есть! Вставляем данные во временную пустую таблицу, а затем синхронизируем таблицы с помощью инструкции MERGE, дебютировавшей в SQL Server 2008:
MERGE (Transact-SQL)
Выполняет операции вставки, обновления или удаления для целевой таблицы на основе результатов соединения с исходной таблицей. Например, можно синхронизировать две таблицы путем вставки, обновления или удаления строк в одной таблице на основании отличий, найденных в другой таблице.
Пример использования:
MERGE INTO Order AS Target USING OrderTemp AS Source ON Target.Id = Source.Id WHEN MATCHED THEN UPDATE SET Target.Date = Source.Date WHEN NOT MATCHED THEN INSERT (Date) VALUES (Source.Date);
Используя MERGE, становится легко и просто реализовать различную логику по обработке дубликатов: обновлять данные в целевой таблице, либо игнорировать или даже удалять совпадающие записи. Не забываем про performance tip:
The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.
Таким образом, вставка разбивается на 3 этапа:
1) cоздать/очистить временную таблицу, полностью идентичную целевой таблице;
2) вставить данные с помощью SqlBulkCopy во временную таблицу;
3) используя MERGE, добавить записи из временной таблицы в целевую.
1) cоздать/очистить временную таблицу, полностью идентичную целевой таблице;
2) вставить данные с помощью SqlBulkCopy во временную таблицу;
3) используя MERGE, добавить записи из временной таблицы в целевую.
4. Замеры производительности
Чтобы сравнить рассмотренные в статье подходы между собой, были использованы Entity Framework 6.1.1, SQL Server 2012 и подход Database First (дело вкуса). В моем тесте использовалась такая таблица:
Были проведены замеры времени выполнения для рассматриваемых в статье подходов к сохранению данных в БД, результаты представлены в таблице (время указано в секундах):
| Способ вставки записей в базу данных | Количество записей | ||
|---|---|---|---|
| 1000 | 10000 | 100000 | |
| Add | 7,3 | 101 | 6344 |
| Add + AutoDetectChangesEnabled=false | 6,5 | 64 | 801 |
| Add + отдельный контекст | 8,4 | 77 | 953 |
| AddRange | 7,2 | 64 | 711 |
| SqlBulkCopy | 0,01 | 0,07 | 0,42 |
Ого! Если использовать метод Add для добавления в контекст, сохранение 100000 записей в БД займет почти 2 часа! В то время как SqlBulkCopy на эту же задачу тратит менее секунды! Полагаю, дальнейшие комментарии излишни.
5. Выводы
Мы нашли решение проблемы медленной вставки в базу данных через Entity Framework. Это решение - отказ от инфраструктуры EF (добавление в контекст + сохранение контекста) и переход на использование класса SqlBulkCopy, входящего в состав ADO.NET.Хочу подчеркнуть, что я ни в коем случае не призываю использовать SqlBulkCopy повсеместно или заниматься преждевременной оптимизацией. Для решения многих реальных бизнес-задач - производительности стандартного EF метода AddRange более чем достаточно. Однако, любой разработчик, использующий Entity Framework, должен знать сильные и слабые стороны этого инструмента. Успехов!
entities.GetDataReader() - чет нет такого метода пишет
ОтветитьУдалить