Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как ускорить вставки из SqlBulkCopy  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Есть табличка с 1,5 миллиардов строк.
Вставки идут "кучками" по примерно 100.000 строк.
Время вставки одной "кучки" примерно 10 сек.

Какие есть возможности увеличить скорость вставки?

Таблица:
CREATE TABLE FooTable
(
 FooTableID		bigint IDENTITY(1,1) NOT NULL 
,Entity1ID		int NOT NULL
,Entity2ID		int NOT NULL
,Entity3ID		int NOT NULL
,Valid			bit
,CONSTRAINT PK_FooTable PRIMARY KEY CLUSTERED (FooTableID)
 )WITH (DATA_COMPRESSION = PAGE);

Примечания:
Столбец FooTableIDне обязателен
PRIMARY KEY или уникальный индекс может быть создан на столбцах Entity1,Entity2,Entity3
Столбец Entity1 может быть использован для партиционирования с шагом 1000000
Удаление ПК и его пересоздание после импорта занимает слишком много времени, поэтому неприемлимо
Игры с опциями SqlBulkCopy не приносят видимого эффекта
Диск - обычный, не SSD.

Варианты, которые приходят в голову:
- партиционировать таблицу
- создать уникальный индекс на Entity1ID,Entity2ID,Entity3ID и партиционировать его по полю Entity1ID
- деактивировать сжатие

Что Вы бы посоветовали?
15 сен 15, 16:37    [18152196]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Alexander Us,

Один из способов ускорить bulk insert -- подавать записи во входной потоке в порядке сортировки первичного ключа. То есть отсортировать в вашем datatable их в порядке ключа таблицы, куда вставляете.

В остальном вот рекомендации


https://technet.microsoft.com/en-us/library/ms190421(v=sql.105).aspx
15 сен 15, 16:41    [18152218]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Кстати bulk insert очень хорошо работает в In-Memory таблицу. Потому что при этом нет никакого логирования (кроме момента коммита).
15 сен 15, 16:42    [18152231]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
0) не озвучили версию...
1) Убедиться, что модель восстановления < Full
2) В несжатую таблицу вставка всегда идёт быстрее
3) максимальную скорость заливки можно обеспечить при вставки данных в таблицы-кучи
4) увеличение производительности возможно за счёт распараллеливания (секционирование, вставка в N-потоков, свитч данных)
5) в данном случаи, действительно ин-меммори позволит разогнать массовые операции вставки
15 сен 15, 16:49    [18152282]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
a_voronin
Кстати bulk insert очень хорошо работает в In-Memory таблицу. Потому что при этом нет никакого логирования (кроме момента коммита).

всю 1.5 ярдную таблицу инмемори держать???
15 сен 15, 16:59    [18152337]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Knyazev Alexey
0) не озвучили версию...
1) Убедиться, что модель восстановления < Full
2) В несжатую таблицу вставка всегда идёт быстрее
3) максимальную скорость заливки можно обеспечить при вставки данных в таблицы-кучи
4) увеличение производительности возможно за счёт распараллеливания (секционирование, вставка в N-потоков, свитч данных)
5) в данном случаи, действительно ин-меммори позволит разогнать массовые операции вставки


1) модель восстановления = simple
2) попробую, но видимо несжатаю таблица будет дольше читаться
3) низя, ПК нужен, а его пересоздание после импорта идёт слишком долго
4) не получилось: импорт идёт из .NET приложения, вставлять надо в транзакции в Х таблиц.
я попробовал параллельно вставлять 2,3 таблицы но это надо делать в обном коннекшне и одной транзакции... не вышло
5) сжатая таблица весит 80ГБ, в сервере 32ГБ оперативки
15 сен 15, 17:10    [18152408]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Alexander Us
...


а что тормозит выяснили?

попробуйте собрать ожидания сессии заливки. вдруг, чего интересного вылезет в top.

ну и еще можно потом посмотреть кусок transaction log'a, относящегося к заливке "кучки" данных там тоже можно много интересного откопать (что же на самом деле происходит?). тем более по вашим словам это одна транзакция - будет легко все отфильтровать.

Alexander Us
Игры с опциями SqlBulkCopy не приносят видимого эффекта


Выполнены ли требования minimally logging? Нет, конечно.

Если не хотите пересоздавать весь индекс и хотите иметь выигрыш от minimally logging, и от
Alexander Us
Столбец Entity1 может быть использован для партиционирования с шагом 1000000

и в одной "кучке" данных Entity1 одинаковое, можно попробовтаь так:

- базу в bulk_logged
- switch секции в таблицу той же структуры (metadata operation) (максимум 1 000 000 строк по вашему условию)
- drop clustered index (minimally loged)
- bulk load (minimally loged)
- create index (minimally logged)
- switch секции обратно

Alexander Us
импорт идёт из .NET приложения


показали бы код.
15 сен 15, 17:39    [18152520]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Ivan Durak
a_voronin
Кстати bulk insert очень хорошо работает в In-Memory таблицу. Потому что при этом нет никакого логирования (кроме момента коммита).

всю 1.5 ярдную таблицу инмемори держать???


Чего так примитивно. В качестве буфера и когда данные в ней, уже вставлять в конечную таблицу одной транзакцией.
15 сен 15, 18:01    [18152615]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
o-o
Guest
churupaha,
только зачем ему базу в bulk_logged переводить, у него же и так простая модель :)
15 сен 15, 18:12    [18152654]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
еще, если сможете при секционировании по Entity1 выделить понятие активной секции, и архивных секций (не все ли они у вас активные, судя по структуре?) то активную секцию можно сделать без компрессии, а архивные с page компрессией.

o-o
churupaha, только зачем ему базу в bulk_logged переводить, у него же и так простая модель :)


ага
15 сен 15, 18:26    [18152715]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
o-o
Guest
a_voronin
Ivan Durak
пропущено...

всю 1.5 ярдную таблицу инмемори держать???


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

Объясните, плиз, чем будет быстрее вставка через буфер. Когда вставляем в непустую кластерную таблицу хоть откуда, логируем полностью эту вставку.
Теперь вместо t1 --> t2 у нас t1 --> inmemory --> t2.
Вставка в t2 как логировалась, так и продолжает, кластер никуда не делся (вот churupaha таблицу в кучу перестраивает, а вы нет), зато еще льем в это инмемори, почему станет быстрее?
15 сен 15, 18:35    [18152749]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
o-o
a_voronin
пропущено...


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

Объясните, плиз, чем будет быстрее вставка через буфер. Когда вставляем в непустую кластерную таблицу хоть откуда, логируем полностью эту вставку.
Теперь вместо t1 --> t2 у нас t1 --> inmemory --> t2.
Вставка в t2 как логировалась, так и продолжает, кластер никуда не делся (вот churupaha таблицу в кучу перестраивает, а вы нет), зато еще льем в это инмемори, почему станет быстрее?


1) Можно некоторое время держать "t1 UNION ALL t1_InMemory", а когда серверу полегчает скинуть t1_InMemeory -> t1 .
2) Когда сервер долбит огромное кол-во других транзакции -- производительность BULK INSERT в InMemory деградирует не так быстро, как BULK INSERT в другие виды таблиц
3) Вставка в нормальном хранилище всегда делается через промежуточный буфер, так как исходные данные во многих случаях укладываются в конечную таблицу с некоторым преобразованием, а не 1 в 1, поэтому использовать в качестве буфера InMemory вполне логично
15 сен 15, 18:53    [18152859]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
o-o
Guest
1. А если "не полегчает"?
2. Я у ТС не вижу ни намека на преобразования
3. Ну вы сами пишете, что в другие виды таблиц, кроме инмемори, вставка будет тормозить. Я и говорю, оставим в стороне вставку в инмемори и вернемся к t2, у ТС это обычная" дисковая" кластерная таблица. Вот почему в нее станет быстрее литься? Что вы такое предлагаете, что в нее быстрее начнет вставляться?
15 сен 15, 19:30    [18153087]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3618
a_voronin,
инмемори - это какой-то изврат. Не вижу смысла. Еще и вьюхи городить.
Классика - вставка в новую совершенно пустую таблицу, а потом подключение партицией к основной.
15 сен 15, 19:32    [18153095]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Ivan Durak
a_voronin,
инмемори - это какой-то изврат. Не вижу смысла. Еще и вьюхи городить.
Классика - вставка в новую совершенно пустую таблицу, а потом подключение партицией к основной.


Ну вас, я вообще с BigData развлекаюсь сейчас.
15 сен 15, 20:03    [18153197]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
Ivan Durak
Классика - вставка в новую совершенно пустую таблицу, а потом подключение партицией к основной.
+1
15 сен 15, 21:17    [18153480]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
babona
Member [заблокирован]

Откуда: Батуринск
Сообщений: 1816
+1
а вот в данном случае про In memory поддержу a_voronin
Надо пробовать, кроме того, RAM возможно нарастить, не такая она уж дорогая, как когда-то
15 сен 15, 22:23    [18153766]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
babona
а вот в данном случае про In memory поддержу a_voronin
Надо пробовать, кроме того, RAM возможно нарастить, не такая она уж дорогая, как когда-то
Это если нужна обработка.

В случае ТС - только замедлит. У ТС просто вставка уже обработанных (в приложении) данных в таблицу. Зачем на MSSQL делать ещё одно действие с этими данными, пусть это действие будет в inmemory и быстрым? К тому же это тоже не бесплатно, как минимум эти данные вытеснят кеш.
15 сен 15, 23:09    [18153939]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
o-o
Guest
alexeyvg,

Ну что вы не порадуетесь за форум, вот был у нас один специалист по инмемори, а теперь целых два! Благородный дон бабона вместо временно пославшего нас a_voronin-a сейчас популярно расскажет о том, куда денется полное логирование при вставке в непустую кластерную таблицу из ИНМЕМОРИ. Правда же, babona? У меня же память на лица, товарищ babona уже дважды отключал логирование при апдэйте, в этой самой ветке. И сейчас отключит. Вот нарастит память ТС-у и сервер из одной только благодарности тут же перестанет писать в свой бесполезный transaction log
15 сен 15, 23:46    [18154039]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33970
Блог
Alexander Us
Варианты, которые приходят в голову:
- партиционировать таблицу


будет еще медленнее, т.к. добавится этап сортировки в tempdb
16 сен 15, 00:02    [18154059]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33970
Блог
конечно, если вставлять прямо в секционированную таблицу
16 сен 15, 00:02    [18154061]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Спасибо всем,

думаю придётся поставить SSD - не дёшево зато сердито.
16 сен 15, 09:46    [18154535]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Ееще немного идей
16 сен 15, 09:55    [18154570]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Alexander Us
Спасибо всем,

думаю придётся поставить SSD - не дёшево зато сердито.


та и правильно, зачем разбираться в причинах.
16 сен 15, 10:03    [18154618]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить вставки из SqlBulkCopy  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Народ, вы чего прикалываетесь? Параллельные вычисления вам помогут! Ваши замеры к сожалению изначально неверны, технология измерений хромает. К примеру балк инсер не даст сразу большого прироста по времени в однопоточном тесте(Но , если обратить внимание на трассировку, могут открыться другие интересные факты!). При этом сделайте многопоточный нагрузочный тест и вы увидите совершенно другие результаты. Могу поспорить на ящик пива с ТС что выжму с его сервака больше в 10-ть(ну объективно в 4-е точно) раз скорость чем SSD при его подходе...(кстати узким местом будет не диск).
18 сен 15, 01:42    [18163737]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить