Как ускорить работу отчетов 1С с другими базами через COM-соединение после перехода на SQL Server?

← На главную

При переходе информационных баз 1С с файлового режима на клиент-серверный (SQL Server) пользователи часто сталкиваются со значительным падением производительности отчетов, особенно если для взаимодействия между базами использовались COM-соединения. Давайте подробно разберем, почему это происходит, как диагностировать проблему и какие решения существуют для ускорения работы.

Анализируем причину замедления при переходе на SQL Server

Изначально, когда базы функционировали в файловом режиме, все операции, включая запросы и обработку данных, выполнялись локально в рамках одной машины. Это обеспечивало высокую скорость, поскольку не было накладных расходов на сетевое взаимодействие и работу сервера приложений. Однако, после перевода баз на SQL Server, ситуация кардинально меняется:

Появление сервера приложений 1С и СУБД: Теперь данные хранятся на SQL Server, а запросы к ним обрабатывает сервер 1С. Это означает, что данные начинают "путешествовать" по сети между клиентом 1С, сервером 1С и SQL Server. Каждый этап этого пути добавляет задержки.
Особенности COM-соединения: COM-соединение в 1С по своей сути работает как толстый клиент. Все действия, выполняемые через COM-объект (например, V8.Connect()), по сути имитируют работу интерактивного пользователя. Это может приводить к значительному объему сетевого трафика, если мы передаем большие объемы данных или выполняем много мелких операций в цикле. Мы наблюдали, что время подключения через COM к SQL-базам может увеличиваться в 2-3 раза (с 5 до 15 секунд) по сравнению с файловыми базами, а это уже существенная задержка, которая может превышать время выполнения самого запроса.
Нагрузка на сервер: При выполнении длительных отчетов с интенсивным обменом данными через COM, нагрузка на сервер 1С и SQL Server может достигать критических значений (до 98% ЦП), что приводит к зависаниям не только отчета, но и работы других пользователей в базе.

Диагностируем узкие места производительности

Прежде чем приступать к оптимизации, нам необходимо точно определить, где именно происходит замедление. Для этого мы рекомендуем использовать следующие инструменты и подходы:

Замер производительности: Встроенный в 1С замер производительности позволяет точно определить, на каких этапах выполнения отчета теряется время. Мы можем измерить время, затрачиваемое на:
- Подключение через COM-соединение (Connection = V8.Connect(СтрокаПодключения)). Часто именно этот этап занимает до 50% и более общего времени.
- Выполнение запроса на сервере.
- Загрузку результатов запроса в табличные части или временные таблицы.
Используем замеры для сравнения производительности на файловых и клиент-серверных базах, чтобы выявить, какие операции стали выполняться значительно медленнее.
Анализ нагрузки на сервер: Мы должны отслеживать загрузку центрального процессора, потребление оперативной памяти и очередь к дискам на сервере в момент выполнения проблемного отчета. Это поможет нам понять, является ли узким местом аппаратное обеспечение или проблемы с SQL Server.
План выполнения запроса: Если выяснилось, что долго выполняется именно запрос, необходимо проанализировать план его выполнения в SQL Server Management Studio или через технологический журнал 1С. Это позволит нам увидеть, как SQL Server обрабатывает наш запрос, какие индексы использует (или не использует) и где возникают дорогостоящие операции (например, полное сканирование больших таблиц, неоптимальные соединения).
Версия SQL Server: Как выяснилось в ходе обсуждения, версия SQL Server может критически влиять на производительность. Например, SQL Server 2014 показал себя значительно хуже в сравнении с SQL Server 2019 при выполнении сложных запросов, вплоть до полного зависания базы на старых периодах.

Оптимизируем SQL-запросы для 1С

Часто одной из ключевых причин замедления является неоптимальное составление запросов, которые хорошо работали на файловой базе, но становятся "тяжелыми" на SQL Server. Рассмотрим примеры и рекомендации.

Проблема с полным соединением (FULL JOIN) и вложенными запросами

В нашем случае, одним из проблемных мест оказался запрос с полным соединением и объединением, который был представлен следующим образом:


ВЫБРАТЬ
    ЕСТЬNULL(ХозрасчетныйОстатки.Субконто1, ХозрасчетныйОбороты.Субконто1) КАК Контрагент,
    ЕСТЬNULL(ХозрасчетныйОстатки.Субконто1.Родитель.Наименование, ХозрасчетныйОбороты.Субконто1.Родитель.Наименование) КАК Родитель,
    ЕСТЬNULL(ХозрасчетныйОстатки.Субконто1.Наименование, ХозрасчетныйОбороты.Субконто1.Наименование) КАК Наименование,
    ЕСТЬNULL(ХозрасчетныйОстатки.Субконто1.ИНН, ХозрасчетныйОбороты.Субконто1.ИНН) КАК ИНН,
    ЕСТЬNULL(ХозрасчетныйОстатки.СуммаОстатокДт, 0) КАК НамДолжны,
    ЕСТЬNULL(ХозрасчетныйОстатки.СуммаОстатокКт, 0) КАК МыДолжны,
    ЕСТЬNULL(ХозрасчетныйОбороты.СуммаОборотДт, 0) КАК Оплата,
    0 КАК Сальдо_1007
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(&НаДату, Счет В ИЕРАРХИИ (&СписокСчетов), , ) КАК ХозрасчетныйОстатки
        ПОЛНОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Обороты(&ОплатаС, &ОплатаПо, , Счет В ИЕРАРХИИ (&СписокСчетов), , , КорСчет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетныеСчета), ) КАК ХозрасчетныйОбороты
        ПО ХозрасчетныйОстатки.Субконто1 = ХозрасчетныйОбороты.Субконто1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Хозрасчетный_1007.Субконто1,
    Хозрасчетный_1007.Субконто1.Родитель,
    Хозрасчетный_1007.Субконто1.Наименование,
    Хозрасчетный_1007.Субконто1.ИНН,
    0,
    0,
    0,
    ЕСТЬNULL(Хозрасчетный_1007.СуммаОстаток, 0)
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(&НаДату, Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.МатериалыПереданныеВПереработку), , ) КАК Хозрасчетный_1007

SQL Server, особенно старых версий, часто неоптимально обрабатывает полные соединения (FULL JOIN) и сложные вложенные запросы (SELECT FROM SELECT). Планировщик может выбирать неэффективные планы, что приводит к многократному чтению данных и зависаниям.

Использование временных таблиц

Один из самых эффективных способов оптимизации сложных запросов — это разбиение их на более простые части с использованием временных таблиц. Это позволяет SQL Server строить более эффективные планы выполнения и, что важно, индексировать временные таблицы по соединяемым полям.

Рассмотрим, как мы можем переписать проблемный запрос с использованием временных таблиц:

Получаем Остатки во временную таблицу:


// Временная таблица для остатков
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
    |    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    |    ХозрасчетныйОстатки.Субконто1.Родитель.Наименование КАК Родитель,
    |    ХозрасчетныйОстатки.Субконто1.Наименование КАК Наименование,
    |    ХозрасчетныйОстатки.Субконто1.ИНН КАК ИНН,
    |    ЕСТЬNULL(ХозрасчетныйОстатки.СуммаОстатокДт, 0) КАК НамДолжны,
    |    ЕСТЬNULL(ХозрасчетныйОстатки.СуммаОстатокКт, 0) КАК МыДолжны
    |ПОМЕСТИТЬ ВТ_Остатки
    |ИЗ
    |    РегистрБухгалтерии.Хозрасчетный.Остатки(&НаДату, Счет В ИЕРАРХИИ (&СписокСчетов), , ) КАК ХозрасчетныйОстатки";

Запрос.УстановитьПараметр("НаДату", Граница);
Запрос.УстановитьПараметр("СписокСчетов", СписокСчетов);
Запрос.Выполнить();

Получаем Обороты во временную таблицу:


// Временная таблица для оборотов
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
    |    ХозрасчетныйОбороты.Субконто1 КАК Контрагент,
    |    ЕСТЬNULL(ХозрасчетныйОбороты.СуммаОборотДт, 0) КАК Оплата
    |ПОМЕСТИТЬ ВТ_Обороты
    |ИЗ
    |    РегистрБухгалтерии.Хозрасчетный.Обороты(&ОплатаС, &ОплатаПо, , Счет В ИЕРАРХИИ (&СписокСчетов), , , КорСчет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетныеСчета), ) КАК ХозрасчетныйОбороты";

Запрос.УстановитьПараметр("ОплатаС", Отчет.ОплатаС);
Запрос.УстановитьПараметр("ОплатаПо", КонецДня(Отчет.ОплатаПо));
Запрос.УстановитьПараметр("СписокСчетов", СписокСчетов);
Запрос.Выполнить();

Получаем Остатки по счету 10.07 во временную таблицу:


// Временная таблица для счета 10.07
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
    |    Хозрасчетный_1007.Субконто1 КАК Контрагент,
    |    ЕСТЬNULL(Хозрасчетный_1007.СуммаОстаток, 0) КАК Сальдо_1007
    |ПОМЕСТИТЬ ВТ_1007
    |ИЗ
    |    РегистрБухгалтерии.Хозрасчетный.Остатки(&НаДату, Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.МатериалыПереданныеВПереработку), , ) КАК Хозрасчетный_1007";

Запрос.УстановитьПараметр("НаДату", Граница);
Запрос.Выполнить();

Объединяем данные из временных таблиц:


// Финальный запрос, объединяющий данные
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
    |    ЕСТЬNULL(ВТ_Остатки.Контрагент, ВТ_Обороты.Контрагент) КАК Контрагент,
    |    ЕСТЬNULL(ВТ_Остатки.Родитель, "") КАК Родитель,
    |    ЕСТЬNULL(ВТ_Остатки.Наименование, "") КАК Наименование,
    |    ЕСТЬNULL(ВТ_Остатки.ИНН, "") КАК ИНН,
    |    ЕСТЬNULL(ВТ_Остатки.НамДолжны, 0) КАК НамДолжны,
    |    ЕСТЬNULL(ВТ_Остатки.МыДолжны, 0) КАК МыДолжны,
    |    ЕСТЬNULL(ВТ_Обороты.Оплата, 0) КАК Оплата,
    |    ЕСТЬNULL(ВТ_1007.Сальдо_1007, 0) КАК Сальдо_1007
    |ИЗ
    |    ВТ_Остатки КАК ВТ_Остатки
    |        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Обороты КАК ВТ_Обороты
    |        ПО ВТ_Остатки.Контрагент = ВТ_Обороты.Контрагент
    |        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_1007 КАК ВТ_1007
    |        ПО ЕСТЬNULL(ВТ_Остатки.Контрагент, ВТ_Обороты.Контрагент) = ВТ_1007.Контрагент";

РезультатЗапроса = Запрос.Выполнить();

Такой подход позволил значительно снизить время выполнения запроса на сервере, превратив зависания в приемлемые 25 секунд на проблемном сервере и 4 секунды на тестовом.

Обслуживание SQL Server

Регулярное обслуживание SQL Server критически важно для поддержания производительности. Мы должны убедиться, что выполняются следующие операции:

Обновление статистики: Статистика помогает планировщику запросов SQL Server выбирать оптимальные планы. Если статистика устарела, SQL Server может принимать неверные решения, что приводит к медленным запросам.
Реиндексация/реорганизация индексов: Фрагментированные индексы замедляют доступ к данным. Регулярная реиндексация или реорганизация поддерживает индексы в оптимальном состоянии.

Альтернативы COM-соединению

Хотя оптимизация запросов и SQL Server может значительно помочь, COM-соединение само по себе может быть узким местом из-за своей природы. Рассмотрим более современные и эффективные альтернативы:

Web-сервисы и HTTP-сервисы

Эти механизмы являются предпочтительными для межпрограммного взаимодействия в распределенных системах 1С. Они основаны на протоколе HTTP(S) и позволяют:

Выполнять операции на стороне сервера: Вместо передачи больших объемов данных на клиент через COM, мы можем передать текст запроса и параметры на удаленный сервер через HTTP-сервис. Выполнение запроса и вся тяжелая обработка происходят на удаленном сервере 1С, а обратно на клиент передается уже готовый, возможно, агрегированный результат.
Минимизировать сетевой трафик: За счет серверной обработки, по сети передаются только данные, необходимые для инициирования операции и получения итогового результата, а не все промежуточные данные.
Гибкость развертывания: HTTP-сервисы можно встраивать непосредственно в конфигурацию или развертывать в расширениях, что позволяет получать данные без прямого изменения основной конфигурации.

Для реализации HTTP-сервиса нам потребуется создать новый HTTP-сервис в конфигурации или расширении, добавить в него метод, который будет принимать параметры (например, текст запроса и его параметры) и возвращать результат в виде сериализованной таблицы значений.

Пример использования HTTP-сервиса:


// На стороне удаленной базы (в HTTP-сервисе)
Функция ВыполнитьЗапросОтчета(ПараметрыЗапроса) Экспорт
    // Десериализуем полученные параметры (текст запроса, параметры запроса)
    ТекстЗапроса = ПараметрыЗапроса.ТекстЗапроса;
    Параметры = ПараметрыЗапроса.Параметры;

    Запрос = Новый Запрос(ТекстЗапроса);
    Для Каждого Параметр Из Параметры Цикл
        Запрос.УстановитьПараметр(Параметр.Ключ, Параметр.Значение);
    КонецЦикла;

    Результат = Запрос.Выполнить().Выгрузить();
    Возврат СериализаторXDTO.ЗаписатьXML(Результат); // Возвращаем сериализованный результат
КонецФункции

// На стороне основной базы (клиент)
Процедура ПолучитьДанныеЧерезHTTP()
    // Создаем структуру для передачи параметров
    ПараметрыЗапроса = Новый Структура;
    ПараметрыЗапроса.Вставить("ТекстЗапроса", "ВЫБРАТЬ ..."); // Ваш SQL-запрос
    ПараметрыЗапроса.Вставить("Параметры", Новый Соответствие); // Параметры запроса

    // Сериализуем параметры
    СериализованныеПараметры = СериализаторXDTO.ЗаписатьXML(ПараметрыЗапроса);

    // Отправляем HTTP-запрос
    HTTPЗапрос = Новый HTTPЗапрос("/e1c/hs/MyService/ExecuteQuery");
    HTTPЗапрос.УстановитьТелоИзСтроки(СериализованныеПараметры, КодировкаТекста.UTF8, "application/xml");

    HTTPСоединение = Новый HTTPСоединение("localhost", 80, , , , 100); // Настройки соединения
    Ответ = HTTPСоединение.Отправить(HTTPЗапрос);

    Если Ответ.Код = 200 Тогда
        СтрокаРезультата = Ответ.ПолучитьТелоКакСтроку();
        Результат = СериализаторXDTO.ПрочитатьXML(СтрокаРезультата);
        // Обрабатываем полученную таблицу значений
    Иначе
        Сообщить("Ошибка HTTP-запроса: " + Ответ.Код + " " + Ответ.ПолучитьТелоКакСтроку());
    КонецЕсли;
КонецПроцедуры

ODATA (Open Data Protocol)

ODATA — это протокол для построения и использования RESTful API. 1С поддерживает публикацию стандартного интерфейса ODATA, который позволяет получать данные из справочников, документов и других объектов метаданных без существенной доработки конфигурации, при условии, что эти объекты были опубликованы для ODATA. Это может быть очень быстрым решением для получения простых данных, но для сложных, агрегированных отчетов может потребовать дополнительной настройки или комбинирования с другими методами.

Для публикации объектов через ODATA, мы можем использовать метод УстановитьСоставСтандартногоИнтерфейсаOData(), добавляя в массив нужные объекты метаданных:


// Пример на стороне удаленной базы (в общем модуле или регламентном задании)
Массив = Новый Массив;
Массив.Добавить(Метаданные.Справочники.Контрагенты);
Массив.Добавить(Метаданные.Справочники.Номенклатура);
Массив.Добавить(Метаданные.Справочники.ВидыНоменклатуры); // Добавляем нужный справочник
УстановитьСоставСтандартногоИнтерфейсаOData(Массив);

После публикации мы можем обращаться к данным по URL вида http://localhost/intertopKAmarat/odata/standard.odata/Catalog_ВидыНоменклатуры.

Централизованная база-агрегатор

Если актуальность данных в отчете не требуется в режиме "онлайн" (допустима задержка в несколько минут или часов), мы можем рассмотреть создание отдельной базы-агрегатора. Механизм работы такой базы может быть следующим:

Создаем 5-ю базу, которая будет собирать информацию со всех 4-х других баз.
В каждой из 4-х баз настраиваем регламентное задание или план обмена, который по расписанию выгружает необходимые данные (например, срезы остатков и оборотов) и передает их в базу-агрегатор.
База-агрегатор хранит эти данные в своих регистрах или таблицах.
Отчеты, которые требовали данных из нескольких баз, теперь строятся исключительно на данных из базы-агрегатора, что значительно ускоряет их формирование, поскольку все данные находятся локально.
Для получения данных из базы-агрегатора можно также использовать HTTP-сервисы, передавая параметры отчета и получая готовую таблицу значений.

Сериализация/десериализация таблиц значений через COM

Если мы все же вынуждены использовать COM-соединение, то для повышения скорости передачи больших объемов данных мы можем сериализовать таблицы значений в строку (например, XML или JSON) на удаленной стороне, передать эту строку через COM, а затем десериализовать на основной стороне. Это делает передачу атомарной – по сети передается одна строка, а не множество объектов, что значительно быстрее.

Влияние версии SQL Server и аппаратного обеспечения сервера

В ходе нашего анализа стало очевидным, что версия SQL Server и характеристики аппаратного обеспечения сервера играют критическую роль в производительности 1С.

Версия SQL Server: Мы столкнулись с ситуацией, когда на домашнем компьютере с SQL Server 2019 отчеты "летали", а на рабочем сервере с SQL Server 2014 те же отчеты полностью зависали на старых периодах. Исследования и тесты подтверждают, что SQL Server 2014 может показывать значительно худшую производительность по сравнению с более новыми версиями (2016, 2019), с разницей в скорости выполнения запросов до сотен раз. Поэтому, если на сервере используется SQL Server 2014 или более старая версия, одним из самых эффективных решений может стать обновление SQL Server до версии 2019 или новее.
Производительность CPU: Для 1С и SQL Server очень важна производительность одного ядра (Single Thread Rating) процессора, а не только общее количество ядер. Старые процессоры Intel Xeon E5-2630 v2, несмотря на большое количество ядер и памяти (220 Гб), могут значительно уступать современным десктопным процессорам типа AMD Ryzen 5 3500 по производительности на одно ядро. Это объясняет, почему ваш домашний компьютер мог показывать лучшую производительность.
Дисковая подсистема: Использование SSD-дисков для хранения баз данных и логов SQL Server является обязательным условием для обеспечения высокой производительности 1С.

Мы настоятельно рекомендуем проводить комплексные тесты производительности сервера (CPU-Z, CrystalDiskMark) для оценки его реальной пригодности для работы 1С в клиент-серверном режиме.

Заключение

Ускорение работы с базами 1С через COM-соединение после перехода на SQL Server — это комплексная задача, требующая анализа и оптимизации на нескольких уровнях. Мы убедились, что проблему можно решить, применяя следующие шаги:

Оптимизация запросов: Переработка сложных запросов с полными соединениями и вложенными SELECT'ами с использованием временных таблиц.
Переход на современные механизмы взаимодействия: Замена COM-соединений на HTTP-сервисы или Web-сервисы для обеспечения более эффективной и быстрой передачи данных и выполнения операций на стороне сервера.
Обновление и обслуживание SQL Server: Крайне важен переход на более новую версию SQL Server (например, 2019) и регулярное выполнение задач по обновлению статистики и реиндексации.
Анализ аппаратного обеспечения: Убедитесь, что сервер соответствует современным требованиям по производительности CPU (особенно по одноядерной) и имеет быструю дисковую подсистему (SSD).

Применение этих рекомендаций поможет нам значительно ускорить работу отчетов и обеспечить стабильную и комфортную работу пользователей в клиент-серверных базах 1С.