При работе с информационными системами на платформе 1С:Предприятие мы часто сталкиваемся с проблемой медленных запросов. Особенно остро это ощущается на больших объемах данных, когда отчеты формируются часами, а интерактивная работа пользователей замедляется. Оптимизация запросов — это не просто настройка, а комплексная задача, требующая глубокого понимания принципов работы СУБД и платформы 1С.
В этой статье мы подробно разберем типичные проблемы, с которыми сталкиваются разработчики при написании запросов, и предложим эффективные решения на основе реального примера с форума 1С, связанного с выборкой данных по основным средствам, амортизации и штрихкодам. Мы вместе проанализируем каждую часть проблемного запроса и выясним, как сделать его значительно быстрее.
Давайте проанализируем исходную ситуацию. Перед нами стоит задача оптимизировать запрос, который работает с регистрами сведений "АмортизацияОС", "Штрихкоды", регистром накопления "СтоимостьОбъектовОС" и справочником "ОсновныеСредства". В процессе обсуждения на форуме были выявлены следующие проблемные места:
ПОДОБНО "%" + &Местонахождение + "%" для отбора по наименованию местонахождения из справочника "Основные средства", что крайне неэффективно на больших объемах данных.Регистратор.КапитальноеВложение в условии виртуальной таблицы СрезПоследних, что может приводить к неоптимальному плану выполнения запроса из-за необходимости соединений со всеми возможными типами регистраторов.Прежде чем погружаться в детали конкретного запроса, давайте вспомним основные правила, которые помогут нам в оптимизации любого запроса в 1С:
ГДЕ) и в условиях соединения (ПО).СрезПоследних, Остатки) или применяйте их в секции ГДЕ как можно раньше, чтобы уменьшить объем данных до выполнения ресурсоемких операций.ПОДОБНО "%...%") или требуют большого количества ресурсов (например, ИЛИ в сложных условиях).Временные таблицы (ВТ) — это мощный инструмент для оптимизации сложных запросов в 1С. Они позволяют разбить большой, трудночитаемый и медленный запрос на несколько более простых шагов. Каждый шаг формирует промежуточный результат, который сохраняется во временной таблице, а затем используется в следующих частях пакетного запроса. Это значительно улучшает читаемость кода, упрощает отладку и, что самое главное, повышает производительность, позволяя СУБД строить более оптимальные планы выполнения.
Рассмотрим, как мы можем применить этот подход к нашему запросу. Исходный запрос уже использует временные таблицы, но мы можем улучшить их использование.
Основные рекомендации по работе с временными таблицами:
ИНДЕКСИРОВАТЬ ПО. В индекс включайте поля, по которым будет происходить соединение или отбор.Давайте посмотрим на пример из сообщения 15 и внесем улучшения:
// Шаг 1: Отбираем ОсновныеСредства по местонахождению
// ВТ_Местонахождение будет содержать ссылки на ОС, а не просто строки местонахождения
ВЫБРАТЬ
ОсновныеСредства.Ссылка КАК ОсновноеСредство
ПОМЕСТИТЬ ВТ_ОтфильтрованныеОСПоМестонахождению
ИЗ
Справочник.ОсновныеСредства КАК ОсновныеСредства
ГДЕ
ОсновныеСредства.Местонахождение ПОДОБНО "%" + &Местонахождение + "%"
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство;
В этом примере мы не просто сохраняем строку местонахождения, а сразу отбираем ссылки на ОсновноеСредство, что позволит нам избежать повторного обращения к справочнику.
Одним из самых "тяжелых" операторов для СУБД является ПОДОБНО, особенно когда шаблон начинается и заканчивается символом процента (%), как в ПОДОБНО "%" + &Местонахождение + "%". В этом случае СУБД не может использовать индексы по полю Местонахождение и вынуждена выполнять полное сканирование всей таблицы. Если таблица содержит сотни миллионов записей, как было упомянуто на форуме, это приводит к катастрофическому падению производительности.
Как избежать `ПОДОБНО "%...%"`:
ПОДОБНО (хотя лучше избегать его совсем).Давайте улучшим наш первый шаг с отбором по Местонахождение:
// Шаг 1: Отбираем ссылки на ОсновныеСредства по местонахождению
// Вместо сохранения строки местонахождения, сразу отбираем ссылки на ОС.
// Это позволит нам соединяться по ссылке, а не по строке.
ВЫБРАТЬ
ОсновныеСредства.Ссылка КАК ОсновноеСредство
ПОМЕСТИТЬ ВТ_ОСПоМестонахождению
ИЗ
Справочник.ОсновныеСредства КАК ОсновныеСредства
ГДЕ
ОсновныеСредства.Местонахождение ПОДОБНО "%" + &Местонахождение + "%"
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство;
Мы по-прежнему используем ПОДОБНО на этом этапе, но теперь результат этого "тяжелого" отбора сохраняется в маленькой временной таблице, содержащей только ссылки. Дальнейшие операции будут работать с этими ссылками, а не выполнять повторное сканирование справочника.
Виртуальные таблицы, такие как СрезПоследних для регистров сведений и Остатки для регистров накопления, являются мощными инструментами 1С. Они позволяют получить актуальные данные на определенную дату. Однако их неэффективное использование может стать причиной медленной работы запросов.
Ключевые моменты при работе с виртуальными таблицами:
РегистрСведений.АмортизацияОС.СрезПоследних(&Дата, ОсновноеСредство = &ОС И Организация = &Организация). Это позволяет СУБД применить отборы до того, как будет сформирован полный срез, что значительно сокращает объем обрабатываемых данных.АмортизацияОССрезПоследних.Регистратор.КапитальноеВложение. Если поле Регистратор имеет составной тип (например, может быть "Документ.Поступление", "Документ.ВводНачальныхОстатков" и т.д.), обращение к его реквизитам через точку (.КапитальноеВложение) вынуждает 1С соединяться со всеми таблицами, соответствующими возможным типам. Это очень ресурсоемкая операция.СрезПоследних не вернет данных по этому ОС. Если нам нужно видеть такие ОС в отчете, нам потребуется изменить логику соединения (например, использовать ЛЕВОЕ СОЕДИНЕНИЕ с основным источником данных по ОС).Давайте улучшим запрос к РегистрСведений.АмортизацияОС.СрезПоследних:
// Шаг 2: Получаем данные по амортизации, применяя отборы максимально рано.
// Для обхода проблемы с Регистратор.КапитальноеВложение, если КапитальноеВложение - это реквизит регистратора,
// мы можем использовать ВЫРАЗИТЬ, если заранее знаем тип регистратора,
// или пересмотреть логику, если условие по регистратору не является критичным для среза.
// В данном случае, предположим, что КапитальноеВложение является измерением или реквизитом регистра,
// а не реквизитом регистратора. Если же это реквизит регистратора, то нужно вынести это условие за пределы среза
// или использовать ВЫРАЗИТЬ.
ВЫБРАТЬ
АмортизацияОССрезПоследних.ОсновноеСредство КАК ОсновноеСредство,
АмортизацияОССрезПоследних.Организация КАК Организация
ПОМЕСТИТЬ ВТ_АмортизацияОС
ИЗ
РегистрСведений.АмортизацияОС.СрезПоследних(
&Дата,
(КапитальноеВложение = &КапитальноеВложение И ОсновноеСредство.АмортизационнаяГруппа = &АмортизационнаяГруппа)
ИЛИ
(КапитальноеВложение = &КапитальноеВложение И ОсновноеСредство В
(ВЫБРАТЬ
ВТ_ОСПоМестонахождению.ОсновноеСредство
ИЗ
ВТ_ОСПоМестонахождению КАК ВТ_ОСПоМестонахождению))
) КАК АмортизацияОССрезПоследних
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация;
Обратите внимание, что в исходном запросе (сообщение 15) для СрезПоследних используется ОБЪЕДИНИТЬ ВСЕ, чтобы реализовать логику ИЛИ. Это хороший подход, так как ОБЪЕДИНИТЬ ВСЕ работает быстрее, чем ИЛИ в условиях, поскольку не требует удаления дубликатов. Мы перенесли условие Местонахождение В (...) внутрь виртуальной таблицы. Если КапитальноеВложение является реквизитом регистратора, а не самого регистра сведений, то условие по нему придется вынести из параметров виртуальной таблицы и применять уже к результату среза, но это может быть менее эффективно.
Решение проблемы с Регистратор.КапитальноеВложение:
Если КапитальноеВложение — это реквизит регистратора, и мы не можем просто так передать его в параметры СрезПоследних, нам необходимо:
КапитальноеВложение. Для этого можно использовать оператор ВЫРАЗИТЬ, чтобы явно указать тип регистратора и избежать избыточных соединений.
// Пример, если КапитальноеВложение - реквизит регистратора
// Шаг 2.1: Получаем срез последних без условия по КапитальноеВложение
ВЫБРАТЬ
АмортизацияОССрезПоследних.ОсновноеСредство КАК ОсновноеСредство,
АмортизацияОССрезПоследних.Организация КАК Организация,
АмортизацияОССрезПоследних.Регистратор КАК Регистратор
ПОМЕСТИТЬ ВТ_АмортизацияОС_Предварительный
ИЗ
РегистрСведений.АмортизацияОС.СрезПоследних(
&Дата,
(ОсновноеСредство.АмортизационнаяГруппа = &АмортизационнаяГруппа)
ИЛИ
(ОсновноеСредство В
(ВЫБРАТЬ
ВТ_ОСПоМестонахождению.ОсновноеСредство
ИЗ
ВТ_ОСПоМестонахождению КАК ВТ_ОСПоМестонахождению))
) КАК АмортизацияОССрезПоследних
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация,
Регистратор;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2.2: Фильтруем по КапитальноеВложение, используя ВЫРАЗИТЬ
ВЫБРАТЬ
ВТ_АмортизацияОС_Предварительный.ОсновноеСредство КАК ОсновноеСредство,
ВТ_АмортизацияОС_Предварительный.Организация КАК Организация
ПОМЕСТИТЬ ВТ_АмортизацияОС
ИЗ
ВТ_АмортизацияОС_Предварительный КАК ВТ_АмортизацияОС_Предварительный
ГДЕ
ВЫРАЗИТЬ(ВТ_АмортизацияОС_Предварительный.Регистратор КАК Документ.ПринятиеКУчетуОС).КапитальноеВложение = &КапитальноеВложение
ИЛИ
ВЫРАЗИТЬ(ВТ_АмортизацияОС_Предварительный.Регистратор КАК Документ.ВводНачальныхОстатковОС).КапитальноеВложение = &КапитальноеВложение
// Добавьте другие типы регистраторов, которые имеют реквизит КапитальноеВложение
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация;
В этом примере мы явно указываем типы документов, у которых есть реквизит КапитальноеВложение, что позволяет СУБД избежать соединений со всеми возможными таблицами и значительно ускорить запрос.
Порядок соединения таблиц и наличие правильных индексов играют критическую роль в производительности запросов. Всегда стремитесь соединять таблицы таким образом, чтобы на каждом шаге объем данных уменьшался или оставался минимальным.
Рекомендации по соединениям:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ): Если вам нужны данные, которые присутствуют в обеих таблицах, используйте внутреннее соединение. Оно отсекает строки, не имеющие совпадений.ЛЕВОЕ СОЕДИНЕНИЕ) с осторожностью: Левое соединение сохраняет все строки из левой таблицы, даже если нет совпадений в правой. Это может быть необходимо, но если правая таблица очень большая, а левая маленькая, это может быть неэффективно. Старайтесь, чтобы правая таблица в левом соединении была максимально отфильтрована.Рекомендации по индексированию:
ГДЕ): Если вы часто фильтруете по какому-либо полю, оно должно быть в индексе.ПО): Поля, участвующие в условиях ПО при соединениях, также должны быть проиндексированы. Для ВНУТРЕННЕГО СОЕДИНЕНИЯ индексируйте поля в обеих таблицах. Для ЛЕВОГО СОЕДИНЕНИЯ особенно важно индексировать поля в правой таблице.Рассмотрим следующий шаг запроса, где мы получаем остатки стоимости ОС и затем группируем их:
// Шаг 3: Получаем остатки стоимости ОС, отфильтрованные по ОС и Организации из предыдущей ВТ.
ВЫБРАТЬ
СтоимостьОбъектовОСОстатки.ОсновноеСредство КАК ОсновноеСредство,
СтоимостьОбъектовОСОстатки.Организация КАК Организация,
СтоимостьОбъектовОСОстатки.ИнвентарныйНомер КАК ИнвентарныйНомер,
СтоимостьОбъектовОСОстатки.СтоимостьОстаток КАК СтоимостьОстаток
ПОМЕСТИТЬ ВТ_СтоимостьОстатки
ИЗ
РегистрНакопления.СтоимостьОбъектовОС.Остатки(
&Дата,
(ОсновноеСредство, Организация) В
(ВЫБРАТЬ
ВТ_АмортизацияОС.ОсновноеСредство,
ВТ_АмортизацияОС.Организация
ИЗ
ВТ_АмортизацияОС КАК ВТ_АмортизацияОС)
) КАК СтоимостьОбъектовОСОстатки
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация,
ИнвентарныйНомер;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Группируем и фильтруем по стоимости
ВЫБРАТЬ
ВТ_СтоимостьОстатки.ОсновноеСредство КАК ОсновноеСредство,
ВТ_СтоимостьОстатки.Организация КАК Организация,
ВТ_СтоимостьОстатки.ИнвентарныйНомер КАК ИнвентарныйНомер,
СУММА(ВТ_СтоимостьОстатки.СтоимостьОстаток) КАК СтоимостьОстаток
ПОМЕСТИТЬ ВТ_Сгруппированные
ИЗ
ВТ_АмортизацияОС КАК ВТ_АмортизацияОС
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СтоимостьОстатки КАК ВТ_СтоимостьОстатки
ПО ВТ_АмортизацияОС.ОсновноеСредство = ВТ_СтоимостьОстатки.ОсновноеСредство
И ВТ_АмортизацияОС.Организация = ВТ_СтоимостьОстатки.Организация
ГДЕ
ВТ_СтоимостьОстатки.СтоимостьОстаток > 10000
СГРУППИРОВАТЬ ПО
ВТ_СтоимостьОстатки.ОсновноеСредство,
ВТ_СтоимостьОстатки.Организация,
ВТ_СтоимостьОстатки.ИнвентарныйНомер
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация,
ИнвентарныйНомер;
Здесь мы видим, что отбор по (ОсновноеСредство, Организация) В (...) передается непосредственно в виртуальную таблицу Остатки. Это очень хорошо, так как позволяет СУБД сразу работать с меньшим объемом данных. Далее, ВНУТРЕННЕЕ СОЕДИНЕНИЕ с ВТ_АмортизацияОС также является эффективным, поскольку обе таблицы уже сильно отфильтрованы. Индексирование ВТ_Сгруппированные по ключам соединения и группировки также является правильным шагом.
Наконец, мы переходим к присоединению данных о штрихкодах. Сообщение 2 предлагает "Отбери сперва в пакетном запросе данные без регистра Штрихкоды, а потом, во втором запросе пакета этот короткий результат соедини с регистром штрихкодов". Исходный запрос (сообщение 15) следует этому принципу, присоединяя штрихкоды на последнем этапе к уже сгруппированным и отфильтрованным данным.
// Шаг 5: Присоединяем штрихкоды
ВЫБРАТЬ
ВТ_Сгруппированные.ОсновноеСредство КАК ОсновноеСредство,
ВТ_Сгруппированные.Организация КАК Организация,
ВТ_Сгруппированные.СтоимостьОстаток КАК СтоимостьОстаток,
МАКСИМУМ(ЕСТЬNULL(Штрихкоды.Штрихкод, "")) КАК Штрихкод
ИЗ
ВТ_Сгруппированные КАК ВТ_Сгруппированные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
ПО ВТ_Сгруппированные.ИнвентарныйНомер = Штрихкоды.ИнвентарныйНомер
СГРУППИРОВАТЬ ПО
ВТ_Сгруппированные.ОсновноеСредство,
ВТ_Сгруппированные.Организация,
ВТ_Сгруппированные.СтоимостьОстаток;
Использование ЛЕВОЕ СОЕДИНЕНИЕ здесь оправдано, поскольку нам нужно получить все Основные Средства из ВТ_Сгруппированные, независимо от того, есть у них штрихкоды или нет. Применение функции МАКСИМУМ для Штрихкод (в случае, если у одного инвентарного номера может быть несколько штрихкодов, и нам нужен один из них) и ЕСТЬNULL для обработки отсутствующих штрихкодов также является стандартной практикой.
Объединяя все наши рекомендации, мы можем представить следующий вариант оптимизированного запроса. Обратите внимание, что конкретная реализация может варьироваться в зависимости от структуры данных и точных требований.
////////////////////////////////////////////////////////////////////////////////
// Шаг 1: Отбираем ссылки на ОсновныеСредства по местонахождению
// Делаем "тяжелый" отбор ПОДОБНО только один раз и сохраняем ссылки на ОС.
ВЫБРАТЬ
ОсновныеСредства.Ссылка КАК ОсновноеСредство
ПОМЕСТИТЬ ВТ_ОСПоМестонахождению
ИЗ
Справочник.ОсновныеСредства КАК ОсновныеСредства
ГДЕ
ОсновныеСредства.Местонахождение ПОДОБНО "%" + &Местонахождение + "%"
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем данные по амортизации с максимально ранними отборами.
// Используем ОБЪЕДИНИТЬ ВСЕ для реализации логики ИЛИ внутри виртуальной таблицы.
// Если КапитальноеВложение - реквизит регистратора, то этот шаг нужно разбить,
// как показано в примере выше с ВЫРАЗИТЬ.
ВЫБРАТЬ
АмортизацияОССрезПоследних.ОсновноеСредство КАК ОсновноеСредство,
АмортизацияОССрезПоследних.Организация КАК Организация
ПОМЕСТИТЬ ВТ_АмортизацияОС
ИЗ
РегистрСведений.АмортизацияОС.СрезПоследних(
&Дата,
(КапитальноеВложение = &КапитальноеВложение И ОсновноеСредство.АмортизационнаяГруппа = &АмортизационнаяГруппа)
ИЛИ
(КапитальноеВложение = &КапитальноеВложение И ОсновноеСредство В
(ВЫБРАТЬ
ВТ_ОСПоМестонахождению.ОсновноеСредство
ИЗ
ВТ_ОСПоМестонахождению КАК ВТ_ОСПоМестонахождению))
) КАК АмортизацияОССрезПоследних
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Получаем остатки стоимости ОС, отфильтрованные по ОсновномуСредству и Организации
// из ВТ_АмортизацияОС.
ВЫБРАТЬ
СтоимостьОбъектовОСОстатки.ОсновноеСредство КАК ОсновноеСредство,
СтоимостьОбъектовОСОстатки.Организация КАК Организация,
СтоимостьОбъектовОСОстатки.ИнвентарныйНомер КАК ИнвентарныйНомер,
СтоимостьОбъектовОСОстатки.СтоимостьОстаток КАК СтоимостьОстаток
ПОМЕСТИТЬ ВТ_СтоимостьОстатки
ИЗ
РегистрНакопления.СтоимостьОбъектовОС.Остатки(
&Дата,
(ОсновноеСредство, Организация) В
(ВЫБРАТЬ
ВТ_АмортизацияОС.ОсновноеСредство,
ВТ_АмортизацияОС.Организация
ИЗ
ВТ_АмортизацияОС КАК ВТ_АмортизацияОС)
) КАК СтоимостьОбъектовОСОстатки
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация,
ИнвентарныйНомер;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Соединяем данные по амортизации и стоимости, группируем и фильтруем.
// Здесь ВНУТРЕННЕЕ СОЕДИНЕНИЕ оправдано, так как нам нужны ОС, по которым есть и амортизация, и стоимость.
ВЫБРАТЬ
ВТ_СтоимостьОстатки.ОсновноеСредство КАК ОсновноеСредство,
ВТ_СтоимостьОстатки.Организация КАК Организация,
ВТ_СтоимостьОстатки.ИнвентарныйНомер КАК ИнвентарныйНомер,
СУММА(ВТ_СтоимостьОстатки.СтоимостьОстаток) КАК СтоимостьОстаток
ПОМЕСТИТЬ ВТ_Сгруппированные
ИЗ
ВТ_АмортизацияОС КАК ВТ_АмортизацияОС
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СтоимостьОстатки КАК ВТ_СтоимостьОстатки
ПО ВТ_АмортизацияОС.ОсновноеСредство = ВТ_СтоимостьОстатки.ОсновноеСредство
И ВТ_АмортизацияОС.Организация = ВТ_СтоимостьОстатки.Организация
ГДЕ
ВТ_СтоимостьОстатки.СтоимостьОстаток > 10000
СГРУППИРОВАТЬ ПО
ВТ_СтоимостьОстатки.ОсновноеСредство,
ВТ_СтоимостьОстатки.Организация,
ВТ_СтоимостьОстатки.ИнвентарныйНомер
ИНДЕКСИРОВАТЬ ПО
ОсновноеСредство,
Организация,
ИнвентарныйНомер;
////////////////////////////////////////////////////////////////////////////////
// Шаг 5: Присоединяем штрихкоды к уже отфильтрованным и сгруппированным данным.
// Используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы получить все ОС, даже если у них нет штрихкодов.
ВЫБРАТЬ
ВТ_Сгруппированные.ОсновноеСредство КАК ОсновноеСредство,
ВТ_Сгруппированные.Организация КАК Организация,
ВТ_Сгруппированные.СтоимостьОстаток КАК СтоимостьОстаток,
МАКСИМУМ(ЕСТЬNULL(Штрихкоды.Штрихкод, "")) КАК Штрихкод
ИЗ
ВТ_Сгруппированные КАК ВТ_Сгруппированные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
ПО ВТ_Сгруппированные.ИнвентарныйНомер = Штрихкоды.ИнвентарныйНомер
СГРУППИРОВАТЬ ПО
ВТ_Сгруппированные.ОсновноеСредство,
ВТ_Сгруппированные.Организация,
ВТ_Сгруппированные.СтоимостьОстаток;
Оптимизация запросов в 1С — это итеративный процесс. Нам всегда следует начинать с анализа проблемного запроса, выявлять "узкие места", применять общие принципы оптимизации, а затем тщательно проверять результат с помощью Консоли запросов 1С, анализируя план выполнения. Помните, что каждый запрос уникален, и к его оптимизации нужно подходить индивидуально, учитывая специфику данных и требования к отчету.
← К списку