Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
Добрый вечер, господа

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

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

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

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

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


Проверяли, инсерт в таблицу без индексов пробегает мгновенно (удивительно, да?)... Подозрение пало на индексы
Проверяли, инсерт во временную пустую таблицу с последующей вставкой в целевую пробегает за 2 минуты.... Но индексы-то остались! Подозрение пало на батчи в Хибернейте
Проверили, батчи включены...
22 янв 13, 23:00    [13812440]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
также:

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

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

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

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

Обьясните мне, как кеш связан с батчами?
22 янв 13, 23:07    [13812455]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
twister_mc
также:

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


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

"после старта сервера: при размере батча, равном количеству инсертов, "
22 янв 13, 23:08    [13812460]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
"батчи в Хибернейте" - это какая команда в профайлере?
22 янв 13, 23:32    [13812509]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
invm
Member

Откуда: Москва
Сообщений: 9397
Огласите результат select @@version
twister_mc
Проверяли, инсерт в таблицу без индексов пробегает мгновенно (удивительно, да?)...
Что вы нашли в этом удивительного? По вашему обновление индексов не требует ресурсов?
twister_mc
Проверяли, инсерт во временную пустую таблицу с последующей вставкой в целевую пробегает за 2 минуты.... Но индексы-то остались! Подозрение пало на батчи в Хибернейте
Не знаю что есть "батчи в Хибернейте", но если средствами сервера достигается приемлемая производительность, то виноват клиент.
twister_mc
Обьясните мне, как кеш связан с батчами?
Кеш связан не с батчами, а со страницами данных и индексов. Очистив кеш, вы заставили сервер заново поднимать их с диска.
22 янв 13, 23:49    [13812571]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
Да, я понимаю, что обновление индексов вещь не мгновенная...

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

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

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

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

и еще вопрос: может ли повлиять на результат инсерта предварительная выборка из таблицы ... какое нибудь кеширование....
23 янв 13, 00:28    [13812660]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Параллелит ли сервер вставку одной записи?
23 янв 13, 01:40    [13812779]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
twister_mc
было замечено, что запись из временной таблицы намного быстрее. почитал, нашел что это изза того что скул сервер параллелит инсерты из селектов
Дело не в распаралеливании, а в том, что серверу нужно выполнить большое количество операций. Если там индексов нет, то скорость ещё хоть какая то (как вы делаете со временной таблицей), но если ещё 4 индекса, то для каждой вставки вы ещё дополнительно делаете поиск и вставку в 4 индекса.

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

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

2. делать групповую вставку вида:
insert ...
values (...), (...)
заменяя "батчи" на такие операции.

3. Использовать bulk load (класс SqlBulkCopy)
23 янв 13, 08:59    [13813204]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
alexeyvg
Вариантов несколько:
4. Заполнять DataTable, из которой передавать данные на сервер (либо напрямую в таблицу, либо передавая её как параметр в процедуру)
23 янв 13, 09:00    [13813210]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Гость333
Member

Откуда:
Сообщений: 3683
twister_mc
после

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

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

Вы "dbcc dropcleanbuffers" с какой целью делаете? Просто для тестирования, или это периодически происходит в штатном режиме?
23 янв 13, 10:58    [13813840]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Может быть, вы сможете мне помочь?


Не делать массовую заливку через Хибернейте.
23 янв 13, 11:22    [13814002]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
alexeyvg
twister_mc
было замечено, что запись из временной таблицы намного быстрее. почитал, нашел что это изза того что скул сервер параллелит инсерты из селектов
Дело не в распаралеливании, а в том, что серверу нужно выполнить большое количество операций. Если там индексов нет, то скорость ещё хоть какая то (как вы делаете со временной таблицей), но если ещё 4 индекса, то для каждой вставки вы ещё дополнительно делаете поиск и вставку в 4 индекса.


вопрос: почему вставка из временной таблицы такая быстрая? я так понимаю, потому, что скул сервер удобно умеет параллелить insert ... select

Гавриленко Сергей Алексеевич
Параллелит ли сервер вставку одной записи?


понятное дело что нет но связь с jdbc батчами мне не очевидна. батчи составны и почему бы их не параллелить?


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

Нет, конечно, я чищу кеши только для тестирования... Чтоб симитировать работу системы когда приходят совсем разные запросы и не совсем понятно какие кеши держать в памяти... Или я не совсем правильно это делаю?


pkarklin
Не делать массовую заливку через Хибернейте.


система увы уже имеет такую архитектуру... я вообще не сторонник хибера но. .. тем имеем что имеем. .. вопрос как сделать его чуть быстрее
23 янв 13, 11:45    [13814158]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

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


вопрос: почему вставка из временной таблицы такая быстрая? Ведь индексы в целевой таблице присутствуют и для вставки из временной таблицы тоже. ..
я так понимаю, потому, что скул сервер удобно умеет параллелить insert ... select

Гавриленко Сергей Алексеевич
Параллелит ли сервер вставку одной записи?


понятное дело что нет но связь с jdbc батчами мне не очевидна. батчи составны и почему бы их не параллелить?


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

Нет, конечно, я чищу кеши только для тестирования... Чтоб симитировать работу системы когда приходят совсем разные запросы и не совсем понятно какие кеши держать в памяти... Или я не совсем правильно это делаю?


pkarklin
Не делать массовую заливку через Хибернейте.


система увы уже имеет такую архитектуру... я вообще не сторонник хибера но. .. тем имеем что имеем. .. вопрос как сделать его чуть быстрее
23 янв 13, 11:47    [13814170]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
twister_mc
я вообще не сторонник хибера но. .. тем имеем что имеем. .. вопрос как сделать его чуть быстрее


Передавать на сервер не кучу инсертов (пусть даже в одной транзакции), а скопом данные. Это может быть XML, например.
23 янв 13, 11:49    [13814178]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
twister_mc
понятное дело что нет но связь с jdbc батчами мне не очевидна. батчи составны и почему бы их не параллелить?
Батч - это набор последовательных команд. Когда вы придумаете, как выполнять последовательные команды параллельно, да еще и без нарушения логики, вам дадут нобелевскую премию.

Сообщение было отредактировано: 23 янв 13, 11:55
23 янв 13, 11:53    [13814211]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
invm
Member

Откуда: Москва
Сообщений: 9397
twister_mc
вопрос: почему вставка из временной таблицы такая быстрая? я так понимаю, потому, что скул сервер удобно умеет параллелить insert ... select
Потому что вы доподлинно не знаете что там на самом деле делает хибернейт. Следовательно сравнивать не с чем.
twister_mc
Нет, конечно, я чищу кеши только для тестирования... Чтоб симитировать работу системы когда приходят совсем разные запросы и не совсем понятно какие кеши держать в памяти... Или я не совсем правильно это делаю?
Сервер сам прекрасно разберется, что ему держать в кеше.
twister_mc
вопрос как сделать его чуть быстрее
Сначала отследить профайлером, что отсылает серверу хибернейт, а по полученным результатам уже думать что делать.
23 янв 13, 11:53    [13814214]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
alexeyvg
Member

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

вопрос: почему вставка из временной таблицы такая быстрая? я так понимаю, потому, что скул сервер удобно умеет параллелить insert ... select
Потому что серверу нужно будет выполнить одну операцию для каждого индекса. Да, в этой операции будет вставляться много записей, но это всё равно быстрее (и да, внутри операции может быть и распаралеливание, нужно смотреть планы выполнения)
twister_mc
связь с jdbc батчами мне не очевидна. батчи составны и почему бы их не параллелить?
Это вопрос - почему jdbc не распаралеливает? Ну, вообще ни одно средство доступа к БД не делит передаваемый текст на множество кусков с асинхронным выполнением, это был бы просто абсурд.
twister_mc
Чтоб симитировать работу системы когда приходят совсем разные запросы и не совсем понятно какие кеши держать в памяти... Или я не совсем правильно это делаю?
Куши нужны для поиска тех записей в индексе, которые нужно менять, так что очистка кеша должна замедлять вставку. Для случая отсутствия индексов будет без разницы.
twister_mc
alexeyvg
Дело не в распаралеливании, а в том, что серверу нужно выполнить большое количество операций. Если там индексов нет, то скорость ещё хоть какая то (как вы делаете со временной таблицей), но если ещё 4 индекса, то для каждой вставки вы ещё дополнительно делаете поиск и вставку в 4 индекса.


вопрос: почему вставка из временной таблицы такая быстрая? Ведь индексы в целевой таблице присутствуют и для вставки из временной таблицы тоже. ..
я так понимаю, потому, что скул сервер удобно умеет параллелить insert ... select
Гавриленко Сергей Алексеевич
Параллелит ли сервер вставку одной записи?


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

Нет, конечно, я чищу кеши только для тестирования... Чтоб симитировать работу системы когда приходят совсем разные запросы и не совсем понятно какие кеши держать в памяти... Или я не совсем правильно это делаю?
pkarklin
Не делать массовую заливку через Хибернейте.

система увы уже имеет такую архитектуру... я вообще не сторонник хибера но. .. тем имеем что имеем. .. вопрос как сделать его чуть быстрее
Не получится быстрее.

В принципе можно сделать распаралеливание на клиенте (это собственно и есть рекламируемый подход работы со всякими ORM), но это будет всё равно на порядки медленнее "прямых" способов, ну и наверное сложно в кодировании.

Но вообще идеология Хибернейта не запрещает выполнять что то в обход ORM, разве не так?

Ну и архитектуру тоже можно менять, собственно, в чём проблема, если это не учебное задание? Тем более что система до конца не доделана, как я понимаю, ведь не работает основной функционал?
23 янв 13, 12:07    [13814313]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Crimean
Member

Откуда:
Сообщений: 13148
twister_mc
Вставляем около 100к записей
Инсерт занимает около часа


получаем 27 записей в секунду. это вообще "ниочем". я бы посмотрел профайлером куда уходит время - на собственно вставки, на компиляции батчей или на задержки между батчами для начала.

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


эм.. это средствами сиквела? или "стандартным способом"?

короче, я за оценку ситуации профайлером
если время уходит на компиляции - оборачиваем в хранимки
если время уходит на промежутки между батчами - разбираемся с прикладухой
если время уходит на чисто вставки.. - вы же проверяли, само по себе оно быстрое, вычеркиваем, остаются 2 причины
23 янв 13, 12:20    [13814423]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
twister_mc
Member

Откуда:
Сообщений: 23
Спасибо!
Буду "шатать"

Вопрос - при инсерте SQL батчем, когда будут перестраиваться индексы? В конце транзакции, для всех инсертов скопом, или для каждого отдельного инсерта?

Судя по замерам, то скопом после коммита
23 янв 13, 17:13    [13817003]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
invm
Member

Откуда: Москва
Сообщений: 9397
twister_mc
Вопрос - при инсерте SQL батчем, когда будут перестраиваться индексы? В конце транзакции, для всех инсертов скопом, или для каждого отдельного инсерта?

Судя по замерам, то скопом после коммита
А вы не гадайте и не замеряйте. Все ответы -- в плане выполнения.
23 янв 13, 17:28    [13817112]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31430
twister_mc
Вопрос - при инсерте SQL батчем, когда будут перестраиваться индексы? В конце транзакции, для всех инсертов скопом, или для каждого отдельного инсерта?
Я же написал - после каждого инсёрта.

При наличии общей транзакции скорость увеличивается по другой причине.
23 янв 13, 17:35    [13817141]     Ответить | Цитировать Сообщить модератору
 Re: Insert performance  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
twister_mc,

При попытке вставлять с помощью Linq, Entity, Hibernate и прочих подобных помощников записей на 100К Вы всегда будете иметь тормоза. Посему делать Вам нестандартный механизм (нестандартный с точки зрения вышеприведенных помощников) для вставки - по другому к сожалению не получится. Выбирайте, или SqlBulkCopy или Table-value parameter или временная таблица.
23 янв 13, 20:18    [13817983]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить