Почему batch insert ускоряет запись данных в базу данных?
Что проверяют
Понимаете ли вы, из чего складывается стоимость одной вставки, и знаете ли инструменты платформы. Ответ уровня «меньше запросов, значит быстрее» слишком общий, ждут разбор по составляющим и границы, за которой пачка начинает вредить.
Как отвечать
Время вставки почти не тратится на саму запись строки. Тратится на:
- круг по сети: тысяча строк это тысяча запросов и тысяча ожиданий ответа, и на удалённой базе задержка сети становится главной статьёй;
- разбор и планирование команды, а также переключение контекста на сервере при каждом вызове;
- транзакцию: в режиме автоматической фиксации каждая вставка это отдельная транзакция со своим сбросом журнала на диск. Одна транзакция на пачку превращает тысячу сбросов в один.
Инструменты в C#: SqlBulkCopy для больших объёмов, он идёт отдельным путём с минимальным журналированием; табличный параметр на основе пользовательского табличного типа, когда нужна серверная логика в одной процедуре; обычное пакетирование команд, когда строк немного. EF Core склеивает изменения SaveChanges в несколько команд сам, но AddRange вместо Add в цикле и отключение автоматического отслеживания изменений на больших объёмах дают заметный выигрыш. Стоит знать, что Dapper с коллекцией параметров отправляет по команде на элемент, это не пакет.
Где спотыкаются
Делают пачку бесконечной. Одна транзакция на миллион строк это разросшийся журнал, эскалация блокировок до уровня таблицы и полный откат при единственной ошибке в конце. Разумно резать на куски по несколько сотен или тысяч строк и фиксировать по ходу.
Второе: упираются в предел параметров. SQL Server не принимает больше 2100 параметров в одной команде, поэтому попытка вставить десять тысяч строк одним запросом падает, а размер пачки надо считать от числа колонок, а не брать круглым числом.
Третье: считают, что одна большая транзакция снимает проблему блокировок. Ровно наоборот: чем дольше транзакция, тем дольше удерживаются блокировки и тем хуже читателям. И на таблице с десятком индексов узким местом становятся уже не круги по сети, а поддержка индексов при каждой вставке.