Как оптимизировать запрос и алгоритм проверки оплат для контроля сроков выставления ЭСЧФ в 1С

← На главную

При разработке алгоритмов контроля дебиторской задолженности и сроков выставления электронных счетов-фактур (ЭСЧФ) разработчики часто сталкиваются с падением производительности системы. В типовых конфигурациях (например, «1С:Бухгалтерия для Беларуси») неоптимальные выборки из регистра бухгалтерии и обработка данных во встроенном языке могут приводить к зависаниям на десятки секунд. Рассмотрим типовые ошибки в таком коде и пошагово разберем, как перевести вычисления в единый производительный пакетный запрос.

Анализ проблем исходного кода

Проанализируем типичный пример неоптимального кода, где разработчик пытается связать остатки по регистру, проводки по банку и документы реализации:

Запрос внутри цикла: Самая грубая архитектурная ошибка — выполнение Запрос2.Выполнить() для каждой строки таблицы значений. Если в выборке сотни или тысячи документов, обращение к СУБД в цикле создает колоссальную нагрузку и сетевые задержки.
Обращение через точку к составным типам: Конструкции вида СчетФактураВыданный.ДокументОснование.ВалютаДокумента или СостоянияЭД.СсылкаНаОбъект.ДокументОснование на уровне СУБД транслируются во множество тяжелых внешних соединений (LEFT JOIN) со всеми возможными таблицами конфигурации, входящими в составной тип.
Отсутствие параметров у виртуальных таблиц: Чтение РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто без явного указания периода и условий на счета приводит к чтению избыточных объемов записей и вычислению движений субконто «на лету» без использования индексов.
Избыточные расчеты дат через строки: Преобразование дат в строковые форматы с конкатенацией для получения 10-го числа следующего месяца создает лишние накладные расходы процессора.
Хранение таблицы значений в ресурсе с типом ХранилищеЗначения: Упаковка динамической таблицы в бинарные данные нарушает целостность ссылок, раздувает таблицы базы данных и лишает возможности строить выборки стандартными средствами платформы.

Шаг 1. Устраняем запрос в цикле и соединяем данные пакетом

Выясним причину выполнения второго запроса: автору требовалось получить дату и документ последнего поступления денежных средств по каждой реализации. Эту задачу необходимо решать непосредственно в запросе с помощью временных таблиц и агрегатной функции МАКСИМУМ.

Посмотрим на пример правильного получения оплат по расчетным счетам (счет 51) и авансам (счет 62.5 в корреспонденции с 62.1) без циклических вызовов СУБД:


ВЫБРАТЬ
    ХозрасчетныйДвиженияССубконто.Сумма КАК СуммаОплачено,
    ЕСТЬNULL(ХозрасчетныйДвиженияССубконто.Период, ДАТАВРЕМЯ(1, 1, 1)) КАК ДатаПоступления,
    ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.СубконтоКт3 КАК Документ.РеализацияТоваровУслуг) КАК Реализация
ПОМЕСТИТЬ ВТ_ВсеПоступления
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        &ДатаНач,
        &ДатаКон,
        (СчетДт = &СчетДт ИЛИ СчетДт = &СчетДт2) И СчетКт = &СчетКт,
        ,
    ) КАК ХозрасчетныйДвиженияССубконто
ГДЕ
    ХозрасчетныйДвиженияССубконто.СубконтоКт3 ССЫЛКА Документ.РеализацияТоваровУслуг
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Реализация КАК Реализация,
    СУММА(ВТ.СуммаОплачено) КАК СуммаОплачено,
    МАКСИМУМ(ВТ.ДатаПоступления) КАК ДатаПоследнейОплаты
ПОМЕСТИТЬ ВТ_ИтогиПоОплатам
ИЗ
    ВТ_ВсеПоступления КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Реализация
ИНДЕКСИРОВАТЬ ПО
    Реализация
;

Обратим внимание на следующие оптимизации:

Условия на счета переданы в третий параметр виртуальной таблицы, что позволяет СУБД сразу отфильтровать нужные проводки на уровне индекса регистра.
Использована явная типизация через ВЫРАЗИТЬ(...) для поля субконто, имеющего составной ссылочный тип.
Создан индекс по полю связи Реализация во временной таблице, что критично для последующих соединений.

Шаг 2. Оптимизация работы с электронными счетами-фактурами

Разберем, как получить суммы уже выставленных ЭСЧФ без медленных разыменований через регистр сведений СостоянияЭД. Поле СсылкаНаОбъект имеет составной тип, поэтому его обязательно нужно сузить перед соединением:


ВЫБРАТЬ
    СчетФактураТабличнаяЧасть.ДокументОснование КАК Реализация,
    СУММА(СчетФактура.СуммаДокумента) КАК СуммаЭСЧФ
ПОМЕСТИТЬ ВТ_ВыставленныеЭСЧФ
ИЗ
    Документ.СчетФактураВыданный.ДокументыОснования КАК СчетФактураТабличнаяЧасть
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК СчетФактура
        ПО СчетФактураТабличнаяЧасть.Ссылка = СчетФактура.Ссылка
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СостоянияЭД КАК СостоянияЭД
        ПО СчетФактураТабличнаяЧасть.Ссылка = СостоянияЭД.СсылкаНаОбъект
ГДЕ
    СчетФактура.Проведен = ИСТИНА
    И СостоянияЭД.СостояниеВерсииЭД = &СостояниеВерсииЭД
    И СостоянияЭД.ДействияСоСтороныДругогоУчастника <> "Аннулирован"
    И СчетФактураТабличнаяЧасть.ДокументОснование В
        (ВЫБРАТЬ ВТ.Ссылка ИЗ ВТ_Реализации КАК ВТ)
СГРУППИРОВАТЬ ПО
    СчетФактураТабличнаяЧасть.ДокументОснование
ИНДЕКСИРОВАТЬ ПО
    Реализация
;

Шаг 3. Расчет контрольных дат средствами языка запросов

В исходном коде присутствовали сложные преобразования для определения контрольных сроков: 10-го числа следующего месяца, 60 дней со дня отгрузки и 2 дней с момента поступления оплаты. Перенесем расчет дат в запрос с помощью стандартных функций ДОБАВИТЬКДАТЕ, НАЧАЛОПЕРИОДА и РАЗНОСТЬДАТ.

Рассмотрим правила расчета:

10-е число месяца, следующего за месяцем реализации: для вычисления сначала смещаем дату реализации на 1 месяц вперед, берем начало этого месяца и прибавляем 9 дней (так как начало месяца — это 1-е число 00:00:00). Формула в запросе: ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(Реализация.Дата, МЕСЯЦ, 1), МЕСЯЦ), ДЕНЬ, 9).
Срок 60 дней: прибавляем к дате отгрузки ровно 60 дней через ДОБАВИТЬКДАТЕ(Реализация.Дата, ДЕНЬ, 60).
Срок 2 дня с момента оплаты: прибавляем 2 дня к дате поступления через ДОБАВИТЬКДАТЕ(ВТ_ИтогиПоОплатам.ДатаПоследнейОплаты, ДЕНЬ, 2).

Итоговый оптимизированный вариант процедуры

Объединим все части в единую процедуру без циклических запросов и с минимальным объемом кода пост-обработки:


&НаСервере
Процедура ПроверкаЭСЧФ(ТекущаяДатаРасчета) Экспорт

    Запрос = Новый Запрос;
    Запрос.Текст = 
    "ВЫБРАТЬ
    |	Реализация.Ссылка КАК Реализация,
    |	Реализация.Дата КАК Дата,
    |	Реализация.Контрагент КАК Контрагент,
    |	Реализация.СуммаДокумента КАК СуммаДокумента
    |ПОМЕСТИТЬ ВТ_Реализации
    |ИЗ
    |	Документ.РеализацияТоваровУслуг КАК Реализация
    |ГДЕ
    |	Реализация.Проведен = ИСТИНА
    |	И Реализация.Дата >= &ДатаНач
    |	И Реализация.ВалютаДокумента = &ВалютаРегл
    |ИНДЕКСИРОВАТЬ ПО
    |	Реализация
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	Движения.СубконтоКт3 КАК Реализация,
    |	СУММА(Движения.Сумма) КАК Оплачено,
    |	МАКСИМУМ(Движения.Период) КАК ДатаОплаты
    |ПОМЕСТИТЬ ВТ_Оплаты
    |ИЗ
    |	РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
    |			&ДатаНач,
    |			&ДатаКон,
    |			(СчетДт = &Счет51 ИЛИ СчетДт = &Счет62_5) И СчетКт = &Счет62_1,
    |			,
    |			СубконтоКт3 В (ВЫБРАТЬ ВТ.Реализация ИЗ ВТ_Реализации КАК ВТ)) КАК Движения
    |СГРУППИРОВАТЬ ПО
    |	Движения.СубконтоКт3
    |ИНДЕКСИРОВАТЬ ПО
    |	Реализация
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	Основания.ДокументОснование КАК Реализация,
    |	СУММА(ЭСЧФ.СуммаДокумента) КАК СуммаЭСЧФ
    |ПОМЕСТИТЬ ВТ_ЭСЧФ
    |ИЗ
    |	Документ.СчетФактураВыданный.ДокументыОснования КАК Основания
    |	ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.СчетФактураВыданный КАК ЭСЧФ
    |		ПО Основания.Ссылка = ЭСЧФ.Ссылка
    |	ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СостоянияЭД КАК СостоянияЭД
    |		ПО Основания.Ссылка = СостоянияЭД.СсылкаНаОбъект
    |ГДЕ
    |	ЭСЧФ.Проведен = ИСТИНА
    |	И СостоянияЭД.СостояниеВерсииЭД = &СтатусОтправлен
    |	И СостоянияЭД.ДействияСоСтороныДругогоУчастника <> ""Аннулирован""
    |	И Основания.ДокументОснование В (ВЫБРАТЬ ВТ.Реализация ИЗ ВТ_Реализации КАК ВТ)
    |СГРУППИРОВАТЬ ПО
    |	Основания.ДокументОснование
    |ИНДЕКСИРОВАТЬ ПО
    |	Реализация
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ВТ_Р.Реализация КАК Реализация,
    |	ВТ_Р.Дата КАК ДатаРеализации,
    |	ВТ_Р.Контрагент КАК Контрагент,
    |	ВТ_Р.СуммаДокумента КАК СуммаРеализации,
    |	ЕСТЬNULL(ВТ_Опл.Оплачено, 0) КАК Оплачено,
    |	ВТ_Опл.ДатаОплаты КАК ДатаОплаты,
    |	ЕСТЬNULL(ВТ_ЭСЧФ.СуммаЭСЧФ, 0) КАК ВыставленоЭСЧФ,
    |	ВТ_Р.СуммаДокумента - ЕСТЬNULL(ВТ_ЭСЧФ.СуммаЭСЧФ, 0) КАК ОстатокДляЭСЧФ,
    |	ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(ВТ_Р.Дата, МЕСЯЦ, 1), МЕСЯЦ), ДЕНЬ, 9) КАК Дата10Числа,
    |	ДОБАВИТЬКДАТЕ(ВТ_Р.Дата, ДЕНЬ, 60) КАК Дата60Дней,
    |	ДОБАВИТЬКДАТЕ(ВТ_Опл.ДатаОплаты, ДЕНЬ, 2) КАК СрокОплатыПлюс2Дня
    |ИЗ
    |	ВТ_Реализации КАК ВТ_Р
    |	ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Оплаты КАК ВТ_Опл
    |		ПО ВТ_Р.Реализация = ВТ_Опл.Реализация
    |	ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЭСЧФ КАК ВТ_ЭСЧФ
    |		ПО ВТ_Р.Реализация = ВТ_ЭСЧФ.Реализация
    |ГДЕ
    |	ВТ_Р.СуммаДокумента - ЕСТЬNULL(ВТ_ЭСЧФ.СуммаЭСЧФ, 0) > 0";

    Запрос.УстановитьПараметр("ДатаНач", НачалоГода(ТекущаяДатаРасчета));
    Запрос.УстановитьПараметр("ДатаКон", КонецДня(ТекущаяДатаРасчета));
    Запрос.УстановитьПараметр("ВалютаРегл", Константы.ВалютаРегламентированногоУчета.Получить());
    Запрос.УстановитьПараметр("СтатусОтправлен", Перечисления.СтатусыЭД.Отправлен);
    Запрос.УстановитьПараметр("Счет51", ПланыСчетов.Хозрасчетный.РасчетныеСчета);
    Запрос.УстановитьПараметр("Счет62_1", ПланыСчетов.Хозрасчетный.РасчетыСПокупателями);
    Запрос.УстановитьПараметр("Счет62_5", ПланыСчетов.Хозрасчетный.РасчетыПоАвансамПолученным);

    ТЗРезультат = Запрос.Выполнить().Выгрузить();
    ТЗРезультат.Колонки.Добавить("КрайнийСрокВыставления", Новый ОписаниеТипов("Дата"));

    Для Каждого Стр Из ТЗРезультат Цикл
        Если ТекущаяДатаРасчета >= Стр.Дата60Дней Тогда
            Стр.КрайнийСрокВыставления = КонецДня(Стр.Дата60Дней + 2 * 86400);
        ИначеЕсли Стр.Оплачено > 0 И ЗначениеЗаполнено(Стр.ДатаОплаты) Тогда
            Если ТекущаяДатаРасчета < Стр.Дата10Числа Тогда
                Стр.КрайнийСрокВыставления = КонецДня(Стр.Дата10Числа);
            Иначе
                Стр.КрайнийСрокВыставления = КонецДня(Стр.СрокОплатыПлюс2Дня);
            КонецЕсли;
        Иначе
            Стр.КрайнийСрокВыставления = КонецДня(Стр.Дата10Числа);
        КонецЕсли;
    КонецЦикла;

КонецПроцедуры