0. YPermitin 2508 09.03.19 22:15 Сейчас в теме

Быстрее чем INSERT! BULK-операции и примеры использования

Microsoft SQL Server поддерживает так называемые BULK-операции, используемые для быстрого изменения больших объемов данных в базе. В статье пойдет речь о практических примерах их использования. Все примеры сделаны в контексте платформы 1С (а как иначе).

Перейти к публикации

Комментарии
Избранное Подписка Сортировка: Древо
1. acanta 55 09.03.19 22:51 Сейчас в теме
убедить разработчика не использовать параметры вывода (OUTPUT)

Разработчики PG / SQL не используют записи логов для информирования о результатах или из одного внешнего источника данных нельзя переключиться на другой источник, например текстовый файл или таблицу с результатами?
2. YPermitin 2508 09.03.19 23:35 Сейчас в теме
(1) Если правильно понял, то можно конечно. Другое дело что это будет не хорошо, т.к. фактически создает искусственные ограничения для разработчика БД и могут привести к проблемам с производительностью. Те же возвращаемые параметры это частоиспользуемая функция TSQL.

Вообщем, обходные пути есть, но иногда это костыли и кошмары разработчика БД. :)
3. julego 10.03.19 10:55 Сейчас в теме
Было бы здорово ещё увидеть пример кода, возвращающего output-параметр из внешней хранимой процедуры. Возможно?
Aletar; user774630; YPermitin; +3 Ответить
4. YPermitin 2508 10.03.19 11:04 Сейчас в теме
(3) пример с ADO или внешними источниками данных?

В любом случае можно.
5. Aletar 11.03.19 05:55 Сейчас в теме
(4) А как? Я тоже сталкивался с такой проблемой. Если это функция, то все понятно, но как получить output-параметры хранимой процедуры через внешние источники данных?
6. YPermitin 2508 11.03.19 06:22 Сейчас в теме
(5) Есть огромный костыль по использованию глобальной временной таблицы. При вызове хранимки выходные параметры записываются туда. А считать их можно потом отдельно простым селектом. Со всеми вытекающими, но вариант рабочий.

Могу позже сделать пример и выложить ответным комментарием.
julego; Aletar; +2 Ответить
7. Aletar 11.03.19 06:24 Сейчас в теме
23. YPermitin 2508 13.03.19 23:38 Сейчас в теме
(7) лучше поздно, чем никогда :)

Вот ответ на вопрос со всеми примерами. И даже чуть больше.

https://infostart.ru/public/1019947/
10. julego 11.03.19 09:11 Сейчас в теме
(4) С внешними источниками понятно, костыли, но тоже интересно. С ADO должно быть проще и правильнее?
11. YPermitin 2508 11.03.19 09:17 Сейчас в теме
(10) правильнее или нет не знаю, наверное от задачи лучше смотреть. Но проше точно. Вечером постараюсь найти время и сделать пример.

На нем и сами определите что лучше :)
24. YPermitin 2508 14.03.19 10:15 Сейчас в теме
(10) лучше поздно, чем никогда :)

Вот ответ на вопрос со всеми примерами. И даже чуть больше.

https://infostart.ru/public/1019947/
25. julego 16.03.19 06:07 Сейчас в теме
(24)Спасибо! Признательна за такое подробное освещение вопроса.
8. PerlAmutor 35 11.03.19 06:39 Сейчас в теме
Задачу быстрой загрузки данных решал несколько иначе, без INSERTов. Сначала через BULK выгрузил пример таблицы в двоичный (.dat) файл. Разобрал его структуру, воспроизвел формирование двоичных данных скриптом. Дальше подставлял в параметр и все - миллионы строк во временной таблице. Оттуда данные уже перемещал в основную таблицу.
YPermitin; +1 Ответить
9. YPermitin 2508 11.03.19 06:49 Сейчас в теме
(8) интересное решение!

А можно где-нибудь посмотреть пример такой выгрузки? Для интереса.
19. PerlAmutor 35 12.03.19 06:16 Сейчас в теме
(9) К сожалению, нет. Мои изыскания достались компании на которую я работал, после её ликвидации все наработки остались на серверах руководства.
12. Quantum80 11.03.19 09:31 Сейчас в теме
Быстро сформировать большой csv файл на 1С тоже может оказаться не тривиальной задачей.
YPermitin; +1 Ответить
13. YPermitin 2508 11.03.19 09:35 Сейчас в теме
(12) тоже верно.

Средствами 1С это может занять длительное время. Тут вариантов несколько:
1. Делать в несколько потоков выгрузку нескольких файлов.
2. Делать с помощью внешних компонентов.
3. Использовать не 1Сные средства, как в нескольких примерах из статьи.
4. И конечно же не использовать BULK, где он не эффективен.
17. Loklir 11.03.19 16:45 Сейчас в теме
(12)Я использую выгрузку не в csv а в XML, и OPENROWSET на стороне сервера. Как по мне то так гораздо быстрей.
YPermitin; +1 Ответить
18. YPermitin 2508 11.03.19 17:58 Сейчас в теме
(17) не ожидал увидеть упоминание про OPENROWSET в комментариях! Спасибо, что написали!

OPENROWSET это нечто иное, это не только BULK-операции, это еще и возможность удаленное взаимодействия с различными источниками данных, в т.ч. и CSV, XML, XLSX, другими инстансами SQL Server и даже PG и т.д. Все то, для чего существуют поставщики, даже ElasticSearch!

OPENROWSET может использовать для BULK-операций, но в прочих равных условиях использование нативного формата выгрузки и загрузки через BCP обычно быстрее, в то же время менее функциональное.

Не знаю конкретно Вашу ситуацию, но трудно судить почему это будет работать быстрее чем BULK-операции. Возможно сам этап формирования CSV занимает больше времени. В любом случае, OPENROWSET - это отличный инструмент при работы со многими источниками данных. В статье про него не стал писать, т.к. это вроде как и не совсем BULK, и вроде как если прикрутить его к 1С, то страшно может стать :)
Loklir; acanta; +2 Ответить
14. Darklight 17 11.03.19 09:57 Сейчас в теме

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

Таким образом, сразу можно зафиксировать, что инструкция BULK INSERT не всегда может подойти для загрузки, особенно в контексте платформы 1С, где ссылки и некоторые другие поля хранятся в виде "varbinary".


такое ограничение сводит на нет большинство полезных стратегий применения BULK INSERT между таблицами баз 1С, которые почти всегда имеют UUIDы (и не только), хранящиеся как varbinary.

Но за статью спасибо.
YPermitin; +1 Ответить
15. YPermitin 2508 11.03.19 10:05 Сейчас в теме
(14) здесь надо понимать, что речь про инструкцию TSQL "Bulk Insert".

Ограничение можно обойти, если использовать BCP, который использует собственный формат выгрузки. Двоичные данные не удастся передать, если делать выгрузку в CSV.

Можно сделать свой алгоритм выгрузки из 1С в формат BCP, как предлагал коллега в комментариях.

Вообщем, это не безвыходная ситуация.
16. Darklight 17 11.03.19 13:13 Сейчас в теме
(15)Ок, спасибо за пояснения
20. geron4 90 12.03.19 08:29 Сейчас в теме
Спасибо! Хороший материал!
Как заметил автор, использование вышеописанных методов подходит для случаев, где они действительно нужны, если есть время и возможность - лучше пользоваться типовыми механизмами 1С, но действительно бывают случаи, когда нужна скорость выполнения, например операция не может закончится в адекватное время.
21. starik-2005 1852 12.03.19 10:31 Сейчас в теме
Чисто за картинку! Потом может прочитаю )))
YPermitin; +1 Ответить
22. YPermitin 2508 12.03.19 10:36 Сейчас в теме
(21) статью готовил 2 часа, из них 1 час на мемы :)
26. rukalico 27.03.19 14:33 Сейчас в теме
Юрий, добрый день!

Почему во всей статье приводится приvер чтения из промежуточного csv файла?
Разве не логичнее писать во внешнюю таблицу напрямую из 1С выборки запроса.
В статье везде тем не менее файл, это наводит на мысль - есть ли здесь какой то нюанс?
YPermitin; +1 Ответить
27. YPermitin 2508 27.03.19 14:40 Сейчас в теме
(26) нюансы есть.

Например, вот вопросы, которые можно себе задать для полного понимания:
1. Что значит писать напрямую из выборки 1С?
2. Что такое выборка данных в 1С и как она реализована?

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

А так, если кратко: напрямую в базу можно фактически только через ADO или внешние источники, но это не оптимально при большом объеме, т.к. реализовано через большоое количество операций INSERT.

Также есть информация. Что выбока данных - это фактически работа с результатом запроса, созраненного в файл (сеансовые данные). Точных экспериментов мало ставил, но уверенность есть.
28. rukalico 27.03.19 14:59 Сейчас в теме
(27) Имеется ввиду Запрос.Выполнить().Выбрать()
То есть если нужно записать во внешнюю таблицу разве для этого нужно делать промежуточную выгрузку в csv? И почему этот способ как основной пример в статье рассматривается.. Вот это я не понял.
29. YPermitin 2508 27.03.19 18:12 Сейчас в теме
(28)
если нужно записать во внешнюю таблицу разве для этого нужно делать промежуточную выгрузку в csv? И почему этот способ как основной пример в статье рассматривается.. Вот это


Если получить данные через выборку, а потом вставлять во внешнюю таблицу отдельными операциями INSERT, то это конечно будет медленнее для больших объемов данных, чем использование BULK INSERT.

BULK INSERT можно делать из внешнего файла либо CSV формата, либо нативного формата SQL Server, который используется утилитой BCP. То есть в статье все же два варианта выгрузки, а не только CSV.
30. nvv1970 06.05.19 12:13 Сейчас в теме
Появился повод вернуться к статье, подискутировать и обсудить пару нюансов...

Есть задача подчистить некоторые данные в некоторой базе. Однако, из-за неуверенности, что заказчик верно сформулировал условия удаления - решил некоторые удаляемые данные сохранить во внешнюю базу. База 0.5Тб, бэкап развернуть тупо негде в случае восстановления каких-то отдельных записей.
Есть проблема: дисковая подсистема дохлая и показывает не более 20-40Мб/с (и это при последовательном доступе, пики до 100Мб/с - единичны, часто деградация до 1-5 Мб/с). А скопипастить нужно минимум 50+Гб, 200+ млн строк (этот же сервер, но диски источник-приемник разные).

В добавок к статье хотелось бы сравнить способы `bulk ins ert`, `select into`, `ins ert in to`.

Например, select into выполняет минимальное логирование и выполняется достаточно быстро. Не нагуглил реальных сравнений с "балком"
Вопрос (select into vs bulk ins ert): на практике какая будет разница прямого копирования и выгрузки+загрузки через файл? Поделитесь результатами тестов.

К слову, `Ins ert in to` тоже может быть таким же быстрым как и `select into` при соблюдении ряда условий (все есть в документации): simple, tablock изменяемой таблицы, отсутствие индексов. Миллионы строк вставляются за секунды.

Однако ВСТАВКА - это НЕ САМОЕ узкое место оказалось в моей задаче!
Таблицы в приемнике делаю сжатыми, но это не повлияло на результат.
Чертовы pageiolatch_sh в базе источнике ((

Пример на основе `sele ct into`:
1. выполняем копирование основной таблицы регистра накопления (10-15 колонок) - копирование 1млн строк выполняется за 10-20 сек, в таблице 30млн строк = 10 Гб, скорость дисков 30-40Мб/с. Ожидания на чтении возникают редко.
2. выполняем копирование регистра версии объектов (наиболее показательный для сравнения пример, но по факту данные конечно же не нужны для резервирования)... 1,5 млн=11Гб... даже 10000 строк начинает здорово задумываться. Стабильные, постоянные и длительные pageiolatch... Чтение еле шевелится. 1-3Мб/с. Так можно дождаться "второго пришествия" или как минимум нового года ((

Первые подозрения: шпиндели и фрагментация. Однако ситуация точно ровно такая же и у других клиентов: версии объектов даже последовательно читаются крайне медленно (кстати, коллеги, а как у вас?). Исключал в тестировании влияние буфера, т.е. сравниваем только физическое чтение.
Я думаю, что все дело в blob-объектах (image) регистра. Вероятно они и провоцируют непоследовательное чтение.
(Как MS ни ругается, что пора прекратить использовать устаревшие blob-типы image и ntext - 1С явно не спешит от них отказываться, хранение var***(max) кажется реализовано иначе, т.е. прямо в страницах с данными... Лучше перечитать BOL, мог что-то забыть и соврать)

Еще один важный вопрос с учетом предыдущей проблемы:
- как при переносе данных реализовать порционность и гарантировать последовательное чтение исходных данных?
Например, основная таблица регистров накопления - это всегда период. Можно по месяцу вычитывать и переносить. Данные по периодам распределены более-менее равномерно, чтение внутри периода - последовательно (при отсутствии фрагментации). Нет необходимости формировать таблицу ключей.
А вот непериодические РС? Делать временную упорядоченную таблицу ключей кластерного индекса (например, только первые поля)? Очень индивидуальное решение для каждого регистра.
Как еще можно реализовать порционность при условии, что исходные данные после каждой порции переноса не удаляются?
31. YPermitin 2508 06.05.19 13:11 Сейчас в теме
(30) спасибо за развернутый комментарий. Постараюсь на него ответить, но позже.

Очень обширные вопросы задали, с телефона пальцы устанут печатать :)
Aleskey_K; +1 Ответить
Оставьте свое сообщение
Новые вопросы с вознаграждением
Автор темы объявил вознаграждение за найденный ответ, его получит тот, кто первый поможет автору.

Вакансии

Консультант 1С
Нижний Новгород
зарплата до 100 000 руб.
Полный день


Программист 1С
Бобров
зарплата от 100 000 руб. до 150 000 руб.
Временный (на проект)

Студент (стажер) 1С
Нижний Новгород
зарплата от 25 000 руб.
Полный день

Программист 1С
Санкт-Петербург
зарплата от 120 000 руб.
Полный день