Запрос по изменению оборотов

[Харлампий Дымба]

Есть отчетик - изменение оборотов.
Пользователь задает несколько периодов, может выколоть некоторые дни недели. Все используемые в отчете даты собираю в список значений СписокДатПолный.

Сейчас в черном запросе в 1с выглядит примерно так (полный текст формируется динамически, ниже 2 ключевые строки):

Период с СамаяНачальнаяДата по СамаяКонечнаяДата;
...
Условие(СписокДатПолный.НайтиЗначение(ДатаДок)>0);

Собственно вопросов два и они разные:

Первый (поинтереснее) - Условие(СписокДатПолный.НайтиЗначение(ДатаДок)>0) тормозное, но Условие(ДатаДок в СписокДатПолный) в 27 релизе, как известно, в SQL не работает. Может есть другой способ контроля даты по условию в списке? Ну кроме Запрос.ВключитьSQL(0) - это ещё тормознее.

Второй (ну тут наверное без вариантов): для сильно разненесенных периодов (ну типа сравниваем апрель 2025 и апрель 2024) при выполнении запроса (Период с '01.04.2024' по '30.04.2025') будут обсчитываться все функции внутри выколотого периода ('01.05.2024'-'31.03.2025'). Можно этого избежать?

Совет переписать на прямой запрос - очевиден, банален и пока нереализуем, так как есть более насущные кандидаты и другие задачи.
Задача в том, чтобы малыми силами разрулить этот запрос в рамках черного и без переписывания всего отчета.

[vk_barnaul]

Есть отчетик - изменение оборотов.
Пользователь задает несколько периодов, может выколоть некоторые дни недели. Все используемые в отчете даты собираю в список значений СписокДатПолный.
...
Задача в том, чтобы малыми силами разрулить этот запрос в рамках черного и без переписывания всего отчета.
...

Из вопроса не понятно какие результаты ты получаешь, но я бы лучше выполнил несколько запросов перебирая СписокДатПолный, и на каждом этапе копируя все результаты в какую-то другую структуру. Я бы выбрал типа ИнксированногоСписка, но это если получаемые результаты возможно как-то однозначно индексировать для разных периодов.

[Пиит]

1.Можно попробовать список дат заменить строкой и использовать условие "Строка(Дата) В ПолныйСписокСтр".
2. Для функций добавить условие Когда дата входит нужные периоды, но врятли это заметно ускорит запрос.

[Злоп]

"Условие(ДатаДок в СписокДатПолный) в 27 релизе, как известно, в SQL не работает."
- это откуда такие сведения? это именно для типов ДАТА или для всех?
вроде как не работает только в случае когда в СЗ одно значение только задано (и точно не помню - толи в в прямм условии, то ли в НЕ(ДатаДок в СписокДатПолный").

[Злоп]

как вариант - забей на фильтрацию по периодам. собери полный массив данных, закинь в ИТЗ, а из ИТЗ через Мин-Макс значения на индекс - выкуси нужные/ненужные периоды.

[Злоп]

ИТЗ работает быстро.

[Харлампий Дымба]

К #2:
1. Тоже думал про это, но останавливала неясность в двух местах:
а)возможное ограничение длины строки запроса (судя по всему, нет), если например 3 квартала сравнивать, то получится минимум 90*9=810 символов;
б)неопределенность в работе функции Строка() для пользовательской работы настройки представления года в дате - одинаково ли будет идти преобразование в условии в запросе и при формировании строки отбора дат (судя по всему, да).
Но по итогу получилось примерно одинаково: что Строка(), что НайтиЗначение()
2. В точку. Именно так и сделано и запрос это ускоряет. В принципе, для некоторых периодов (например они идут подряд или почти подряд и не имеют выколотых дней) можно было бы вообще отказаться от проверки через Условие(СписокДатПолный.НайтиЗначение(ДатаДок)>0); - так как отбор всё-равно идёт по Когда() в функции.

К #1:
Я текст запроса динамически формирую, там произвольное число группировок, некоторые из них могут быть с группами (типа Диспетчер/Покупатель/Магазин/Товар/Документ) и я понимаю что есть другие варианты его собрать, но всё переписывать и отлаживать - займёт прилично времени, не та работа.
Просто всё было более-менее нормально по скорости и в черном запросе - когда в dbf работало Условие(ДатаДок в СписокДатПолный), а вот в sql оно не работает ни в Условие(), ни в Когда() - из-за ошибки релиза, а вызов функции НайтиЗначение() это дополнительные тормоза. Вот я и подумал, вдруг все-таки есть способ отбора по списку дат получше.

[Харлампий Дымба]

- это откуда такие сведения? это именно для типов ДАТА или для всех?

Опыт, сын ошибок трудных...
И по списку документов - тоже не работает. А вот в dbf работает. Всё про 27й релиз.

собери полный массив данных, закинь в ИТЗ

"произвольное число группировок, некоторые из них могут быть с группами (типа Диспетчер/Покупатель/Магазин/Товар/Документ)" - я смогу отрабатотать? Наверное смогу. Но умумукуюсь.

[Пиит]

Может для общего условия  вместо полного списка дат взять через НЕ список исключений? Он то меньше будет.
Правильно я понимаю, что функций столько же наборов, сколько периодов, и отличаются они через Когда?

[Харлампий Дымба]

Может для общего условия  вместо полного списка дат взять через НЕ список исключений? Он то меньше будет.

В общем случае, наверное, может быть и сильно больше - при сравнении марта 2025 и марта 2024, например. Но заход интересный, не помню проверял ли работоспособность Условие (Не(Дата в СписокИсключаемыхДат)) в SQL - надо будет глянуть.
Щас, пар сек.

[Харлампий Дымба]

Не, база занята. Ладно, пока есть с чем поработать и над чем подумать, спасибо!
Опять же - вот не понимаю я - в целом СписокЗначений::Принадлежит работает гораздо медленнее, чем СписокЗначений::НайтиЗначение и я им практически не пользуюсь. Но именно в данном случае похоже получается, что
Условие(СписокДатПолный.Принадлежит(ДатаДок)=1); быстрее чем Условие(СписокДатПолный.НайтиЗначение(ДатаДок)>0); возможно на нём и остановлюсь, если  Условие (Не(Дата в СписокИсключаемыхДат))  не сработает в SQL.

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

Код Выделить Развернуть

Процедура Сформировать()
    Перем НачДата[4], КонДата[4], СписокДатПериодов[4];//количество периодов можно изменить
    
    СписокДатПолный = СоздатьОбъект("СписокЗначений");
    СписокДатСтрокой = "";
    СписокИсключаемыхДат = СоздатьОбъект("СписокЗначений");

    ВсегоПериодов = Разм(НачДата);
    Для СчПериодов = 1 По ВсегоПериодов Цикл//1й квартал каждого года (по количеству периодов) до 2025
        НачДата[СчПериодов] = Дата(2025-ВсегоПериодов+СчПериодов,1,1);
        КонДата[СчПериодов] = Дата(2025-ВсегоПериодов+СчПериодов,3,31);
        СписокДатПериодов[СчПериодов] = СоздатьОбъект("СписокЗначений");
        Для ДатаПром = НачДата[СчПериодов] По КонДата[СчПериодов] Цикл
            Если НомерДняНедели(ДатаПром) = 1 Тогда Продолжить КонецЕсли;//я бы понедельники взял и отменил
            СписокДатПолный.ДобавитьЗначение(ДатаПром);
            СписокДатСтрокой = СписокДатСтрокой + ";" + Строка(ДатаПром);
            СписокДатПериодов[СчПериодов].ДобавитьЗначение(ДатаПром);
        КонецЦикла;    
    КонецЦикла;    
    Для ДатаПром = НачДата[1] По КонДата[ВсегоПериодов] Цикл
        Если СписокДатПолный.НайтиЗначение(ДатаПром)=0 Тогда
            СписокИсключаемыхДат.ДобавитьЗначение(ДатаПром);
        КонецЕсли;
    КонецЦикла;    
    
    //вид документа (РасходнаяНакладная) и реквизиты (Товар и Сумма) можно поменять под свою конфигурацию
    Запрос = СоздатьОбъект("Запрос");
    ТекстЗапроса = "
    |Период с '"+НачДата[1]+"' по '"+КонДата[ВсегоПериодов]+"';
    |Товар = Документ.РасходнаяНакладная.Товар;
    |СуммаДок = Документ.РасходнаяНакладная.Сумма;
    |ДатаДок = Документ.РасходнаяНакладная.ДатаДок;";
    Для СчПериодов=1 По ВсегоПериодов Цикл
        ТекстЗапроса = ТекстЗапроса + "
        |Функция Сумма"+СчПериодов+" = Сумма(СуммаДок) когда ((ДатаДок >= '"+НачДата[СчПериодов]+"') и (ДатаДок <= '"+КонДата[СчПериодов]+"'));";
        //|Функция Сумма"+СчПериодов+" = Сумма(СуммаДок) когда (ДатаДок в СписокДатПериодов["+СчПериодов+"]);";//так не работает: "Переменная не объявлена как массив"
    КонецЦикла;    
    ТекстЗапроса = ТекстЗапроса + "
    |Группировка Товар без Упорядочивания;
    |Группировка Документ;
    //|Условие(ДатаДок в СписокДатПолный); // - скорость 55855 / не работает в SQL 7.70.027, но можно включить работу с помощью Запрос.ВключитьSQL(0), а это замедляет
    //|Условие(Строка(ДатаДок) в СписокДатСтрокой); // - скорость 77031 / вариант Пиита
    //|Условие(НЕ(ДатаДок в СписокИсключаемыхДат)); // - скорость 60247 / 2й вариант Пиита
    //|Условие(СписокДатПолный.НайтиЗначение(ДатаДок)>0); // - скорость 90510 / текущий вариант
    //|Условие(СписокДатПолный.Принадлежит(ДатаДок)=1); // - скорость 63983 // ещё один вариант
    |";
    //можно не задавать Условие(), но тогда таблица запроса будет пухнуть ненужными товарами/документами, 
    //чтобы потом сдуться обратно при вычисление функции с условием Когда()
    //и к тому же тогда нельзя исключить опеределенные дни (понедельники, например) из общего списка - скорость 70827

    Сообщить(ТекстЗапроса);
    
    Время1 = _GetPerformanceCounter(); 
    Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда Возврат КонецЕсли;
    Время2 = _GetPerformanceCounter();
    Сообщить("Время формирования запроса: "+Число(Время2 - Время1));

    ТЗ=СоздатьОбъект("ТаблицаЗначений");    
    Запрос.Выгрузить(ТЗ);
    ТЗ.ВыбратьСтроку();

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

[Пиит]

Так может условие для ДатыДок разбить на два, одно простое прописать периодами через ИЛИ, а во втором - список исключаемых дат, где оставить только понедельники?

[Харлампий Дымба]

Чем дальше в лес, тем больше дров..

Код Выделить Развернуть

|Условие(";
Для СчПериодов=1 По ВсегоПериодов Цикл
	ТекстЗапроса = ТекстЗапроса + ?(СчПериодов=1,""," или ")+"(ДатаДок >= '"+НачДата[СчПериодов]+"') и (ДатаДок <= '"+КонДата[СчПериодов]+"')";
КонецЦикла;
ТекстЗапроса = ТекстЗапроса + ");

Работает даже чуть быстрее, чем Условие(ДатаДок в СписокДатПолный). То есть, если выколотых дат нет, то условие периоду лучше составлять по ИЛИ.

А вот если надо исключить выколотые даты, тут сложнее. Пусть Условие1 - условие по периодам через ИЛИ, а Условие2 - уточняющее условие на выколотые даты - Условие(СписокДатПолный.Принадлежит(ДатаДок)=1)

Навскидку не скажу есть ли разница между

Условие1
Условие2

и

Условие2
Условие1

То есть, с одной стороны условия в запросе выполняются по И. С другой - 1С любит рассчитывать все условия по И до конца, независимо от того, выполнено или нет Условие1. С третьей стороны - ИТС говорит, что во время выборки выполняются только элементарные условия (то есть не содержащие функций языка), т.е., если я правильно понял, Условие1 будет обсчитываться во время выборки, а Условие2 в постобработке таблицы запроса. А с четвертой стороны - у SQL и DBF могут быть свои заморочки с планом выполнения запроса.

[Пиит]

Подкину еще дров.
Можно все понедельники перечислить в запросе как элементарные условия ДатаДок<>'..'

[Харлампий Дымба]

Можно все понедельники перечислить в запросе как элементарные условия ДатаДок<>'..'

Не) Это прям уже совсем... Как пример, 3 сравниваемых квартала, в которых нужны продажи по выходным, это прям будет паровоз из уходящих в даль ДатаДок<>