Как выполнять прямые запросы к базам 1С 7.7 на MS SQL и DBF?

Штатный механизм запросов в платформе 1С:Предприятие 7.7 при работе с большими объемами данных часто работает медленно, особенно при расчете сложных отчетов по регистрам или выборке движений документов. Для ускорения обработки информации разработчики используют технологию прямых запросов к базе данных в обход встроенного движка платформы. Рассмотрим подробнее, какие компоненты и технологии подходят для реализации прямых запросов на MS SQL и DBF, а также разберем различия между ними.

Основной инструмент для MS SQL: внешняя компонента 1С++ и класс ODBCRecordSet

Для серверных баз на MS SQL стандартом де-факто является бесплатная внешняя компонента 1С++ и ее ключевой класс ODBCRecordSet. Попытки использовать штатные объекты Windows вроде ADODB.Command на практике крайне неудобны, так как они не содержат механизма трансляции метаданных 1С.

Разберем основные преимущества использования ODBCRecordSet:

1. Встроенный метапарсер: позволяет писать в тексте запроса привычные идентификаторы конфигурации (например, $Регистр.ОстаткиТоваров, $Справочник.Номенклатура) и виртуальные таблицы. Парсер автоматически заменяет их на реальные имена физических таблиц и колонок базы SQL.
2. Виртуальные таблицы остатков и оборотов: макросы $РегистрОстатки, $РегистрОбороты и $РегистрОстаткиИОбороты сами генерируют оптимальный T-SQL код, соединяющий таблицы итогов и движений с корректным учетом точки актуальности (ТА).
3. Автоматическая типизация: макросы типизации (например, $ВидСправочника.Номенклатура) избавляют разработчика от необходимости вручную декодировать и кодировать ссылки формата Base36.
4. Прямая выгрузка результатов: данные из запроса можно выгрузить напрямую в объект ТаблицаЗначений или ИндексированнаяТаблица без медленного построчного обхода выборки.

Посмотрим на типовой пример реализации прямого запроса через класс ODBCRecordSet компоненты 1С++:

ЗапросSQL = СоздатьОбъект("ODBCRecordSet");
ТекстЗапроса = "
|SELECT 
|    Рег.$Номенклатура AS [Номенклатура $Справочник.Номенклатура],
|    Рег.$КоличествоОстаток AS [Количество]
|FROM 
|    $РегистрОстатки.ОстаткиТоваров(:ВыбДата~,,
|                                   Номенклатура = :ВыбНоменклатура,
|                                   (Номенклатура), (Количество)) AS Рег
|";

ЗапросSQL.УстановитьТекстовыйПараметр("ВыбДата", ТекущаяДата());
ЗапросSQL.УстановитьТекстовыйПараметр("ВыбНоменклатура", ВыбТовар);

ТабРезультат = СоздатьОбъект("ТаблицаЗначений");
Если ЗапросSQL.ВыполнитьИнструкцию(ТекстЗапроса, ТабРезультат) = 0 Тогда
    Предупреждение("Ошибка выполнения прямого SQL-запроса!");
КонецЕсли;

Почему не OLEDB, а именно ODBC?

В компоненте 1С++ реализована поддержка как ODBC, так и OLEDB. Однако на практике рекомендуется использовать именно ODBC по следующим причинам:

1. Реализация OLEDB в 1С++ носила экспериментальный характер и имеет ограниченную поддержку макросов метапарсера.
2. Класс ODBCRecordSet максимально оптимизирован под работу с Microsoft SQL Server и стабильно функционирует на всех версиях платформы от SQL Server 2000 до современных версий.

Особенности структуры хранения регистров в MS SQL

Проанализируем, как физически хранятся регистры в базе данных 1С 7.7 на MS SQL Server:

1. Таблица итогов (RG<ID>): хранит остатки на начало каждого месяца (расчетного периода) и на момент точки актуальности (ТА).
2. Таблица движений (RA<ID>): содержит записи каждого проведенного документа с реквизитами, ресурсами и служебными полями.
3. Поле DATE_TIME_IDDOC: составное служебное поле, включающее дату (8 символов), время со смещением (6 символов) и внутренний ID документа (9 символов).
4. Ссылки Base36: идентификаторы объектов метаданных хранятся в виде строк в тридцатишестеричной системе счисления.

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

Прямые запросы к файловым базам (DBF) через 1sqlite

Если разработка или тестирование ведется на файловой копии (DBF), инструмент ODBCRecordSet не подойдет в полной мере. Для файловых баз лучшим решением является внешняя компонента 1sqlite.

Особенности работы с 1sqlite:

1. Компонента поднимает движок SQLite в адресном пространстве 1С и подключает файлы базы данных .dbf как виртуальные таблицы.
2. Используется стандартный SQL-диалект SQLite, а не T-SQL.
3. В 1sqlite отсутствуют виртуальные таблицы остатков платформенного уровня SQL (такие как $РегистрОстатки). Расчет остатков на произвольную дату требует ручного объединения таблиц итогов и движений через конструкции UNION ALL или предварительной подготовки срезов во временных таблицах в оперативной памяти (:memory:).

Почему нельзя тестировать запросы SQL на файловой базе DBF?

Попытка писать код в файловой базе с расчетом на его перенос в MS SQL приводит к ряду проблем:

1. Разница в структуре полей и индексов: в DBF-версии формат хранения даты, времени и ссылочных типов отличается от MS SQL.
2. Разный диалект SQL: синтаксис T-SQL (MS SQL Server) и SQLite (1sqlite) несовместим для сложных конструкций, функций работы со строками, датами и временными таблицами.
3. Поведение монопольного режима: при открытии DBF-файлов драйвером ODBC в монопольном режиме часто возникают ошибки разделяемого доступа, чего не происходит на сервере SQL.
4. Разница в производительности: алгоритм, показавший высокую скорость на локальных DBF-файлах, может вызывать полное сканирование таблиц (Table Scan) на SQL-сервере из-за неоптимального использования индексов оптимизатором SQL.

Вывод и рекомендация: если рабочая информационная база работает под управлением MS SQL Server, для разработки и тестирования необходимо развернуть локальный экземпляр MS SQL Server (например, бесплатную редакцию Express или Developer) и использовать связку 1С++ + ODBCRecordSet.