Развитие и поддержка крупных информационных баз на платформе 1С:Предприятие 7.7 часто упирается в производительность штатных механизмов. Когда объем базы данных начинает исчисляться десятками гигабайт (например, 30 ГБ и более), классические запросы 1С и методы работы с регистрами начинают работать медленно. Выходом из этой ситуации становится изучение и внедрение механизмов прямых запросов к СУБД (MS SQL Server) с использованием внешней компоненты 1С++ и класса ODBCRecordset. В этой статье мы подробно разберем, как устроен этот механизм, когда он дает максимальный прирост скорости, как правильно составлять запросы и типизировать возвращаемые данные.
Штатный генератор запросов 1С 7.7 транслирует код встроенного языка в SQL-запросы крайне неоптимально. Он создает избыточные соединения (JOIN), не умеет правильно использовать индексы СУБД во многих сложных ситуациях и часто выбирает лишние данные. Прямые запросы позволяют разработчику напрямую общаться с MS SQL Server на его родном языке T-SQL, используя всю мощь СУБД: от построения оптимальных планов выполнения до использования временных таблиц и тонкой настройки блокировок.
Перечислим основные преимущества использования прямых запросов:
---
Рассмотрим вопрос, который часто возникает у начинающих разработчиков: будет ли прямой запрос быстрее на простых операциях, например, при выборке элементов справочника? Проанализируем три различных подхода к получению кодов и наименований из справочника Номенклатура.
Вариант 1. Классический запрос 1С 7.7
Этот вариант использует внутренний движок запросов платформы:
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса = "
|КодН = Справочник.Номенклатура.Код;
|НаименН = Справочник.Номенклатура.Наименование;
|Группировка НаименН без групп;
|";
Если Запрос.Выполнить(ТекстЗапроса) = 1 Тогда
Пока Запрос.Группировка(1) = 1 Цикл
// Обработка данных
КонецЦикла;
КонецЕсли;
Вариант 2. Простая объектная выборка через ВыбратьЭлементы()
Для простых задач получения списка элементов справочника без сложных условий штатная выборка часто оказывается эффективнее штатного запроса:
Спр = СоздатьОбъект("Справочник.Номенклатура");
Спр.ВыбратьЭлементы();
Пока Спр.ПолучитьЭлемент() = 1 Цикл
Код = Спр.Код;
Наименование = Спр.Наименование;
КонецЦикла;
Вариант 3. Прямой SQL-запрос через 1С++ (ODBCRecordset)
Теперь напишем прямой запрос для решения этой же задачи:
RS = СоздатьОбъект("ODBCRecordset");
RS.УстБД1С();
ТекстЗапроса = "
|SELECT
| Спр.Code as Код,
| Спр.Descr as Наименование
|FROM
| $Справочник.Номенклатура as Спр";
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
Анализ эффективности:
Для простых выборок («получить все элементы справочника») разница в скорости между прямой объектной выборкой (Вариант 2) и прямым запросом (Вариант 3) на небольших справочниках будет практически незаметна. Более того, штатный метод ВыбратьЭлементы() может отработать быстрее, так как не тратится время на инициализацию внешних объектов и передачу таблицы значений в память 1С. Однако, штатный 1С-запрос (Вариант 1) в данном случае проиграет обоим методам из-за сложной внутренней структуры генерации T-SQL кода платформой.
Прямые запросы раскрывают свой потенциал на 100%, когда в запросе появляются сложные условия, соединения нескольких таблиц, фильтрация по реквизитам и работа с итогами регистров.
---
В физической структуре SQL-базы данных 1С 7.7 таблицы и поля имеют нечитаемые системные имена (например, таблица справочника может называться SC433, а реквизит — SP435). Чтобы разработчику не приходилось вручную сопоставлять эти имена по файлу конфигурации 1Cv7.DDS, в компоненте 1С++ реализован мощный инструмент — метапарсер.
Метапарсер автоматически заменяет конструкции со знаком доллара $ на реальные имена физических таблиц и полей базы данных SQL. Рассмотрим основные правила работы с метапарсером:
$Справочник.ИмяСправочника — заменяется на имя таблицы справочника (например, SC433).$Документ.ИмяДокумента — заменяется на имя таблицы шапки документа (например, DH1025).$Реквизит.ИмяРеквизита (или использование через псевдоним таблицы $Спр.Реквизит) — преобразуется в физическое имя колонки в SQL-таблице (например, SP435).---
При выполнении прямого запроса SQL возвращает «сырые» типы данных: строки, числа и даты. Для того чтобы полученная Таблица Значений содержала полноценные объекты 1С (ссылки на элементы справочников, документы), необходимо выполнить типизацию полей. В 1С++ это делается с помощью конструкции квадратных скобок [...] в описании полей выборки.
Рассмотрим основные сценарии типизации данных:
Для того чтобы колонка идентификатора элемента превратилась в ссылку на справочник в 1С, указываем тип объекта:
ТекстЗапроса = "
|SELECT
| Спр.ID as [Товар $Справочник.Номенклатура]
|FROM
| $Справочник.Номенклатура as Спр";
Если мы выбираем документы конкретного вида из таблицы шапки или табличной части, типизация выполняется аналогично:
ТекстЗапроса = "
|SELECT
| Док.IDDoc as [Документ $Документ.Реализация]
|FROM
| $Документ.Реализация as Док";
В общих таблицах 1С (таких как журнал документов 1CJOURN или таблицы движений регистров) ссылка на документ состоит из двух полей СУБД: уникального идентификатора документа IDDoc (строка из 9 символов) и кода вида документа IDDocDef (числовой идентификатор метаданных). Чтобы 1С++ правильно собрала ссылку на документ, в выборку нужно передать оба эти поля с жестко определенным именованием колонок (для вида документа используется суффикс _вид строчными буквами):
ТекстЗапроса = "
|SELECT
| Журнал.IDDoc as [Док $Документ],
| Журнал.IDDocDef as Док_вид
|FROM
| _1SJOURN as Журнал
|WHERE
| Журнал.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата";
---
Автор темы спрашивал, есть ли в прямых запросах группировки, аналогичные классическим запросам 1С. Да, группировки в прямых запросах реализуются стандартными средствами языка SQL при помощи оператора GROUP BY.
Давайте исправим ошибку в первом запросе автора. В его штатном 1С-запросе была указана группировка по наименованию. В прямом запросе для достижения аналогичного результата необходимо использовать групповую функцию СУБД:
ТекстЗапроса = "
|SELECT
| Спр.Descr as [Наименование],
| COUNT(Спр.ID) as [КоличествоЭлементов]
|FROM
| $Справочник.Номенклатура as Спр
|GROUP BY
| Спр.Descr";
Обратите внимание, что при использовании группировок в SQL все поля, которые присутствуют в списке SELECT (и не являются агрегатными функциями вроде SUM, COUNT, AVG), обязательно должны быть перечислены в секции GROUP BY.
---
Наибольший прирост производительности прямые запросы дают при работе с регистрами. Платформа 1С 7.7 хранит остатки в физических таблицах итогов (с префиксом RG*) на начало каждого месяца. Если вам необходимо получить остатки на текущую Точку Актуальности (ТА), штатные методы платформы вроде ВыгрузитьИтоги() работают достаточно быстро. Но если нужно получить остатки на произвольную дату в прошлом, штатные механизмы начинают последовательно рассчитывать движения, что приводит к сильным задержкам.
Компонента 1С++ решает эту проблему элегантно с помощью механизма виртуальных таблиц, который эмулирует поведение виртуальных таблиц из 1С 8. Метапарсер на лету строит сложный SQL-запрос к таблицам остатков и движений.
Рассмотрим пример получения остатков товаров на произвольную дату:
ТекстЗапроса = "
|SELECT
| Рег.Товар as [Товар $Справочник.Номенклатура],
| Рег.ОстатокТовараОстаток as Остаток
|FROM
| $РегистрОстатки.ОстаткиТоваров(:ВыбДата,,
| Склад = :ВыбСклад,
| (Товар), (ОстатокТовара)) as Рег";
RS.УстПараметр("ВыбДата", РабочаяДата());
RS.УстПараметр("ВыбСклад", ВыбранныйСклад);
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
---
При работе большого количества пользователей в базе данных 1С 7.7 часто возникают взаимные блокировки таблиц. Чтобы отчеты, формируемые пользователями, не блокировали запись новых документов и не вызывали ошибку «Транзакция отменена из-за блокировки», в прямых запросах на чтение крайне рекомендуется использовать хинт СУБД WITH (NOLOCK).
Разберем пример его использования:
ТекстЗапроса = "
|SELECT
| Спр.Code as Код,
| Спр.Descr as Наименование
|FROM
| $Справочник.Номенклатура as Спр WITH (NOLOCK)";
Важно: Указание класса изолированности транзакций NOLOCK позволяет выполнять «грязное чтение» данных. Это означает, что запрос считает даже те данные, транзакция по записи которых в данный момент еще не завершена. Для большинства аналитических отчетов это абсолютно приемлемо и безопасно. Однако использовать WITH (NOLOCK) в модулях проведения документов, где критически важна абсолютная точность остатков в транзакции, категорически запрещено.
---
Часто в отчет или обработку нужно передать список значений (например, отобрать данные только по определенному списку складов или группе номенклатуры). Если использовать конструкцию СУБД IN (:Список), то при большом размере списка СУБД начнет сильно снижать производительность. В 1С++ для этого реализован высокоэффективный механизм создания временных таблиц.
Рассмотрим по шагам, как передать список элементов в прямой запрос:
СписокЗначений в 1С и заполняем его элементами.УложитьСписокОбъектов() класса ODBCRecordset. Этот метод создает временную таблицу в оперативной памяти SQL Server и заполняет ее идентификаторами объектов.INNER JOIN.Разберем пример кода:
СписокТоваров = СоздатьОбъект("СписокЗначений");
// Наполняем список элементами...
RS = СоздатьОбъект("ODBCRecordset");
RS.УстБД1С();
// Укладываем список во временную таблицу #ВремТовары
RS.УложитьСписокОбъектов(СписокТоваров, "#ВремТовары", "Номенклатура");
ТекстЗапроса = "
|SELECT
| Спр.ID as [Товар $Справочник.Номенклатура],
| Спр.Code as Код,
| Спр.Descr as Наименование
|FROM
| $Справочник.Номенклатура as Спр
|INNER JOIN
| #ВремТовары as Фильтр ON Фильтр.Val = Спр.ID";
ТЗ = RS.ВыполнитьИнструкцию(ТекстЗапроса);
---
Многие разработчики предпочитают вести локальную разработку и отладку кода на копиях баз данных в файловом формате (DBF). Напрямую выполнить T-SQL запрос к DBF-файлам через ODBCRecordset невозможно. Чтобы решить эту проблему без развертывания тяжелого MS SQL Server на локальной машине, используется внешняя компонента 1sqlite.dll.
Эта библиотека встраивает в процесс 1С портативный и очень быстрый движок базы данных SQLite. При инициализации она на лету сопоставляет таблицы DBF с таблицами SQLite. Благодаря этому разработчик может писать абсолютно идентичные прямые запросы, отлаживая их в локальной DBF-базе. Код метапарсера 1С++ полностью совместим с синтаксисом 1sqlite.
---
Чтобы понять, насколько эффективно отрабатывает ваш запрос и задействует ли он нужные индексы СУБД, необходимо научиться пользоваться инструментом SQL Server Profiler. Он позволяет в реальном времени перехватывать все запросы, приходящие от платформы 1С к MS SQL Server, и анализировать время их выполнения (Duration), загрузку процессора (CPU) и количество чтений с диска (Reads). Замерив показатели штатного запроса и оптимизированного прямого, вы наглядно увидите разницу в нагрузке на сервер.
Изучение прямых запросов — это качественный скачок в квалификации разработчика 1С 7.7. Начните с замены тяжелых и медленных отчетов, постепенно переходя к оптимизации проведения документов, и ваша система обретет второе дыхание.