При интеграции 1С с внешними базами данных MySQL через механизм ADODB разработчики часто сталкиваются с серьезной проблемой производительности. Попытка выполнить серию команд UPDATE или INSERT, разделенных точкой с запятой, через метод Execute приводит к ошибке синтаксиса. При этом выполнение каждой команды по отдельности занимает слишком много времени, особенно когда речь идет о десятках тысяч записей. В этой статье мы подробно разберем, почему это происходит и как заставить 1С передавать пакетные запросы эффективно.
Разберем ситуацию: стандартный драйвер MySQL ODBC по умолчанию настроен на выполнение только одного SQL-заражения за один вызов. Когда мы передаем строку вида "UPDATE tbl SET f1=1; UPDATE tbl SET f1=2", драйвер воспринимает символ ; как окончание запроса, а следующий текст — как мусор или некорректную команду. Это сделано в целях безопасности, чтобы предотвратить SQL-инъекции, когда злоумышленник может дописать вредоносный код через точку с запятой.
Однако для задач оптимизации нам жизненно необходимо передавать данные пакетами. Рассмотрим основные способы решения этой задачи.
Самый простой и эффективный способ — разрешить драйверу обработку нескольких выражений в одном запросе. Для этого в строку соединения (Connection String) необходимо добавить специальный параметр MULTI_STATEMENTS=1.
Проанализируем пример формирования строки подключения в коде 1С:
Хост = "192.168.1.10";
БазаДанных = "my_database";
Порт = "3306";
Пользователь = "admin";
Пароль = "password";
// Формируем строку соединения с поддержкой пакетных запросов
СтрокаСоединения = СтрШаблон("DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=%1; DATABASE=%2; PORT=%3; UID=%4; PWD=%5; MULTI_STATEMENTS=1",
Хост, БазаДанных, Порт, Пользователь, Пароль);
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = СтрокаСоединения;
Соединение.Open();
// Теперь такой запрос выполнится без ошибок
ТекстЗапроса = "UPDATE goods SET price = 100 WHERE id = 1; UPDATE goods SET price = 200 WHERE id = 2;";
Соединение.Execute(ТекстЗапроса);
Важный нюанс: Если вы используете старые версии драйверов, параметр может называться иначе. Иногда используется числовой флаг в параметре OPTION. Для включения пакетных запросов к текущему значению OPTION нужно прибавить 67108864. Это битовая маска, активирующая поддержку множественных стейтментов.
Если по каким-то причинам настройка драйвера не помогает или вы хотите добиться максимальной скорости на огромных объемах (сотни тысяч строк), пакет одиночных UPDATE — не самый лучший выбор. Рассмотрим более профессиональный подход: использование временной таблицы.
Суть метода заключается в следующем:
INSERT INTO ... VALUES (...), (...), (...).UPDATE с использованием JOIN между основной и временной таблицей.Посмотрим на практическую реализацию этого алгоритма:
// 1. Создаем временную таблицу (или очищаем существующую)
Соединение.Execute("CREATE TEMPORARY TABLE IF NOT EXISTS temp_updates (id INT PRIMARY KEY, new_name VARCHAR(255))");
Соединение.Execute("TRUNCATE TABLE temp_updates");
// 2. Формируем один массивный INSERT
// Вместо тысяч UPDATE мы делаем ОДИН INSERT на все данные
ТекстЗапроса = "INSERT INTO temp_updates (id, new_name) VALUES ";
СписокЗначений = Новый Массив;
Для Каждого СтрокаДанных Из ТаблицаДанных Цикл
СписокЗначений.Добавить(СтрШаблон("(%1, '%2')", СтрокаДанных.Идентификатор, СтрокаДанных.Наименование));
КонецЦикла;
// Объединяем значения через запятую и выполняем
ТекстЗапроса = ТекстЗапроса + СтрСоединить(СписокЗначений, ", ");
Соединение.Execute(ТекстЗапроса);
// 3. Выполняем массовое обновление одним запросом JOIN
ТекстОбновления = "UPDATE original_table
|INNER JOIN temp_updates ON original_table.id = temp_updates.id
|SET original_table.name = temp_updates.new_name";
Соединение.Execute(ТекстОбновления);
Этот метод работает в десятки раз быстрее, так как СУБД минимизирует количество операций записи в лог транзакций и оптимизирует поиск по индексам.
Выясним еще один способ обновления разных строк в одном запросе без использования временных таблиц. Это конструкция UPDATE ... SET field = CASE .... Она полезна, когда нужно обновить небольшое или среднее количество строк.
Пример логики запроса:
UPDATE tbl
SET name = CASE
WHEN id = 1 THEN 'Один'
WHEN id = 2 THEN 'Два'
WHEN id = 3 THEN 'Три'
END
WHERE id IN (1, 2, 3);
В 1С это можно сформировать динамически в цикле. Плюс этого метода в том, что он является стандартным SQL-запросом и не требует специальных прав на создание таблиц или настройки драйвера MULTI_STATEMENTS.
Если логика обновления очень сложная, можно упаковать все команды в хранимую процедуру на стороне MySQL. Вызов процедуры из 1С всегда считается одиночной командой, даже если внутри процедуры выполняются сотни операций. Рассмотрим пример вызова:
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Соединение;
Command.CommandText = "call my_update_procedure()";
Command.CommandType = 4; // adCmdStoredProc
Command.Execute();
Работая с большими пакетами данных через ADODB, помните о следующих ограничениях:
max_allowed_packet. Если ваш сформированный текст запроса (например, со 100 000 значений VALUES) превысит этот размер (обычно 4-16 Мб), сервер разорвет соединение. Рекомендуется разбивать большие пакеты на части по 500-1000 строк.START TRANSACTION; и COMMIT;. Это предотвратит фиксацию каждой строки на диске и колоссально ускорит процесс.СтрЗаменить(Значение, "'", "''"), чтобы избежать ошибок, если в наименовании товара встретится кавычка.Таким образом, мы проанализировали ситуацию и выяснили, что наиболее надежным и современным способом является добавление MULTI_STATEMENTS=1 в строку подключения. Для задач с экстремальной нагрузкой лучшим выбором остается использование временных таблиц с последующим JOIN.