При интеграции 1С с внешними базами данных, такими как MySQL, разработчики часто сталкиваются с проблемой производительности. Стандартная отправка запросов по одному (например, 10 000 операций UPDATE) может занимать недопустимо много времени из-за сетевых задержек и накладных расходов на выполнение каждой отдельной команды. Попытка же отправить строку вида UPDATE...; UPDATE...; часто приводит к синтаксической ошибке.
В этой статье мы разберем, почему возникает ошибка при отправке нескольких запросов, как настроить драйвер для поддержки пакетов и как реализовать высокопроизводительное обновление данных с помощью метода UpdateBatch.
Рассмотрим ситуацию: вы пытаетесь выполнить в одном объекте ADODB.Command сразу два запроса, разделенных точкой с запятой. В ответ система выдает исключение: "You have an error in your SQL syntax; check the manual... near 'UPDATE...'".
Проанализируем причину. По умолчанию большинство ODBC-драйверов MySQL блокируют выполнение нескольких операторов в одном запросе (Multi-Statements) в целях безопасности. Это защищает базу данных от SQL-инъекций. Чтобы драйвер разрешил выполнение пакета, необходимо изменить строку подключения (Connection String), добавив в нее специальный флаг:
Option=67108864 или параметр MULTI_STATEMENTS=1.
Только после этой настройки сервер MySQL примет строку с несколькими командами UPDATE или INSERT, разделенными символом ;.
Более профессиональным и надежным способом в среде 1С является использование объекта ADODB.RecordSet и его метода UpdateBatch. Этот подход позволяет подготовить изменения локально и отправить их на сервер одним пакетом.
Разберем по шагам, как реализовать этот алгоритм на сервере 1С:
adOpenStatic (3) или adOpenKeyset (1) в сочетании с adLockBatchOptimistic (4).RecordSet.UpdateBatch через определенные промежутки (например, каждые 3000 строк), чтобы не переполнять память.Посмотрим на пример реализации процедуры обновления данных:
&НаСервере
Процедура ПроцедураUPDATE(Command, Таблица, ИмяПоиска)
СчетчикЛ = 0;
// Перебираем записи в RecordSet, полученном из SQL
Пока не Command.EOF Цикл
// Ищем соответствующую строку в нашей таблице значений 1С
Отбор = Новый Структура(ИмяПоиска, Command.Fields(ИмяПоиска).Value);
НайденыйЭлемент = Таблица.НайтиСтроки(Отбор);
Если НайденыйЭлемент.Количество() > 0 Тогда
Для j = 0 По Таблица.Колонки.Количество() - 1 Цикл
ИмяКолонки = Таблица.Колонки[j].Имя;
Значение = НайденыйЭлемент[0][j];
// Заполняем поля RecordSet данными из 1С
Если ЗначениеЗаполнено(Значение) Тогда
Попытка
Command.Fields(ИмяКолонки).Value = Значение;
Исключение
// Обработка ошибок типов данных
Command.Fields(ИмяКолонки).Value = Строка(Значение);
КонецПопытки;
КонецЕсли;
КонецЦикла;
КонецЕсли;
Command.MoveNext();
// Отправляем данные пакетами по 3000 записей
СчетчикЛ = СчетчикЛ + 1;
Если СчетчикЛ >= 3000 Тогда
Command.UpdateBatch();
СчетчикЛ = 0;
КонецЕсли;
КонецЦикла;
// Финальная отправка остатков
Если СчетчикЛ > 0 Тогда
Command.UpdateBatch();
КонецЕсли;
КонецПроцедуры
Для добавления новых данных используется метод AddNew. Принцип работы с UpdateBatch остается таким же: мы не сохраняем каждую строку отдельно, а накапливаем изменения в буфере RecordSet.
&НаСервере
Процедура ПроцедураINSERT(Command, Таблица)
СчетчикЛ = 0;
Для i = 0 По Таблица.Количество() - 1 Цикл
Command.AddNew();
Для j = 0 По Таблица.Колонки.Количество() - 1 Цикл
ИмяПоля = Таблица.Колонки[j].Имя;
Значение = Таблица[i][j];
Если Значение <> Неопределено Тогда
Command.Fields(ИмяПоля).Value = Значение;
КонецЕсли;
КонецЦикла;
СчетчикЛ = СчетчикЛ + 1;
Если СчетчикЛ >= 3000 Тогда
Command.UpdateBatch();
СчетчикЛ = 0;
КонецЕсли;
КонецЦикла;
Попытка
Command.UpdateBatch();
Command.Close();
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
КонецПроцедуры
Если вы хотите добиться максимальной производительности в MySQL без использования сложных объектов ADO, проанализируем возможность использования специфического синтаксиса MySQL. Вместо разделения логики на "поиск существующей записи" и "вставку новой", можно отправить один массив данных:
INSERT INTO table (id, field1) VALUES (1, 'val1'), (2, 'val2') ON DUPLICATE KEY UPDATE field1 = VALUES(field1);
Преимущества этого метода:
RecordSet.Выясним причину, по которой даже пакетные запросы могут работать медленно. Если база данных работает в режиме Auto-Commit, она подтверждает запись на диск после каждой порции данных. Чтобы ускорить процесс в десятки раз, необходимо явно управлять транзакциями через объект соединения Connection:
Соединение.BeginTrans(); перед началом цикла обработки.UpdateBatch или пакетные INSERT.Соединение.CommitTrans(); в самом конце.Важный нюанс: При использовании транзакций обязательно добавляйте блок Попытка...Исключение, чтобы в случае ошибки вызвать RollbackTrans(), иначе база данных может заблокировать таблицы.
Проанализируем ситуацию, когда пакет запросов корректен, но сервер все равно выдает ошибку. В настройках MySQL существует параметр max_allowed_packet. Если ваш пакет данных (например, при выгрузке больших текстовых описаний товаров) превышает этот лимит (по умолчанию может быть 4 Мб), сервер разорвет соединение. Рекомендуется увеличить этот параметр в файле конфигурации my.ini до 16 или 64 Мб при работе с большими объемами данных из 1С.
Таким образом, мы рассмотрели несколько способов оптимизации: от настройки драйвера для поддержки Multi-Statements до использования метода UpdateBatch и оптимизации на уровне SQL-синтаксиса. Выбор метода зависит от объема ваших данных и требований к надежности транзакций.