При разработке отчетов или обработок в конфигурации 1С:Управление торговлей 11 программисты часто сталкиваются с задачей получения актуальных остатков товаров вместе с их ценами. Однако некорректно составленный запрос может привести к катастрофическому падению производительности: база «подвисает», оперативная память сервера приложений стремительно заполняется, а дисковое пространство под TempDB на SQL-сервере заканчивается. Разберем детально, почему это происходит и как построить эффективный запрос.
Проанализируем исходный запрос, который вызвал проблему:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
СвободныеОстатки.ВНаличии КАК ВНаличии
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних,
РегистрНакопления.СвободныеОстатки КАК СвободныеОстатки
Главная ошибка здесь заключается в отсутствии условия связи между таблицами. В языке запросов 1С (как и в стандартном SQL), если две таблицы просто перечислены через запятую без секции СОЕДИНЕНИЕ...ПО или условия в секции ГДЕ, система выполняет декартово произведение (Cross Join).
Рассмотрим, что это означает на практике. Если в вашей базе данных содержится 10 000 уникальных товаров с ценами и 10 000 записей об остатках, сервер попытается создать временный результат, в котором каждая строка из первой таблицы будет сопоставлена с каждой строкой из второй таблицы. В итоге получится таблица объемом 100 000 000 строк. Именно попытка сервера сформировать и обработать такой гигантский массив данных в оперативной памяти приводит к «жору» ресурсов и зависанию всей системы.
Первое, что мы должны сделать — это указать системе, как именно сопоставлять данные из разных регистров. В данном случае связующим звеном выступает поле Номенклатура. Рассмотрим исправленный вариант с использованием ЛЕВОГО СОЕДИНЕНИЯ:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Цены.Цена КАК Цена,
Остатки.ВНаличииОстаток КАК ВНаличии
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки КАК Остатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Остатки.Номенклатура = Цены.Номенклатура
Почему это важно: при использовании ЛЕВОЕ СОЕДИНЕНИЕ система возьмет записи об остатках и для каждой конкретной позиции найдет соответствующую цену. Если цены на товар нет, в поле Цена будет подставлено значение NULL, но при этом количество строк останется равным количеству товаров на остатках, а не увеличится в геометрической прогрессии.
В конфигурациях на платформе 1С 8.3 крайне важно правильно работать с виртуальными таблицами, такими как СрезПоследних и Остатки. Распространенная ошибка — накладывать фильтры в секции ГДЕ. Посмотрим на разницу.
Если мы напишем ГДЕ Цены.ВидЦены = &ВидЦены, то система сначала выберет все последние цены для всей номенклатуры базы данных, соединит их с остатками и только потом отфильтрует нужный вид цены. Правильный подход — передать фильтр непосредственно в параметры виртуальной таблицы.
Проанализируем структуру параметров:
Для УТ 11 это критично, так как цены хранятся в разрезе видов цен. Без фильтра по виду цены запрос вернет по одной строке для каждого вида цены на каждый товар, что опять приведет к дублированию строк в итоговой таблице.
Для достижения максимальной производительности, особенно в крупных базах данных, рекомендуется использовать временные таблицы (ВТ). Это позволяет разбить сложный запрос на простые этапы, которые SQL-серверу легче оптимизировать.
Разберем алгоритм по шагам:
Номенклатура.Рассмотрим пример кода:
ВЫБРАТЬ
СвободныеОстаткиОстатки.Номенклатура КАК Номенклатура,
СвободныеОстаткиОстатки.ВНаличииОстаток КАК ВНаличии
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(&Период, ) КАК СвободныеОстаткиОстатки
;
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
ВЫБРАТЬ
ВТ_Остатки.Номенклатура КАК Номенклатура,
ВТ_Остатки.ВНаличии КАК ВНаличии,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
ВТ_Остатки КАК ВТ_Остатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&Период,
Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_Остатки)
И ВидЦены = &ВидЦены
) КАК Цены
ПО ВТ_Остатки.Номенклатура = Цены.Номенклатура
Что мы здесь сделали:
ВТ_Остатки, ограничив выборку только товарами в наличии.ИНДЕКСИРОВАТЬ ПО, что позволяет SQL-серверу использовать механизм Index Seek вместо медленного полного сканирования таблицы при соединении.СрезПоследних мы использовали условие Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_Остатки). Это гарантирует, что 1С не будет вычислять цены для всего справочника номенклатуры (который может содержать сотни тысяч позиций), а ограничится только нужным списком.Помните, что в 1С:Управление торговлей 11 учет практически всегда ведется в разрезе характеристик. Если вы просто соедините данные по номенклатуре, но проигнорируете характеристики, вы получите неверные данные (цены могут «размножиться» на остатки разных цветов или размеров).
Проанализируем правильное условие связи при наличии характеристик:
ПО ВТ_Остатки.Номенклатура = Цены.Номенклатура И ВТ_Остатки.Характеристика = Цены.Характеристика
Если характеристики не используются, убедитесь, что в запросе не выбираются пустые ссылки на характеристики, которые могут влиять на группировку данных.
Подводя итог: «подвисание» базы — это почти всегда признак того, что сервер выполняет лишнюю работу. Использование временных таблиц, правильная фильтрация в параметрах виртуальных таблиц и обязательное использование условий ПО в соединениях позволят вашим запросам отрабатывать за доли секунды даже на очень больших объемах данных.