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