В практике разработки на платформе 1С часто возникают ситуации, когда стандартных возможностей встроенного языка запросов недостаточно для решения задач производительности. В таких случаях программисты прибегают к прямым SQL-запросам (Direct SQL). Одной из самых сложных задач при этом является корректная трансляция обращения к виртуальным таблицам, таким как Остатки, в чистый T-SQL. Рассмотрим подробно, как устроена эта магия внутри СУБД и как правильно составить аналогичный запрос вручную.
Прежде чем писать код, проанализируем, как 1С хранит данные регистров накопления в SQL Server. Для каждого регистра накопления платформа создает как минимум две физические таблицы:
_AccumRg<n>) — содержит все записи приходов и расходов._AccumRgT<n>) — хранит рассчитанные остатки на начало каждого месяца.Когда мы вызываем виртуальную таблицу Остатки на определенную дату, SQL-запрос, генерируемый платформой, выполняет сложную операцию: он берет остатки из таблицы итогов на ближайшую предыдущую точку (например, на начало текущего месяца) и корректирует их на сумму оборотов из таблицы движений за период от даты итогов до целевой даты запроса. Если мы хотим получить остатки на текущий момент (актуальные итоги), платформа обращается напрямую к таблице итогов, где записи помечены специальным периодом (обычно это '3999-11-01 00:00:00' для версии 8.x или '3999-01-01' для 7.7).
Если вы работаете в среде, поддерживающей внешние компоненты (например, 1С 7.7 с компонентой 1С++), задача упрощается благодаря метасимволам, которые автоматически транслируют синтаксис 1С в физические имена таблиц SQL. Рассмотрим пример запроса с использованием ODBCRecordSet.
Разберем по шагам создание такого запроса:
Запрос = СоздатьОбъект("ODBCRecordSet");
Запрос.УстановитьТекстовыйПараметр("ДатаОстатка", ДатаОстатка);
Запрос.УстановитьТекстовыйПараметр("Склад", Склад);
ТекстЗапроса = "
|SELECT
| рег.Номенклатура [Номенклатура $Справочник.Номенклатура],
| рег.КоличествоОстаток Остаток
|FROM
| $РегистрОстатки.ОстаткиНоменклатуры(:ДатаОстатка,, Склад = :Склад, (Номенклатура), (Количество)) рег
|";
В этом примере конструкция $РегистрОстатки... является виртуальным расширением, которое компонента превратит в полноценный SQL-код с объединением таблиц итогов и движений. Однако, если задача стоит в написании чистого T-SQL без использования посредников, нам нужно понимать физическую структуру.
Для платформы 1С 8.3 прямой запрос будет выглядеть гораздо массивнее. Рассмотрим ситуацию, когда нам нужно получить остатки, не используя встроенный механизм 1С. Мы должны самостоятельно объединить данные из таблицы итогов и таблицы движений. Проанализируем примерную структуру такого запроса:
SELECT
T1.Fld38239RRef AS Номенклатура_ID,
SUM(T1.Balance) AS Остаток
FROM (
-- 1. Берем остатки из таблицы итогов на начало месяца
SELECT _Fld38239RRef, _Fld38245 AS Balance
FROM _AccumRgT38248
WHERE _Period = '2023-10-01 00:00:00' AND _Fld38242RRef = @СкладID
UNION ALL
-- 2. Добавляем движения (Приход '+', Расход '-') с начала месяца до нужной даты
SELECT _Fld38239RRef,
CASE WHEN _RecordKind = 0 THEN _Fld38245 ELSE -_Fld38245 END
FROM _AccumRg38238
WHERE _Period >= '2023-10-01 00:00:00' AND _Period < @ДатаОстатка
) T1
GROUP BY T1.Fld38239RRef
HAVING SUM(T1.Balance) <> 0
Обратите внимание на важные моменты при работе с физическими таблицами:
01.01.2023 в SQL может выглядеть как 01.01.4023. При написании прямых запросов это нужно учитывать в условиях WHERE.Номенклатура) хранятся в формате binary(16). Для фильтрации по конкретной ссылке нужно передавать ее шестнадцатеричное представление (GUID).WITH (NOLOCK), чтобы не блокировать работу пользователей в 1С при выполнении тяжелых отчетов.Если вам требуется использовать полученные остатки для последующих соединений с другими данными, лучше всего поместить их во временную таблицу. Это позволит SQL-серверу оптимизировать план выполнения запроса. Выясним, как корректно создать и наполнить временную таблицу в рамках одного сеанса.
-- Отключаем вывод количества строк для ускорения
SET NOCOUNT ON;
-- Создаем временную таблицу
CREATE TABLE #TempInventory (
ItemID binary(16),
Quantity numeric(19, 3)
);
-- Наполняем данными
INSERT INTO #TempInventory (ItemID, Quantity)
SELECT
рег.Номенклатура,
рег.КоличествоОстаток
FROM
$РегистрОстатки.ОстаткиНоменклатуры(:ДатаОстатка,, Склад = :Склад, (Номенклатура), (Количество)) рег;
-- Создаем индекс для ускорения последующих Join
CREATE INDEX IX_TempInv_Item ON #TempInventory (ItemID);
-- Используем временную таблицу в основном запросе
SELECT
inv.ItemID [Номенклатура $Справочник.Номенклатура],
inv.Quantity AS Остаток
FROM
#TempInventory AS inv
WHERE
inv.Quantity > 0;
-- Не забываем удалять таблицу, если работаем в длительной сессии
DROP TABLE #TempInventory;
В современных версиях MS SQL Server для повышения читаемости кода часто используют обобщенные табличные выражения (CTE). Рассмотрим, как это может выглядеть. CTE позволяет определить временный результирующий набор данных, который доступен в рамках выполнения одного запроса SELECT, INSERT, UPDATE или DELETE.
WITH CurrentBalances AS (
SELECT
_Fld38239RRef as ItemID,
SUM(_Fld38245) as Qty
FROM _AccumRgT38248
WHERE _Period = '3999-11-01 00:00:00' -- актуальные итоги
GROUP BY _Fld38239RRef
)
SELECT
Items._Description,
Balances.Qty
FROM CurrentBalances Balances
JOIN _Reference50 Items ON Balances.ItemID = Items._IDRRef
WHERE Balances.Qty > 100;
Преимущество CTE заключается в том, что оно не создает физическую нагрузку на tempdb (в отличие от временных таблиц), если данные используются однократно. Однако для сложных расчетов с многократным использованием одних и тех же промежуточных данных временные таблицы с индексами все равно остаются более производительным решением.
Подведем итоги нашего анализа. Преобразование запроса 1С в прямой SQL требует глубокого понимания структуры базы данных. Основные шаги включают:
_AccumRgT).#temp таблицы, CTE и NOLOCK.Помните, что прямые запросы к базе данных 1С — это мощный инструмент, который требует осторожности. Ошибки в таких запросах могут привести к получению недостоверных данных или снижению стабильности работы всей системы.