При работе с большими базами данных 1С запросы к виртуальным таблицам остатков могут выполняться медленно, особенно если в них используются сложные условия, обращения через точку или иерархические отборы. Рассмотрим подробно, как оптимизировать представленный в теме запрос, проанализируем узкие места и разберем по шагам правильные подходы к написанию высокопроизводительного кода.
Проанализируем первый пакет исходного запроса. В нем условие по номенклатуре и остаткам наложено в секции ГДЕ. Это типичная ошибка начинающих разработчиков. Когда мы используем секцию ГДЕ для виртуальной таблицы, система сначала получает все остатки из базы данных (возможно, по всей номенклатуре и всем складам), и только потом фильтрует их средствами SQL.
Для ускорения работы необходимо перенести все возможные фильтры в параметры виртуальной таблицы. Это позволит SQL-серверу использовать индексы и отобрать только нужные записи еще на этапе обращения к таблице итогов. Разберем, как должен выглядеть исправленный фрагмент:
// Вместо ГДЕ используем параметры виртуальной таблицы
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(
&Дата1,
Склад В ИЕРАРХИИ (&Склады) И Номенклатура В ИЕРАРХИИ (&ВыбНоменкл)
) КАК ПартииТоваровНаСкладахОстатки
Важно: Условие на отрицательные остатки (КоличествоОстаток < 0) оставить в ГДЕ допустимо, так как это рассчитываемые поля, которых нет в физической таблице индексов в «чистом» виде. Однако фильтры по измерениям (Склад, Номенклатура) обязаны быть в параметрах.
Конструкция В ИЕРАРХИИ является одной из самых тяжелых для SQL-сервера. Платформа 1С преобразует её в сложный запрос с множеством OR или временных таблиц, содержащих все вложенные элементы. Если дерево складов или номенклатуры имеет большую вложенность, производительность падает в геометрической прогрессии.
Рассмотрим более эффективный метод — предварительную выгрузку элементов иерархии во временную таблицу. Проанализируем пример кода:
// Шаг 1: Получаем все склады из иерархии в ВТ
ВЫБРАТЬ
Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВТ_Склады
ИЗ
Справочник.Склады КАК Склады
ГДЕ
Склады.Ссылка В ИЕРАРХИИ(&Склады)
ИНДЕКСИРОВАТЬ ПО
Склад;
// Шаг 2: Аналогично для номенклатуры, если она задана
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_ВыбНоменклатура
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В ИЕРАРХИИ(&ВыбНоменкл)
ИНДЕКСИРОВАТЬ ПО
Номенклатура;
Теперь в основном запросе вместо В ИЕРАРХИИ мы будем использовать простое условие В (ВЫБРАТЬ Номенклатура ИЗ ВТ_ВыбНоменклатура). Это позволяет оптимизатору SQL применить более быстрые алгоритмы соединения таблиц (например, Semi Join).
В исходном запросе мы видим обращения ПартииТоваровНаСкладахОстатки.Номенклатура.Код и ДокументОприходования.Дата. В языке запросов 1С каждое такое обращение — это неявное левое соединение (LEFT JOIN) с таблицей справочника или документа.
Если в выборку попадает 100 000 строк, система выполнит 100 000 соединений. Выясним, как сделать это быстрее. Правильная стратегия — сначала получить все ссылки во временную таблицу, а в финальном запросе одним соединением вытянуть нужные реквизиты. Это значительно снижает нагрузку на дисковую подсистему сервера БД.
Рассмотрим фрагмент кода из первого сообщения:
ГДЕ
ВЫБОР
КОГДА &ВыбНоменкл <> НЕОПРЕДЕЛЕНО
ТОГДА ПартииТоваровНаСкладахОстатки.Номенклатура В ИЕРАРХИИ (&ВыбНоменкл)
ИНАЧЕ ИСТИНА
КОНЕЦ
Использование логики ВЫБОР внутри секции ГДЕ мешает SQL-серверу построить оптимальный план выполнения (запрос становится non-SARGable). Сервер не знает заранее, какое условие будет выполняться, и часто выбирает сканирование всей таблицы (Table Scan) вместо поиска по индексу.
Решение: Используйте динамическое формирование текста запроса в коде 1С. Если параметр ВыбНоменкл не заполнен, просто не добавляйте строку с фильтром в текст запроса. Это гарантирует, что SQL-сервер получит максимально простой и понятный текст для исполнения.
Проанализируем структуру регистра ПартииТоваровНаСкладах. Виртуальные таблицы работают быстро только тогда, когда порядок полей в фильтре соответствует порядку измерений в конфигураторе. Если в регистре первым измерением идет Номенклатура, а в запросе вы первым делом фильтруете по Складу, индекс может не сработать.
Также важно убедиться, что итоги регистра рассчитаны. Если &Дата1 в запросе сильно отличается от даты актуальности итогов, система будет вынуждена вычислять остатки «на лету» по таблице движений, что крайне медленно. Рекомендуем выполнять запрос на начало или конец периода (месяца), когда итоги гарантированно рассчитаны.
С учетом всех разобранных моментов, проанализируем, как должен выглядеть итоговый вариант запроса:
// 1. Подготовка складов
ВЫБРАТЬ Ссылка ПОМЕСТИТЬ ВТ_Склады ИЗ Справочник.Склады ГДЕ Ссылка В ИЕРАРХИИ(&Склады) ИНДЕКСИРОВАТЬ ПО Ссылка;
// 2. Получение "минусовых" партий
ВЫБРАТЬ
Остатки.Номенклатура,
Остатки.Склад,
Остатки.СерияНоменклатуры,
Остатки.ДокументОприходования,
Остатки.Качество,
Остатки.СтатусПартии,
Остатки.ХарактеристикаНоменклатуры,
Остатки.Заказ
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&Дата1, Склад В (ВЫБРАТЬ Ссылка ИЗ ВТ_Склады)) КАК Остатки
ГДЕ
(Остатки.КоличествоОстаток < 0 ИЛИ Остатки.СтоимостьОстаток < 0);
// 3. Финальная выборка с получением реквизитов через соединение
ВЫБРАТЬ
Данные.Номенклатура.Код КАК Код,
Данные.Номенклатура,
Данные.Склад,
Данные.ХарактеристикаНоменклатуры,
Данные.СерияНоменклатуры,
Данные.ДокументОприходования,
Данные.ДокументОприходования.Дата КАК ДатаДокОприходования,
Данные.СтатусПартии,
Данные.Заказ,
Данные.Качество
ИЗ
ВТ_Данные КАК Данные
УПОРЯДОЧИТЬ ПО
Код,
ДатаДокОприходования;
Подведем итог: Основное ускорение в 1С достигается за счет минимизации объема данных, передаваемых между SQL-сервером и приложением, использования индексов через параметры виртуальных таблиц и отказа от сложных иерархических конструкций в пользу предварительно подготовленных временных таблиц.