gifts2017

Оптимизация штатных запросов 7.7

Опубликовал Владислав Чинючин (vcv) в раздел Программирование - Практика программирования

1С 7.7 выполняет запросы просто и бездумно - все что сказали, все и сделает. Никаких попыток оптимизации. Если в запросе есть переменная, например
"СвойствоПоставщика=Регистр.ПартииНаличие.Партия.Поставщик.ОсновноеСвойство;"
произойдет обращение к справочникам контрагентов и значений свойств, даже если переменная СвойствоПоставщика не используется ни в условиях, ни в группировках. Что оборачивается потерей быстродействия.

1С 7.7 выполняет запросы просто и бездумно - все что сказали, все и сделает. Никаких попыток оптимизации. Если в запросе есть переменная, например
"СвойствоПоставщика=Регистр.ПартииНаличие.Партия.Поставщик.ОсновноеСвойство;"
произойдет обращение к справочникам контрагентов и значений свойств, даже если переменная СвойствоПоставщика не используется ни в условиях, ни в группировках. Что оборачивается потерей быстродействия.

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

Кардинальным методом решения проблемы могут явиться, например, прямые запросы, но и с использованием штатных возможностей можно ускорить выполнение запросов.

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

Простенькая реализация "оптимизации" текста запроса встречается и в типовых конфигурациях. Например, в ТиС это выглядит примерно так (отчет "ОтчетПоПроектам"):

    ЕстьЗначение = 0;
    Если (ВыводитьЗаявки = 1) или (ВыводитьЗаказы = 1) Тогда
        ТекстЗапроса = ТекстЗапроса + "
            |Валюта = ";
    КонецЕсли;
    Если ВыводитьЗаявки = 1 Тогда
        ТекстЗапроса = ТекстЗапроса + "Регистр.Заявки.ДоговорПокупателя.ВалютаВзаиморасчетов";
        ЕстьЗначение = 1;
    КонецЕсли;

Если валюта нам в запросе не нужна, переменная не добавляется и не будет лишнего обращения к реквизитам договора.

Метод действенный, но, к сожалению, не универсальный и неудобный, требующий больших переделок всех отчетов. Более удобно анализировать текст запроса перед выполнением запроса, когда уже есть все условия и группировки, и выкидывать из него ненужные строки.

Примерно так:

В каждый отчет вставляем перед выполнением запроса следующую строчку:

глПередВыполнениемЗапроса(Контекст, Запрос, ТекстЗапроса,"");

А в глобальный модуль добавляется функция:

Процедура глПередВыполнениемЗапроса(Конт, Запрос, ТекстЗапроса, стрСписокФиксированныхПеременных="") Экспорт
    Перем КоличествоСтрок, НомерСтроки, Позиция, ИмяПеременной;
    Перем СписокСтрокЗапроса, СписокУдаляемыхПеременных, СписокФиксированныхПеременных;
    Перем Строчка;
    Если стрСписокФиксированныхПеременных<>"*" Тогда
        // Если Константа.ЗапретитьОптимизациюТекстаЗапроса<>1 Тогда
         СписокФиксированныхПеременных = глРазделитьВСписок(стрСписокФиксированныхПеременных);
            //
            // Убираем комментарии из текста запроса
            //
            КоличествоСтрок = СтрКоличествоСтрок(ТекстЗапроса);
            времТекстЗапроса = "";
            Для НомерСтроки = 1 По КоличествоСтрок Цикл
                Строчка = СтрПолучитьСтроку(ТекстЗапроса,НомерСтроки);
                Позиция = Найти(Строчка,"//");
                Если Позиция<>0 Тогда
                    Строчка = Прав(Строчка,Позиция-1);
                КонецЕсли;
                Если ПустаяСтрока(Строчка)=Тогда
                    времТекстЗапроса = времТекстЗапроса + Строчка + РазделительСтрок;
                КонецЕсли;
            КонецЦикла;
            ТекстЗапроса = времТекстЗапроса;
            //
            // Текст запроса преобразуется к такому виду, что бы одна строка запроса (до точки с запятой)
            // соответствовала одной строке в тексте.
            //
            ТекстЗапроса = СтрЗаменить(ТекстЗапроса,РазделительСтрок," ");
            ТекстЗапроса = СтрЗаменить(ТекстЗапроса,";",";"+РазделительСтрок);
            //
            // Разбиваем текст запроса на отдельные строки с выделением имен переменных
            //
            КоличествоСтрок = СтрКоличествоСтрок(ТекстЗапроса);
            СписокСтрокЗапроса = СоздатьОбъект("СписокЗначений");
            СписокУдаляемыхПеременных = СоздатьОбъект("СписокЗначений");
            Для НомерСтроки = 1 По КоличествоСтрок Цикл
                Строчка = СтрПолучитьСтроку(ТекстЗапроса,НомерСтроки);
                Позиция = Найти(Строчка,"=");
                ИмяПеременной = "";
                Если Позиция<>0 Тогда
                    ИмяПеременной = СокрЛП(Лев(Строчка,Позиция-1));
                    Если (Найти(ИмяПеременной," ")<>0) ИЛИ (Найти(ИмяПеременной,"(")<>0) Тогда
                        ИмяПеременной = "";
                    ИначеЕсли СписокФиксированныхПеременных.НайтиЗначение(ИмяПеременной)=Тогда
                        СписокУдаляемыхПеременных.ДобавитьЗначение(ИмяПеременной);
                    КонецЕсли;
                КонецЕсли;
                СписокСтрокЗапроса.ДобавитьЗначение(ИмяПеременной,Строчка);
            КонецЦикла;
            //
            // Формируем список удаляемых переменных, то есть тех, которые не участвуют в функциях и
            // группировках запроса
            //
            КоличествоСтрок = СписокСтрокЗапроса.РазмерСписка();
            Для НомерСтроки = 1 По КоличествоСтрок Цикл
                ИмяПеременной = СписокСтрокЗапроса.ПолучитьЗначение(НомерСтроки, Строчка);
                Если ПустоеЗначение(ИмяПеременной)=Тогда
                    Позиция = 1;
                    Пока Позиция <= СписокУдаляемыхПеременных.РазмерСписка() Цикл
                        Если Найти(ВРег(Строчка),ВРег(СписокУдаляемыхПеременных.ПолучитьЗначение(Позиция)))<>Тогда
                            СписокУдаляемыхПеременных.УдалитьЗначение(Позиция);
                        ИначеЕсли (СписокУдаляемыхПеременных.ПолучитьЗначение(Позиция)="ТекущийДокумент") И (Найти(ВРег(Строчка),"ГРУППИРОВКА")<>0) И (Найти(ВРег(Строчка)," ДОКУМЕНТ")<>0) Тогда
                            СписокУдаляемыхПеременных.УдалитьЗначение(Позиция);
                        Иначе
                            Позиция = Позиция + 1;
                        КонецЕсли;
                    КонецЦикла;
                    Если СписокУдаляемыхПеременных.РазмерСписка()=Тогда
                        Прервать;
                    КонецЕсли;
                КонецЕсли;
            КонецЦикла;
            //
            // Формируем текст запроса с выкидыванием неиспользуемых переменных
            //
            ТекстЗапроса = "";
            КоличествоСтрок = СписокСтрокЗапроса.РазмерСписка();
            Для НомерСтроки = 1 По КоличествоСтрок Цикл
                ИмяПеременной = СписокСтрокЗапроса.ПолучитьЗначение(НомерСтроки, Строчка);
                Если ПустоеЗначение(ИмяПеременной)=Тогда
                    ТекстЗапроса = ТекстЗапроса + Строчка + РазделительСтрок;
                ИначеЕсли СписокУдаляемыхПеременных.НайтиЗначение(ИмяПеременной)=Тогда
                    ТекстЗапроса = ТекстЗапроса + Строчка + РазделительСтрок;
                КонецЕсли;
            КонецЦикла;
        // КонецЕсли
    КонецЕсли;
КонецПроцедуры // глПередВыполнениемЗапроса

В первое время желательно еще добавить константу "ЗапретитьОптимизациюТекстаЗапроса" и раскомментарить условие с ней. Это позволит, когда вылезет ошибка в формировании какого-нибудь отчета, временно запретить оптимизацию и неспешно разбираться, какую же переменную запроса нужно включить в исключения.

Результат:

Выполнение запросов ускоряется, ускорение зависит от реально используемых в запросе переменных и группировок, наибольшее ускорение достигается, если отчет формируется без подробностей, сводно.

По факту, в моей базе, формирование ведомости по партиям с групировками Фирма/Поставщик/Склад ускорилось в три раза во сравнению с неоптимизированным запросом. При использовании группировок типа Партия или Документ эффект от оптимизации почти пропадает. В общем, все зависит от переменных, которых вы надобавляли в свой запрос. Если у вас много группировок вида "СвойствоПоставщика=Регистр.ПартииНаличие.Партия.Поставщик.ОсновноеСвойство", эффект от оптимизации может быть огромным.

P.S.

В некоторых отчетах используются дополнительные переменные запроса, не участвующие ни в условиях, ни в группировках. Такие переменные необходимо включить в исключения, что бы оптимизация не удалила их из текста запроса.

Например, в штатном отчете ТиС "Отчет по продажам ТМЦ" вызов процедуры оптимизации текста запроса должен выглядеть так:

глПередВыполнениемЗапроса(Контекст, Запрос, ТекстЗапроса,"ДатаДокумента");

P.P.S.

Дополнительные функции. глРазделить() обычно есть в типовых, глРазделитьВСписок() обычно нет или по другому называется.

//******************************************************************************
//    глРазделить(Стр,Разделитель)
//
//    Параметры:
//        Стр            - строка, которую необходимо разложить на подстроки
//        Разделитель - строка-разделитель, по умолчанию - запятая
//
//    Возвращаемое значение:
//        Многострочная строка, составленная из подстрок
//
//    Описание:
//        Разбивает строку на многострочную строку по разделителю
//
Функция глРазделить(Знач Стр,Разделитель=",") Экспорт
    Если Разделитель=" " Тогда
        Пока Найти(Стр,"  ")>Цикл
            Стр = СтрЗаменить(Стр,"  "," ")
        КонецЦикла
    КонецЕсли;
    Возврат СтрЗаменить(Стр,Разделитель,РазделительСтрок)
КонецФункции // глРазделить()

//******************************************************************************
//    глРазделитьВСписок(Стр,Разделитель)
//
//    Параметры:
//        Стр            - строка, которую необходимо разложить на подстроки
//        Разделитель - строка-разделитель, по умолчанию - запятая
//
//    Возвращаемое значение:
//        Список значений, составленный из подстрок
//
//    Описание:
//        Разбивает строку в список значений по разделителю
//
Функция глРазделитьВСписок(Знач Стр,Разделитель=",",ПризнакПометки="") Экспорт
    Перем Текст, Номер, Список, Строчка;
    Текст = глРазделить(Стр,Разделитель);
    Список = СоздатьОбъект("СписокЗначений");
    Если ПустоеЗначение(Стр)=Тогда
        Для Номер=По СтрКоличествоСтрок(Текст) Цикл
            Строчка = СокрЛП(СтрПолучитьСтроку(Текст,Номер));
            Если ПустоеЗначение(Строчка)=Тогда
                Если ПустаяСтрока(ПризнакПометки)=Тогда
                    Список.ДобавитьЗначение(Строчка);
                ИначеЕсли Лев(Строчка,СтрДлина(ПризнакПометки)) <> ПризнакПометки Тогда
                    Список.ДобавитьЗначение(Строчка);
                Иначе
                    Список.ДобавитьЗначение(СокрЛП(Сред(Строчка,СтрДлина(ПризнакПометки)+1)));
                    Список.Пометка(Список.РазмерСписка(),1);
                КонецЕсли;
            КонецЕсли;
        КонецЦикла;
    КонецЕсли;
    Возврат Список;
КонецФункции // глРазделитьВСписок()

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Александр Рытов (Арчибальд) 26.01.10 15:08
Слегка перефразируя глубокомысленное замечание из топика, получим:
Если в тело длинного цикла напихать всякой ненужной хрени, то быстродействие снизится.
От же однако... :o Кто бы мог подумать... :o
2. Епрст (Ёпрст) 26.01.10 15:18
Ну и для универсальности.. выкинул бы из текста
глРазделитьВСписок, Константа.ЗапретитьОптимизациюТекстаЗапроса

3. vkr (vkr) 26.01.10 15:18
(0) Ващще-то, "Скальпель Оккама"... ;)
Но, все равно - автору спасибо за обозначение и попытку решения проблемы!
4. Епрст (Ёпрст) 26.01.10 15:21
+2 Ну и непонятно, накой туда контекст передавать, ежели он не используется там вообще.
5. Епрст (Ёпрст) 26.01.10 15:36
+4 И сам Запрос - тоже...
А так... +
6. Владислав Чинючин (vcv) 26.01.10 15:50
(2) Функция глРазделитьВСписок вполне претендует на универсальную нужность, поэтому просто добавил в статью. Примечание, зачем нужна константа, тоже.
(4), (5) Привычка везде передавать контекст. Вдруг понадобится. По принципу, что любая функция более-менее уважающая себя функция должна знать, в каком контексте выполняется. Внутреннее соглашение о передаче параметров между сам-с-собой :-)
7. Епрст (Ёпрст) 26.01.10 16:03
(6) проще не вводить константу, а пользовать turbomd...
8. Владислав Чинючин (vcv) 26.01.10 20:07
(7) Тот, кто пользуется turbomd, догадается сам, что ему лучше. А для того, кто не пользуется, советовать незнакомую внешнюю компоненту вместо одной константы... Гм... :)
А еще бывает РБД. Я вот побаиваюсь экспериментировать с turbomd в распределенке.
9. Епрст (Ёпрст) 27.01.10 09:00
(8) А чего там такого в распределенке ?
с помощью turbomd ты ж не делаешь реструктуризацию базы, всего лишь "точишь" мд и исправляешь ошибки.
А переслать пару текстовых файликов в перефирийки не составляет труда..или обновленный мд
10. Аркадий Кучер (Abadonna) 27.01.10 15:37
А мне понравилось:
Метод девственный

Метод - целка? :D
11. Владислав Чинючин (vcv) 27.01.10 15:42
(10) Глазастый :D
По крайней мере я девственности этот метод не лишал, предпочитаю секс с более симпатичными алгоритмами, чем типовые от 1С :)
12. Аркадий Кучер (Abadonna) 27.01.10 16:01
(11) Тогда уж не с алгоритмами, а с интерфейсами ;))))
13. Михаил Семенов (Shaman100M) 27.01.10 18:27
заочно, плюс за идею. Все конструкции языка обрабатывает? и когда(А=Б) тоже?

(12) интерфейсы это лица заграничной национальности? :) тогда все логично.
14. Владислав Чинючин (vcv) 27.01.10 19:17
(13) Никаких конструкций языка. Все очень просто и тупо. Выделяются строки запроса с переменными - строки, в которых есть символ "равно" и до символа "равно" нет круглой скобки. Все остальные строки тупо проверятся на наличие имен переменных. Это не обеспечивает 100% выкидывания переменной из запроса (переменная, названная "а" никогда не выкинется), но для подавляющего большинства запросов, в которых не встречается коротких имен переменных, этого хватает.
В конце концов, у меня статья с идеей и примером кода, а не продакшин-решение. :)
15. Аркадий Кучер (Abadonna) 27.01.10 19:22
(0) Подсказываю тебе давно мной применяемый метод разбиения по любому разделителю:
Стр=СтрЗаменить(Стр,КакойХошьРазделитель,РазделительСтрок)
как миленькие многострочными становятся ;)
16. Владислав Чинючин (vcv) 27.01.10 19:53
(15) А кого, собственно говоря ты хочешь делать многострочным? Как-то не уловил глубины мысли в твоем комментарии.
17. Аркадий Кучер (Abadonna) 27.01.10 20:01
(16) Ха, я на эту не глянул:
 Возврат СтрЗаменить(Стр,Разделитель,РазделительСтрок)

Раньше в типовых ТАК по уродски разделяли, что я и подумал, что и там тоже по уродски...
18. baa (baa) 28.01.10 13:39
Автору заслуженный плюс. Очень нужный алгоритм, особенно в универсальных отчетах, где в нагрузку идет еще куча переменных. Давно над этим задумывался. Обычно сам текст запроса формирую методом исключения, но это действительно красиво получается. СПАСИБО
19. Станислав (zalst) 29.01.10 15:41
хоть и неактуально для меня - понравилось, поддерживаю константу, вместо турбомд и т.п.

p.s. передача контекста тоже здраво(как по мне), во многих случаях позволяет оптимизировать/доработать код, не меняя вызова процедуры. +1
20. Михаил Зотов (ZOMI) 01.02.10 04:00
Использовать не буду , но за идею, изложение + .
По опыту - ну десяток типовых отчетов в ТиС активно используют и ручками их поправить приятнее и надежнее ,,,
21. Иваныч Иванов (Иваныч) 08.11.13 16:43
(20) Я с вами полностью согласен