При разработке отчетов и обработок на платформе 1С:Предприятие 7.7 с использованием прямых запросов (компоненты 1С++) часто возникает задача: найти для каждой позиции номенклатуры самый последний проведенный документ, в котором она фигурировала. Типовой подход с объединением строк всех документов через UNION ALL и последующей группировкой по максимальной дате MAX(DATE_TIME_IDDOC) часто работает медленно и выглядит перегруженным. Разберем причины неоптимальности и рассмотрим практические способы ускорения выборки.
Проанализируем базовый запрос, чтобы понять, какие узкие места приводят к снижению производительности:
$ДокументСтроки.ИмяДокумента (физические таблицы DT*) по умолчанию присутствует только кластерный индекс по полям IDDOC + LINENO_. Поиск по реквизиту Номенклатура приводит к полному сканированию таблицы (Clustered Index Scan)._1SJourn только по полю IDDoc без указания вида документа IDDocDef СУБД не может задействовать индекс DOCTYPE (поля IDDOCDEF + DATE_TIME_IDDOC).MAX(DATE_TIME_IDDOC) выполняется повторное соединение с таблицей _1SJourn исключительно ради получения IDDoc и IDDocDef, хотя эти данные уже зашиты в составное поле даты и времени.ЖурДок.Closed & 1 = 1 мешает оптимизатору запросов эффективно применять фильтры по индексам.Скорректируем структуру запроса: перенесем таблицу _1SJourn в секцию FROM каждого подзапроса и явно укажем фильтрацию по виду документа ЖурДок.IDDocDef = $ВидДокумента.Имя. Это позволит SQL Server применить индекс DOCTYPE.
Посмотрим на пример правильного формирования блока объединения:
SELECT
СтрДок.Номенклатура AS Номенклатура,
ЖурДок.DATE_TIME_IDDOC AS DATE_TIME_IDDOC
FROM
_1SJourn AS ЖурДок WITH (NOLOCK)
INNER JOIN
$ДокументСтроки.Реализация AS СтрДок WITH (NOLOCK) ON СтрДок.IDDoc = ЖурДок.IDDoc
WHERE
ЖурДок.IDDocDef = $ВидДокумента.Реализация
AND ЖурДок.Closed & 1 = 1
Поле DATE_TIME_IDDOC в 1С 7.7 имеет строго фиксированную длину — 23 символа: 8 символов даты (ГГГГММДД), 6 символов времени (ЧЧММСС) и 9 символов уникального идентификатора документа (IDDoc). Мы можем получить IDDoc напрямую строковой функцией RIGHT(), полностью исключив завершающий INNER JOIN с таблицей _1SJourn.
Разберем готовый оптимизированный вариант запроса:
ТекстЗапроса = "
|SELECT
| ОбщаяТаблица.Номенклатура AS [Номенклатура $Справочник.Номенклатура],
| RIGHT(ОбщаяТаблица.MaxDateTimeIDDoc, 9) AS [Док $Документ]
|FROM
| (
| SELECT
| ОбъединеннаяТаб.Номенклатура AS Номенклатура,
| MAX(ОбъединеннаяТаб.DATE_TIME_IDDOC) AS MaxDateTimeIDDoc
| FROM
| (
| SELECT
| СтрРеал.Номенклатура AS Номенклатура,
| ЖурРеал.DATE_TIME_IDDOC AS DATE_TIME_IDDOC
| FROM
| _1SJourn AS ЖурРеал WITH (NOLOCK)
| INNER JOIN
| $ДокументСтроки.Реализация AS СтрРеал WITH (NOLOCK) ON СтрРеал.IDDoc = ЖурРеал.IDDoc
| WHERE
| ЖурРеал.IDDocDef = $ВидДокумента.Реализация
| AND ЖурРеал.Closed & 1 = 1
|
| UNION ALL
|
| SELECT
| СтрПост.Номенклатура AS Номенклатура,
| ЖурПост.DATE_TIME_IDDOC AS DATE_TIME_IDDOC
| FROM
| _1SJourn AS ЖурПост WITH (NOLOCK)
| INNER JOIN
| $ДокументСтроки.ПоступлениеТМЦ AS СтрПост WITH (NOLOCK) ON СтрПост.IDDoc = ЖурПост.IDDoc
| WHERE
| ЖурПост.IDDocDef = $ВидДокумента.ПоступлениеТМЦ
| AND ЖурПост.Closed & 1 = 1
| ) AS ОбъединеннаяТаб
| GROUP BY
| ОбъединеннаяТаб.Номенклатура
| ) AS ОбщаяТаблица
|";
Если база данных работает под управлением Microsoft SQL Server 2005 и новее, эффективнее отказаться от связки GROUP BY + MAX() в пользу оконной функции ROW_NUMBER() с секционированием по номенклатуре. Это позволяет СУБД выполнить сортировку и сразу отобрать первую строку для каждого товара за один проход.
Рассмотрим реализацию через оконные функции:
SELECT
Номенклатура AS [Номенклатура $Справочник.Номенклатура],
IDDoc AS [Док $Документ],
IDDocDef AS Док_вид
FROM
(
SELECT
ВсеСтроки.Номенклатура,
ВсеСтроки.IDDoc,
ВсеСтроки.IDDocDef,
ROW_NUMBER() OVER (
PARTITION BY ВсеСтроки.Номенклатура
ORDER BY ВсеСтроки.DATE_TIME_IDDOC DESC
) AS НомерСтроки
FROM
(
SELECT
Стр.Номенклатура,
Жур.IDDoc,
Жур.IDDocDef,
Жур.DATE_TIME_IDDOC
FROM _1SJourn AS Жур WITH (NOLOCK)
INNER JOIN $ДокументСтроки.Реализация AS Стр WITH (NOLOCK) ON Стр.IDDoc = Жур.IDDoc
WHERE Жур.IDDocDef = $ВидДокумента.Реализация AND Жур.Closed & 1 = 1
UNION ALL
SELECT
Стр.Номенклатура,
Жур.IDDoc,
Жур.IDDocDef,
Жур.DATE_TIME_IDDOC
FROM _1SJourn AS Жур WITH (NOLOCK)
INNER JOIN $ДокументСтроки.ПоступлениеТМЦ AS Стр WITH (NOLOCK) ON Стр.IDDoc = Жур.IDDoc
WHERE Жур.IDDocDef = $ВидДокумента.ПоступлениеТМЦ AND Жур.Closed & 1 = 1
) AS ВсеСтроки
) AS Итог
WHERE Итог.НомерСтроки = 1
Если требуется отслеживать только те документы, которые реально изменяли складские остатки (приход, расход, списание), гораздо быстрее обращаться не к табличным частям документов, а к таблице движений регистра остатков (например, $Регистр.ОстаткиТМЦ или _1SENTRY для бухгалтерского учета).
В регистрах измерение Номенклатура входит в состав кластерных индексов, поэтому выборка движений по конкретным позициям выполняется на порядки быстрее. Если в конфигураторе для регистра включена опция Быстрая обработка движений, таблица движений содержит прямую ссылку на документ IDDOC, что избавляет от необходимости сканировать строки первичных документов.
Если видов документов в конфигурации много, ручное написание десятков секций UNION ALL усложняет поддержку кода. Разберем, как автоматически обойти метаданные и собрать запрос только по тем документам, которые содержат реквизит табличной части нужного типа:
ТекстОбъединений = "";
Для НомерДок = 1 По Метаданные.Документ() Цикл
МетаДок = Метаданные.Документ(НомерДок);
РеквизитНайден = 0;
Для НомерРекв = 1 По МетаДок.РеквизитТабличнойЧасти() Цикл
МетаРекв = МетаДок.РеквизитТабличнойЧасти(НомерРекв);
Если (МетаРекв.Тип = "Справочник") И (МетаРекв.Вид = "Номенклатура") Тогда
РеквизитНайден = 1;
ИмяРеквизита = МетаРекв.Идентификатор;
Прервать;
КонецЕсли;
КонецЦикла;
Если РеквизитНайден = 1 Тогда
Если ПустаяСтрока(ТекстОбъединений) = 0 Тогда
ТекстОбъединений = ТекстОбъединений + " UNION ALL ";
КонецЕсли;
ТекстОбъединений = ТекстОбъединений + "
|SELECT
| СтрDoc." + ИмяРеквизита + " AS Номенклатура,
| ЖурDoc.DATE_TIME_IDDOC AS DATE_TIME_IDDOC
|FROM _1SJourn AS ЖурDoc WITH (NOLOCK)
|INNER JOIN $ДокументСтроки." + МетаДок.Идентификатор + " AS СтрDoc WITH (NOLOCK)
| ON СтрDoc.IDDoc = ЖурDoc.IDDoc
|WHERE ЖурDoc.IDDocDef = $ВидДокумента." + МетаДок.Идентификатор + "
| AND ЖурDoc.Closed & 1 = 1";
КонецЕсли;
КонецЦикла;
Таким образом, сочетание хинтов WITH (NOLOCK), фильтрации по IDDocDef, строкового извлечения идентификатора через RIGHT() или использования оконных функций позволяет сократить время выполнения прямого запроса в несколько раз.