При работе с крупными базами данных на платформе 1С:Предприятие 7.7 стандартные механизмы запросов часто не справляются с возрастающей нагрузкой. Разработчики вынуждены прибегать к написанию прямых SQL-запросов к СУБД (например, с использованием внешней компоненты 1С++). Одной из наиболее частых и ресурсоемких задач является выборка подчиненных документов за определенный период вместе с датами их документов-оснований (родительских документов).
В этой статье мы подробно разберем, как правильно связать таблицы журнала документов и подчиненности, чтобы СУБД использовала оптимальные индексы (Index Seek вместо Scan), устраним проблему избыточных чтений (Key Lookup), а также рассмотрим продвинутые техники создания кастомных индексов в обход ограничений платформы 1С 7.7.
Прежде чем приступать к написанию запроса, проанализируем физическую структуру таблиц СУБД, которые участвуют в связи документов:
_1SJourn: Содержит общую информацию обо всех документах системы. Ключевые поля, которые нас интересуют:
IDDOC — уникальный 9-символьный идентификатор документа (первичный ключ).IDDOCDEF — числовой идентификатор вида документа.DATE_TIME_IDDOC — позиция документа на временной шкале (строка длиной 23 символа, содержащая дату в формате YYYYMMDD, время и ИД документа)._1SCrdoc: Хранит ссылки между родительскими и дочерними документами. Ключевые поля:
MDID — идентификатор метаданных отбора (для прямых связей «родитель-потомок» всегда равен 0).PARENTVAL — типизированная строка ссылки на родительский документ (длина 23 символа). Состоит из префикса типа (например, 'O1'), 36-ричного идентификатора вида документа и 9-символьного IDDOC родителя.CHILDID — 9-символьный идентификатор подчиненного документа.Основная сложность заключается в том, чтобы связать поле PARENTVAL таблицы подчиненности с полем IDDOC таблицы журнала документов так, чтобы планировщик MS SQL Server мог использовать индекс по первичному ключу таблицы _1SJourn.
Рассмотрим ситуацию, когда нам необходимо выбрать подчиненные документы за определенный период и получить даты их оснований. Выясним причину неэффективности стандартного связывания строк. Если мы свяжем таблицы простым условием по подстроке, СУБД не сможет использовать индекс:
-- Неоптимальный вариант связи (вызывает сканирование индекса)
ON TabRod.ParentVal = 'O1 3M' + JOsn.IdDoc
Такой подход жестко привязывает запрос к конкретному виду документа (в примере 36-ричный идентификатор вида равен ' 3M') и часто приводит к неоптимальному плану выполнения. Более универсальным и производительным решением является использование функции СУБД SUBSTRING, но примененной к полю связи правильным образом.
Разберем по шагам итоговый оптимизированный запрос, который работает за 1-3 секунды вместо нескольких минут на больших базах данных:
DECLARE @PERIODN CHAR(9)
DECLARE @PERIODK CHAR(9)
SET @PERIODN = '20190101'
SET @PERIODK = '20190331Z'
SELECT TOP 100
J.IdDoc AS [ДокПодчиненный],
JOsn.IdDoc AS [ДокОснование],
JOsn.Date_Time_IdDoc AS Date_Time_IdDoc_Osn
FROM
_1sJourn J (NOLOCK)
-- Шаг 1: Связываем журнал подчиненных документов с таблицей связей по CHILDID и MDID = 0 (попадаем в индекс)
JOIN _1scrdoc TabRod (NOLOCK)
ON TabRod.mdid = 0
AND TabRod.ChildId = J.IdDoc
-- Шаг 2: Связываем с журналом для получения данных родительского документа
JOIN _1sJourn JOsn (NOLOCK)
ON SUBSTRING(TabRod.ParentVal, 7, 9) = JOsn.IdDoc
AND JOsn.IDDOCDEF = 130 -- Ограничение по виду документа-основания
WHERE
J.Date_Time_IdDoc BETWEEN @PERIODN AND @PERIODK
AND J.IDDOCDEF = 130 -- Ограничение по виду подчиненного документа
AND J.SP652 <> ' 0 0 ' -- Исключаем пустые ссылки
Почему это работает быстро? Условие TabRod.mdid = 0 AND TabRod.ChildId = J.IdDoc идеально ложится на системный индекс таблицы _1SCrdoc. Выделение чистой строки идентификатора родителя через SUBSTRING(TabRod.ParentVal, 7, 9) позволяет СУБД сопоставить его с первичным ключом (кластерным индексом) таблицы _1SJourn JOsn.
Проанализируем альтернативную ситуацию. В вашей конфигурации 1С 7.7 документ-основание может быть вынесен в общий реквизит документов (например, SP652) с включенным флагом «Отбор». В этом случае платформа автоматически создает индекс по данному реквизиту в таблице _1SJourn.
Мы можем полностью отказаться от соединения с таблицей _1SCrdoc, что значительно снизит количество логических чтений с диска. Посмотрим на пример такого запроса:
DECLARE @PERIODN CHAR(9)
DECLARE @PERIODK CHAR(9)
SET @PERIODN = '20190101'
SET @PERIODK = '20190331Z'
SELECT TOP 100
J.IdDoc AS [ДокПодчиненный],
JOsn.IdDoc AS [ДокОснование],
JOsn.Date_Time_IdDoc AS Date_Time_IdDoc_Osn
FROM
_1sJourn J (NOLOCK)
-- Соединяем напрямую через функцию RIGHT, выделяя 9 символов IDDOC
JOIN _1sJourn JOsn (NOLOCK)
ON JOsn.IdDoc = RIGHT(J.SP652, 9)
WHERE
J.Date_Time_IdDoc BETWEEN @PERIODN AND @PERIODK
AND J.SP652 LIKE ' 3M%' -- Фильтр по виду документа-основания
AND J.IDDOCDEF = 130
Этот способ работает чрезвычайно быстро на СУБД MS SQL Server, так как СУБД использует индекс общего реквизита для фильтрации подчиненных документов, а затем выполняет точечную выборку записей оснований из кластерного индекса по полю IdDoc.
Часто в подобных запросах требуется наложить условия на строки табличной части документа (например, отобрать документы только со ставкой НДС 18%). Рассмотрим типичный неоптимальный шаблон запроса с использованием INNER JOIN и последующей группировкой GROUP BY:
-- НЕОПТИМАЛЬНЫЙ ШАБЛОН С GROUP BY
SELECT
РасходнаяНакладная.IDDOC [Документ $Документ.РасходнаяНакладная],
$РасходнаяНакладная.СуммаВклНДС СуммаВклНДС
FROM _1SJOURN AS Журнал WITH (NOLOCK)
INNER JOIN $Документ.РасходнаяНакладная AS РасходнаяНакладная WITH (NOLOCK)
ON Журнал.IDDOC = РасходнаяНакладная.IDDOC
INNER JOIN $ДокументСтроки.РасходнаяНакладная AS РасходнаяНакладнаяСтроки WITH (NOLOCK)
ON РасходнаяНакладная.IDDOC = РасходнаяНакладнаяСтроки.IDDOC
WHERE (Журнал.DATE_TIME_IDDOC BETWEEN :НачДата AND :КонДата~)
AND $РасходнаяНакладнаяСтроки.СтавкаНДС = :Ставка18
GROUP BY РасходнаяНакладная.IDDOC, $РасходнаяНакладная.СуммаВклНДС
Такой запрос заставляет СУБД выполнять операцию Key Lookup (обращение к физическим страницам данных для извлечения неиндексированных колонок) для каждой строки табличной части, а затем проводить тяжелую сортировку для выполнения группировки GROUP BY.
Давайте оптимизируем эту ситуацию. Заменим соединение табличной части на высокопроизводительную конструкцию EXISTS. Это позволит СУБД прекратить сканирование строк документа, как только будет найдено первое совпадение по ставке НДС, и полностью избавит нас от необходимости использовать GROUP BY:
-- ОПТИМИЗИРОВАННЫЙ ВАРИАНТ С EXISTS
SELECT
РасходнаяНакладная.IDDOC [Документ $Документ.РасходнаяНакладная],
$РасходнаяНакладная.СуммаВклНДС AS СуммаВклНДС,
Cast(Left(Журнал.DATE_TIME_IDDOC, 8) AS DATETIME) AS Дата,
Журнал.$ОбщийРеквизит.ДокументОснование AS [ДокументОснование $Документ]
FROM _1SJOURN AS Журнал WITH (NOLOCK)
INNER JOIN $Документ.РасходнаяНакладная AS РасходнаяНакладная WITH (NOLOCK)
ON Журнал.IDDOC = РасходнаяНакладная.IDDOC
WHERE (Журнал.DATE_TIME_IDDOC BETWEEN :НачДата AND :КонДата~)
AND Журнал.IDDOCDEF = $ВидДокумента.РасходнаяНакладная
-- Заменяем JOIN табличной части на EXISTS
AND EXISTS (
SELECT 1
FROM $ДокументСтроки.РасходнаяНакладная AS Строки WITH (NOLOCK)
WHERE Строки.IDDOC = РасходнаяНакладная.IDDOC
AND Строки.СтавкаНДС = :Ставка18
)
AND Журнал.$ОбщийРеквизит.ДокументОснование <> ' 0 0 '
Использование EXISTS кардинально сокращает время выполнения запроса на больших базах данных, так как исключает появление дубликатов строк документов на этапе соединения и убирает фазу группировки.
Для достижения максимальной производительности прямых запросов иногда требуется добавить в таблицы СУБД собственные (кастомные) индексы. Однако платформа 1С 7.7 жестко контролирует структуру таблиц. При запуске она сверяет физические индексы СУБД со словарем данных 1cv7.dds и при обнаружении расхождений выдает ошибку, завершая работу.
Для обхода этого ограничения разработчики используют две дополняющие друг друга методики:
1. Динамическое воссоздание индексов по хэшу MD-файла.
Кастомные индексы создаются программно при запуске системы, если файл конфигурации 1cv7.md был изменен. Напишем пример процедуры для модуля ПриНачалеРаботыСистемы:
Процедура ПриНачалеРаботыСистемы()
// Проверяем изменение структуры метаданных по хэшу MD-файла
MetaDataWork = СоздатьОбъект("MetaDataWork");
MDHash = СокрЛП(СервисFormEx.ПолучитьХэшМД5(КаталогИБ() + "1cv7.md", 1));
Если СокрЛП(Константа.MDHash) <> MDHash Тогда
// Если конфигурация менялась, создаем наши индексы заново
ТекстЗапроса = "
|IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'P1C_Custom_Journ_Index')
|CREATE NONCLUSTERED INDEX P1C_Custom_Journ_Index
|ON _1SJourn (IDDOCDEF, DATE_TIME_IDDOC)
|INCLUDE (IDDOC);";
глВыполнитьЗапрос(глБДЗапрос, ТекстЗапроса);
Константа.MDHash = MDHash;
КонецЕсли;
КонецПроцедуры
2. Патч системной процедуры sp_statistics для скрытия индексов.
Чтобы платформа 1С 7.7 не "видела" созданные вручную индексы и успешно запускалась, вы можете модифицировать системную процедуру sp_statistics в СУБД MS SQL (база данных master или конкретная рабочая база). Суть патча заключается в фильтрации результатов: когда запрос к списку индексов идет со стороны приложения 1cv7.exe, процедура скрывает любые индексы, имя которых начинается со специального префикса (например, P1C_).
Пример изменения логики внутри процедуры sp_statistics:
-- Фрагмент кода внутри sp_statistics перед возвратом результирующего набора
IF APP_NAME() = '1CV7'
BEGIN
SELECT * FROM #temp_stats WHERE INDEX_NAME NOT LIKE 'P1C_%'
END
ELSE
BEGIN
SELECT * FROM #temp_stats
END
Благодаря этому трюку ваши кастомные индексы будут активно использоваться оптимизатором MS SQL при выполнении прямых запросов, но сама платформа 1С 7.7 продолжит стабильно работать, не догадываясь об их существовании в СУБД.