При интеграции 1С с внешними базами данных разработчики часто сталкиваются с проблемой низкой производительности при выборке больших объемов информации. Типичный сценарий: база данных Firebird содержит сотни тысяч записей в таблице заказов, и нам требуется регулярно выгружать из нее документы за определенный период. При использовании механизма ADO (ActiveX Data Objects) может возникнуть неприятный эффект, когда метод Open() отрабатывает мгновенно, а при первой же попытке проверить свойство EOF (конец файла) система «зависает» на длительное время (вплоть до получаса).
В этой статье мы подробно разберем причины возникновения этой проблемы и рассмотрим эффективные методы оптимизации запросов и настроек подключения, которые помогут сократить время ожидания с десятков минут до нескольких секунд.
Проанализируем исходный код, который часто приводит к подобным задержкам:
Conn = Новый COMОбъект ("ADODB.Connection");
Conn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind");
RS = Новый COMОбъект ("ADODB.RecordSet");
RS.Open("select * from orders where orders.sdate + orders.stime >= cast('2018-08-30 15:00:00' as timestamp)", Conn);
Пока НЕ (RS.EOF) Цикл // На этой строке происходит длительное зависание
Сообщить(RS.Fields(1).Value());
RS.MoveNext();
КонецЦикла;
RS.Close();
Прежде всего разберем, почему «тупняк» происходит именно на строке Пока НЕ (RS.EOF), а не на методе RS.Open(). Дело в том, что по умолчанию ADO использует так называемое отложенное выполнение (deferred execution). Метод Open() лишь инициализирует запрос на сервере и подготавливает структуру метаданных. Реальное выполнение тяжелого SQL-запроса и чтение первой порции данных происходят только тогда, когда клиентская программа (в данном случае 1С) впервые обращается к данным — то есть при проверке свойства EOF.
Соответственно, вся задержка вызвана тем, что СУБД Firebird в этот момент тратит гигантские ресурсы на выполнение неоптимального SQL-запроса. Рассмотрим, как исправить эту ситуацию по шагам.
Главная архитектурная ошибка в исходном запросе кроется в условии фильтрации: where orders.sdate + orders.stime >= ....
Даже если поля sdate (дата) и stime (время) в таблице Firebird проиндексированы, любое математическое или строковое сложение полей в секции WHERE полностью отключает использование индексов оптимизатором СУБД. СУБД вынуждена выполнять полное сканирование таблицы (Natural Scan / Full Table Scan), поочередно складывая значения полей для каждой из сотен тысяч строк, чтобы сравнить результат с константой.
Рассмотрим несколько вариантов решения этой проблемы:
Вариант А. Разделение условий на дату и время
Для того чтобы оптимизатор смог использовать индекс по полю даты, необходимо переписать запрос так, чтобы поле sdate участвовало в поиске в чистом виде. Мы можем разделить выборку на две части с помощью оператора UNION ALL или сложного логического условия:
// Выбираем все записи за последующие дни (где время не имеет значения)
// ПЛЮС записи за текущий день, но строго позже указанного времени
RS.Open("select * from orders where orders.sdate > '2018-08-30'
union all
select * from orders where orders.sdate = '2018-08-30' and orders.stime >= '15:00:00'", Conn);
В этом случае оптимизатор мгновенно найдет нужные записи по индексу поля sdate, а затем быстро отфильтрует их.
Вариант Б. Фильтрация только по дате с постобработкой в 1С
Если разделение условий кажется слишком громоздким, можно пойти по упрощенному пути: выбрать из СУБД все данные начиная с начала нужного дня (по индексу), а отсечение лишних часов и минут выполнить уже внутри кода 1С:
RS.Open("select * from orders where orders.sdate >= '2018-08-30'", Conn);
Пока НЕ (RS.EOF) Цикл
// Проверяем время на стороне 1С
Если RS.Fields("stime").Value() < НачВремя Тогда
RS.MoveNext();
Продолжить;
КонецЕсли;
// Полезная обработка данных
Сообщить(RS.Fields(1).Value());
RS.MoveNext();
КонецЦикла;
Вариант В. Использование индексов по выражениям (Expression Indexes)
Если вы используете СУБД Firebird версии 2.0 и выше, вы можете создать специальный вычисляемый индекс непосредственно на сервере базы данных. Выполните в консоли администрирования СУБД следующую команду:
CREATE INDEX idx_orders_datetime ON orders COMPUTED BY (sdate + stime);
Важный нюанс: чтобы оптимизатор Firebird задействовал этот индекс, выражение в секции WHERE вашего SQL-запроса в 1С должно символ в символ совпадать с выражением, указанным при создании индекса. Теперь исходный запрос заработает мгновенно, так как сервер будет использовать вычисляемый индекс idx_orders_datetime.
Параметры открытия Recordset оказывают колоссальное влияние на сетевой трафик и скорость обмена данными между СУБД, драйвером OLE DB и платформой 1С. Посмотрим на сигнатуру метода Open():
Recordset.Open(Source, ActiveConnection, CursorType, LockType, Options)
По умолчанию параметр CursorType равен 0 (adOpenForwardOnly — однонаправленный курсор), а местоположение курсора (свойство CursorLocation объекта Connection или Recordset) равно 2 (adUseServer — серверный курсор).
При работе с большими выборками серверный курсор вынуждает драйвер при каждом вызове MoveNext() отправлять сетевой запрос к серверу для получения следующей строки. Чтобы избежать этого «эффекта бадминтона», переключите курсор на клиентскую сторону. Это заставит ADO за один проход выкачать весь результирующий набор данных в оперативную память вашего компьютера:
RS = Новый COMОбъект ("ADODB.RecordSet");
// Устанавливаем клиентский курсор (3 = adUseClient)
RS.CursorLocation = 3;
// Открываем статический курсор (3 = adOpenStatic) с доступом только для чтения (1 = adLockReadOnly)
RS.Open("select * from orders where ...", Conn, 3, 1);
При использовании клиентского курсора метод Open() может занять чуть больше времени, зато проверка EOF и последующий обход коллекции в цикле будут происходить мгновенно в оперативной памяти клиента.
Использование конструкции SELECT * в реальных интеграционных задачах является грубой ошибкой. Выборка всех полей без разбора приводит к избыточному потреблению оперативной памяти и нагрузке на сеть.
Особенно критично это становится, если в таблице базы данных присутствуют текстовые или двоичные поля большого объема (BLOB). СУБД тратит много времени на их чтение с диска. Посмотрим, как оптимизировать список полей:
Пример правильного запроса:
select id, doc_number, sdate, stime, customer_id, total_sum from orders where ...
Даже если СУБД отработала быстро, построчный обход сотен тысяч записей в цикле 1С через COM-соединение будет медленным. Каждый вызов метода MoveNext() или обращение к свойству Fields().Value — это дорогостоящий вызов через границу программных сред (из платформы 1С в подсистему COM).
Чтобы кардинально ускорить процесс, воспользуемся встроенным методом ADO GetRows(). Данный метод выгружает все строки рекордсета в двумерный массив памяти за одно обращение к COM-объекту. Рассмотрим этот паттерн проектирования:
RS = Новый COMОбъект ("ADODB.RecordSet");
RS.CursorLocation = 3; // Обязательно клиентский курсор
RS.Open("select id, doc_number, total_sum from orders where ...", Conn, 3, 1);
Если НЕ RS.EOF Тогда
// Выгружаем все данные в многомерный массив COMSafeArray
ДанныеТаблицы = RS.GetRows();
RS.Close();
// Определяем границы массива
// 0-е измерение: колонки (поля запроса)
// 1-е измерение: строки таблицы
ВсегоКолонок = ДанныеТаблицы.Dimension(1) - 1;
ВсегоСтрок = ДанныеТаблицы.Dimension(2) - 1;
// Обходим массив средствами встроенного языка 1С (это происходит мгновенно!)
Для ИндексСтроки = 0 По ВсегоСтрок Цикл
Идентификатор = ДанныеТаблицы.GetValue(0, IndexСтроки);
НомерДок = ДанныеТаблицы.GetValue(1, IndexСтроки);
Сумма = ДанныеТаблицы.GetValue(2, IndexСтроки);
// Обработка полученных данных...
КонецЦикла;
Иначе
RS.Close();
КонецЕсли;
Использование метода GetRows() совместно с переносом фильтрации на сторону СУБД и правильными индексами позволяет ускорить загрузку больших пакетов данных из Firebird в 1С в десятки раз.