Insert performance

Добрый вечер, господа

Вот уже 10 дней, как я пытаюсь починить производительность, и у меня ничего не выходит. Может быть, вы сможете мне помочь?

Исходные данные:

Вставляем около 100к записей в таблицы (три) каждая по 100 млн записей. Инсерты распределены равномерно.
Индексов 5 штук. Кластеризованный один - по дате

Инсерт занимает около часа. Это плохо, и не подходит.

Работаем с базой через Hibernate


Проверяли, инсерт в таблицу без индексов пробегает мгновенно (удивительно, да?)... Подозрение пало на индексы
Проверяли, инсерт во временную пустую таблицу с последующей вставкой в целевую пробегает за 2 минуты.... Но индексы-то остались! Подозрение пало на батчи в Хибернейте
Проверили, батчи включены...

также:

при размере батча, равном количеству инсертов после старта сервера (он при старте делает селект целевым таблицам) инсерт тестовых данных (2000 записей в таблицу 10млн) пробегает мгновенно
но после

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

инсерт занимает 150 секунд

При размере батча, равному 2, после старта сервера, инсерт тестовых данных занимает 60 секунд
после очистки кеша снова 150 секунд

Обьясните мне, как кеш связан с батчами?

twister_mc
также: при размере батча, равном количеству инсертов после старта сервера (

простите
надо сказать иначе, чтоб было понятно:

"после старта сервера: при размере батча, равном количеству инсертов, "

"батчи в Хибернейте" - это какая команда в профайлере?

Огласите результат select @@version

twister_mc
Проверяли, инсерт в таблицу без индексов пробегает мгновенно (удивительно, да?)...

Что вы нашли в этом удивительного? По вашему обновление индексов не требует ресурсов?

twister_mc
Проверяли, инсерт во временную пустую таблицу с последующей вставкой в целевую пробегает за 2 минуты.... Но индексы-то остались! Подозрение пало на батчи в Хибернейте

Не знаю что есть "батчи в Хибернейте", но если средствами сервера достигается приемлемая производительность, то виноват клиент.

twister_mc
Обьясните мне, как кеш связан с батчами?

Кеш связан не с батчами, а со страницами данных и индексов. Очистив кеш, вы заставили сервер заново поднимать их с диска.

Да, я понимаю, что обновление индексов вещь не мгновенная...

батчи в хибернейте - по сути эквивалент N операций инсерт в рамках одной ms sql транзакции
(implicit transactions off и commit)

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

вопрос: параллелит ли скул сервер запросы при implicit transactions off после commit? Если нет, то можно ли разумным способом параллелить простые инсерты?

версия сервера у нас 2008r2 точнее если нужно скажу завтра

и еще вопрос: может ли повлиять на результат инсерта предварительная выборка из таблицы ... какое нибудь кеширование....

Параллелит ли сервер вставку одной записи?

twister_mc
было замечено, что запись из временной таблицы намного быстрее. почитал, нашел что это изза того что скул сервер параллелит инсерты из селектов

Дело не в распаралеливании, а в том, что серверу нужно выполнить большое количество операций. Если там индексов нет, то скорость ещё хоть какая то (как вы делаете со временной таблицей), но если ещё 4 индекса, то для каждой вставки вы ещё дополнительно делаете поиск и вставку в 4 индекса.

Вариантов несколько:

1. делать через временную таблицу.

2. делать групповую вставку вида:

insert ...
values (...), (...)

заменяя "батчи" на такие операции.

3. Использовать bulk load (класс SqlBulkCopy)

alexeyvg
Вариантов несколько:

4. Заполнять DataTable, из которой передавать данные на сервер (либо напрямую в таблицу, либо передавая её как параметр в процедуру)

twister_mc
после CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO инсерт занимает 150 секунд

Вы "dbcc dropcleanbuffers" с какой целью делаете? Просто для тестирования, или это периодически происходит в штатном режиме?