Как в прямом запросе 1С 7.7 получить значение и дату последней записи периодического реквизита

← На главную

При работе со справочниками в платформе 1С:Предприятие 7.7 часто возникает необходимость получать историю изменений периодических реквизитов. Классический пример — получение актуальных цен номенклатуры на определенную дату. Если нам требуется только сама цена, мы можем легко воспользоваться стандартной конструкцией мета-парсера 1С++ $ПоследнееЗначение. Однако эта функция возвращает исключительно само значение реквизита. Что делать, если в одном отчете нам необходимо вывести одновременно и саму цену, и точную дату её установки?

В этой статье мы подробно разберем, как устроен механизм хранения периодических данных в СУБД MS SQL Server для платформы 1С 7.7, проанализируем структуру таблиц, рассмотрим базовое работающее решение и предложим несколько продвинутых вариантов оптимизации производительности запроса.

Архитектура хранения периодических реквизитов в 1С 7.7

Для начала выясним причину, почему стандартные средства могут не подходить для сложных отчетов, и разберем структуру базы данных. Все периодические значения констант и реквизитов справочников в системе 1С 7.7 хранятся в единой системной таблице с именем _1SConst. Понимание структуры этой таблицы — ключ к написанию эффективных прямых запросов.

Рассмотрим основные поля таблицы _1SConst, которые задействуются при выборке данных:

ID — внутренний числовой идентификатор реквизита справочника или константы. Каждому периодическому реквизиту в словаре данных 1С (файл 1Cv7.DD) присваивается уникальный номер.
OBJID — идентификатор объекта-владельца. Для периодических реквизитов справочника здесь хранится 9-символьный внутренний код элемента справочника (поле ID основной таблицы справочника).
DATE — дата, на которую установлено значение (хранится в формате даты SQL).
TIME — время установки значения. В 1С 7.7 время записывается в виде специальной строковой 36-ричной последовательности (длиной 3 символа), что позволяет СУБД корректно сортировать записи в пределах одной секунды.
DOCID — идентификатор документа, который зафиксировал это изменение (если значение устанавливалось документом).
ROW_ID — уникальный автоинкрементный номер строки (Primary Key), гарантирующий уникальность и определяющий физический порядок добавления записей, если они внесены на одну дату и время.
VALUE — собственно значение реквизита, сохраненное в строковом виде (для простых типов данных) или во внутреннем представлении 1С.

Для корректного получения самого последнего значения периодического реквизита СУБД должна сортировать данные в строго определенном порядке. Сортировка по убыванию должна выглядеть следующим образом: ORDER BY date DESC, time DESC, docid DESC, row_id DESC. Именно такая последовательность гарантирует выборку актуальной записи.

Базовый вариант решения задачи (подзапросы)

Рассмотрим по шагам первый рабочий вариант, к которому пришли разработчики в процессе обсуждения проблемы. Этот подход основывается на использовании коррелирующих подзапросов (Subqueries) в списке выборки SELECT. Для каждого элемента справочника цен мы пишем отдельные подзапросы к таблице _1SConst, чтобы вытащить отдельно цену, дату и время.

Посмотрим на пример такого запроса:


SELECT 
    Цены.PARENTEXT [Владелец $Справочник.Номенклатура],
    (
        SELECT TOP 1
            CAST(c_val.value AS numeric(14, 4))
        FROM
            _1sconst AS c_val (nolock)
        WHERE
            c_val.id = 324 AND
            c_val.objid = Цены.ID AND
            c_val.date <= '20200206'
        ORDER BY c_val.date DESC, c_val.time DESC, c_val.docid DESC, c_val.row_id DESC
    ) AS Цена,
    (
        SELECT TOP 1
            CAST(c_date.date AS datetime)
        FROM
            _1sconst AS c_date (nolock)
        WHERE
            c_date.id = 324 AND
            c_date.objid = Цены.ID AND
            c_date.date <= '20200206'
        ORDER BY c_date.date DESC, c_date.time DESC, c_date.docid DESC, c_date.row_id DESC
    ) AS ДатаЦены,
    (
        SELECT TOP 1
            c_time.time
        FROM
            _1sconst AS c_time (nolock)
        WHERE
            c_time.id = 324 AND
            c_time.objid = Цены.ID AND
            c_time.date <= '20200206'
        ORDER BY c_time.date DESC, c_time.time DESC, c_time.docid DESC, c_time.row_id DESC
    ) AS ВремяЦены
FROM $Справочник.Цены AS Цены WITH (NOLOCK)
WHERE Цены.PARENTEXT = :ВыбНоменклатура

Разберем, как работает этот код. Мы обращаемся к справочнику цен и для каждого найденного элемента выполняем три независимых обращения к таблице _1SConst. Мы ищем строки, где внутренний идентификатор реквизита равен 324 (в данной базе данных под этим номером хранится реквизит "Цена"), а поле objid соответствует идентификатору текущего элемента справочника цен. Мы ограничиваем поиск выбранной датой ('20200206') и сортируем по убыванию, чтобы забрать самую верхнюю строчку через TOP 1.

В чем минусы базового решения и как его улучшить

Хотя представленный выше запрос полностью решает задачу и возвращает корректные данные, он имеет существенные недостатки, которые негативно скажутся на производительности в реальной рабочей базе:

Тройное обращение к одной таблице. Для получения трех полей одной и той же физической записи СУБД вынуждена трижды сканировать индекс таблицы _1SConst. На больших справочниках номенклатуры это приведет к колоссальным потерям времени.
Жестко прописанный идентификатор реквизита (хардкод). Идентификатор 324 уникален только для конкретной копии базы данных. При обновлении конфигурации, объединении метаданных или переносе кода в другую базу (например, из тестовой в рабочую) этот внутренний ID реквизита "Цена" в словаре 1Cv7.DD изменится, и запрос перестанет работать или начнет выбирать неверные данные.

Выясним, как избавиться от этих недостатков и сделать запрос надежным и быстрым.

Шаг 1. Избавляемся от хардкода через мета-парсер 1С++

Вместо жесткого прописывания числового значения 324 мы должны использовать конструкцию мета-парсера 1С++, которая автоматически подставит правильный ID реквизита из структуры метаданных прямо во время выполнения запроса. Для этого служит директива $ИсторияРеквизита.

Конструкцию c_val.id = 324 заменим на динамическую типизацию:

c_val.id = $ИсторияРеквизита.Цены.Цена

Это гарантирует переносимость кода между любыми базами данных с аналогичной конфигурацией.

Шаг 2. Оптимизация производительности с помощью OUTER APPLY

Если ваша база данных работает на СУБД MS SQL Server 2005 или новее, мы можем применить конструкцию OUTER APPLY. Этот оператор позволяет сопоставить каждой строке основного запроса результат выполнения подзапроса. При этом подзапрос может использовать поля внешней таблицы.

Главное преимущество OUTER APPLY в нашем случае заключается в том, что мы обращаемся к таблице _1SConst всего один раз для каждой строки номенклатуры, выбирая сразу все необходимые поля (Значение, Дату и Время). Это значительно снижает нагрузку на дисковую подсистему и процессор сервера.

Проанализируем оптимизированный вариант запроса с использованием OUTER APPLY:


SELECT 
    Цены.PARENTEXT [Владелец $Справочник.Номенклатура],
    LastPrice.Цена,
    LastPrice.Дата,
    LastPrice.Время
FROM $Справочник.Цены AS Цены WITH (NOLOCK)
OUTER APPLY (
    SELECT TOP 1
        CAST(c_vv.value AS numeric(14, 4)) AS Цена,
        CAST(c_vv.date AS datetime) AS Дата,
        c_vv.time AS Время
    FROM _1sconst AS c_vv (NOLOCK)
    WHERE c_vv.id = $ИсторияРеквизита.Цены.Цена
      AND c_vv.objid = Цены.ID
      AND c_vv.date <= :ВыбДата
    ORDER BY c_vv.date DESC, c_vv.time DESC, c_vv.docid DESC, c_vv.row_id DESC
) AS LastPrice
WHERE Цены.PARENTEXT = :ВыбНоменклатура
  AND $Цены.ТипЦен = :ВыбТипЦен

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

Шаг 3. Альтернативный метод оптимизации через оконные функции (ROW_NUMBER)

Для очень больших объемов данных (например, когда строится прайс-лист по всей номенклатуре без жесткого отбора по одному родителю) эффективным решением может стать использование обобщенных табличных выражений (CTE) и оконных функций. Мы можем заранее пронумеровать записи в _1SConst для каждого товара в разрезе дат и соединить готовый плоский набор данных с основным справочником.

Рассмотрим реализацию этого метода:


WITH PreparedPrices AS (
    SELECT 
        objid,
        CAST(value AS numeric(14, 4)) AS Цена,
        CAST(date AS datetime) AS Дата,
        time AS Время,
        ROW_NUMBER() OVER (
            PARTITION BY objid 
            ORDER BY date DESC, time DESC, docid DESC, row_id DESC
        ) AS RowNum
    FROM _1sconst (NOLOCK)
    WHERE id = $ИсторияРеквизита.Цены.Цена
      AND date <= :ВыбДата
)
SELECT 
    Цены.PARENTEXT [Владелец $Справочник.Номенклатура],
    lp.Цена,
    lp.Дата,
    lp.Время
FROM $Справочник.Цены AS Цены WITH (NOLOCK)
LEFT JOIN PreparedPrices AS lp ON lp.objid = Цены.ID AND lp.RowNum = 1
WHERE $Цены.ТипЦен = :ВыбТипЦен

При таком подходе оптимизатор запросов MS SQL Server может применить более эффективные алгоритмы соединения таблиц (например, Hash Match или Merge Join вместо медленного Nested Loops), что особенно критично при формировании тяжелых регламентных отчетов по всей базе данных.

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