Выборка в запросе из регистра сведений данных на дату из строки запроса. Оптимизация.

18.05.10

Разработка - Запросы

Эта статья является продолжением моей публикации http://infostart.ru/public/21181/. В работе я столкнулся с тем, что данная конструкция работает, но медленно. Когда дело касается 20-100 строк, то о времени выполнения запроса не задумываешься. Но когда в тексте запроса около 2000 строк, которые обрабатывают массивы данных около 2000+ строк на 50+ столбцов, то итоговое время заставляет задуматься. Данная статья сделана не для гуру по запросам в 1С8, она рассчитана скорее на начальный и средний уровни.

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

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

За основу я возьму запрос с временными таблицами, так как по моему мнению, так будет более понятно.


ВЫБРАТЬ
    Договор.Сумма КАК Сумма,
    Договор.Ссылка КАК Ссылка,
    МАКСИМУМ(Руководители.Период) КАК Период,
    Договор.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ РуководителиДоговоров 
ИЗ
    Документ.Договор КАК Договор
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Руководители КАК Руководители
        ПО Договор.Сотрудник = Руководители.Сотрудник
            И Договор.Дата >= Руководители.Период
ГДЕ
    Договор.Дата МЕЖДУ &Дата1 И &Дата2

СГРУППИРОВАТЬ ПО
    Договор.Сумма,
    Договор.Ссылка,
    Договор.Сотрудник

ВЫБРАТЬ
    РуководителиДоговоров.Ссылка КАК Договор,
    РуководителиДоговоров.Сумма,
    Руководители.Руководитель
ИЗ
    РуководителиДоговоров КАК РуководителиДоговоров
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Руководители КАК Руководители
        ПО РуководителиДоговоров.Период = Руководители.Период
            И РуководителиДоговоров.Сотрудник = Руководители.Сотрудник


Если мы посмотри на сам запрос, то становится понятно, что большие порции информации будут обрабатываться в соединениях. Уменьшив количество данных в соединениях мы существенно ускорим выполнение запроса.

Для этого в контексте нашего запроса необходимо получить на каждую дату и каждого сотрудника его руководителя. С большое вероятностью можно сказать, что в один день сотрудник сделает более 1 продажи, а руководитель назначается не ежесекундно, а как минимум не чаще, чем раз в день.

Выбираем из таблицы договоров только конец дня даты договора и сотрудника с инструкцией РАЗЛИЧНЫЕ, чтобы избежать дублирование строк. Таким образом получим таблицу, которая будет содержать данные, когда и у какого сотрудника были продажи. По этой таблице получаем для каждой строки руководителя для сотрудника на указанную дату. В результате получаем таблицу: дата продажи, сотрудник, руководитель. В итоге связываем таблицу договоров с полученной таблицей.

Вот итоговый запрос:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    КОНЕЦПЕРИОДА(Договор.Дата, ДЕНЬ) КАК Дата,
    Договор.Сотрудник
ПОМЕСТИТЬ ДатыДоговоров
ИЗ
    Документ.Договор КАК Договор
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ДатыДоговоров.Дата,
    ДатыДоговоров.Сотрудник,
    МАКСИМУМ(Руководители.Период) КАК Период
ПОМЕСТИТЬ ПериодРуководителя
ИЗ
    ДатыДоговоров КАК ДатыДоговоров
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Руководители КАК Руководители
        ПО ДатыДоговоров.Дата >= Руководители.Период
            И ДатыДоговоров.Сотрудник = Руководители.Сотрудник

СГРУППИРОВАТЬ ПО
    ДатыДоговоров.Дата,
    ДатыДоговоров.Сотрудник
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ПериодРуководителя.Дата,
    ПериодРуководителя.Сотрудник,
    Руководители.Руководитель
ПОМЕСТИТЬ РуководителиПоДатам
ИЗ
    ПериодРуководителя КАК ПериодРуководителя
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Руководители КАК Руководители
        ПО ПериодРуководителя.Период = Руководители.Период
            И ПериодРуководителя.Сотрудник = Руководители.Сотрудник
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Договор.Ссылка КАК Договор,
    Договор.Сумма,
    РуководителиПоДатам.Руководитель
ИЗ
    Документ.Договор КАК Договор
        ЛЕВОЕ СОЕДИНЕНИЕ РуководителиПоДатам КАК РуководителиПоДатам
        ПО КОНЕЦПЕРИОДА(Договор.Дата, ДЕНЬ) = РуководителиПоДатам.Дата
            И Договор.Сотрудник = РуководителиПоДатам.Сотрудник


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

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

Спасибо за внимание.

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 8000 руб.

02.09.2020    122348    673    389    

716

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5755    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2006    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6300    31    mkalimulin    25    

50

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1749    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5397    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16198    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. hren 19.05.10 02:39 Сейчас в теме
Ничегоне понял, но очень интересно :)
2. ДимокШ 7 19.05.10 07:11 Сейчас в теме
Типичные причины неоптимальной работы запросов и методы оптимизации
Автор: Рупасов Константин (1С, Москва) http://kb.1c.ru/articleView.jsp?id=44#subquery_in_on
5. Ivon 673 19.05.10 09:42 Сейчас в теме
(2). Не читал, к сожалению, нет доступа.
(3). По поводу оптимизации для разной техники и баз согласен, но в большинстве случаев такая оптимизация даст прирост в скорости выполнения.
(4). В твоем примере вначале произойдет соединение, а после группировка. Смысл оптимизации в том, чтобы между соединениями был как можно меньший объем данных.
6. DoctorRoza 19.05.10 09:53 Сейчас в теме
(5) Пу почему же? Идея была в том, чтобы одним запросом, из текущего документа по ссылке выбрать Сотрудников и Даты! То есть тут и так будет минимум данных с учетом, что и регистр сведений тоже будет содержать параметры, а именно Сотрудников/Даты из текущего документа!
P.s. Может сказал глупость, но я только начинаю разбираться .. новичок! :oops:
7. Ivon 673 19.05.10 10:25 Сейчас в теме
(6). А зачем нужно
ГДЕ

Договор.Ссылка = СсылкаДокумента
?
8. alexk-is 6533 19.05.10 11:42 Сейчас в теме
(7) Видимо ещё не разобрался :)
9. alexk-is 6533 19.05.10 13:37 Сейчас в теме
(6) Исключительно для тренировки предлагаю поразбираться с запросами, например, тут http://www.infostart.ru/public/68269/ и тут http://www.infostart.ru/public/69707/ :)
10. Ivon 673 19.05.10 14:34 Сейчас в теме
(9). С календарем интересное решение.
3. alexk-is 6533 19.05.10 07:12 Сейчас в теме
(0) Методы оптимизации очень сильно зависят от техники, состава данных, состояния базы данных. В убитой горем базе данных сканирование может дать больший эффект. Для понимания достигнутого эффекта не хватает замеров производительности до и после, а также параметров техники и базы.

Приведенный пример очень напоминает получение установленной цены для сравнения с фактической ценой продажи. Помнится задача решалась проще.

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

Примеры запросов по теме цен на Инфостарте в своё время очень активно обсуждались.
4. DoctorRoza 19.05.10 09:27 Сейчас в теме
А вот вопрос .. нужна ли тут первая временная таблица? Допустим

ВЫБРАТЬ РАЗЛИЧНЫЕ
КОНЕЦПЕРИОДА(Договор.Дата, ДЕНЬ) КАК Дата,
Договор.Сотрудник,
МАКСИМУМ(Руководители.Период) КАК Период
ПОМЕСТИТЬ ПериодРуководителя
ИЗ
Документ.Договор КАК Договор
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Руководители КАК Руководители
ПО Договор.Дата >= Руководители.Период
И Договор.Сотрудник = Руководители.Сотрудник
ГДЕ

Договор.Ссылка = СсылкаДокумента

СГРУППИРОВАТЬ ПО
Договор.Дата,
Договор.Сотрудник
;

То есть выберем по ссылке документа различных сотрудников, объединим с регистром и сгруппируем.
11. MoneG2 27 19.05.10 18:04 Сейчас в теме
> запросы с временными таблицами выполняются медленнее

а вы уже пробовали (хотя бы) индексировать поля ВТ, по которым потом соединяете таблицы?
12. Ivon 673 19.05.10 18:16 Сейчас в теме
(11). Честно говоря нет. А вы считаете, что на индексирование не будет потрачена часть времени? И куда складывается информация из временных таблиц?
13. alexk-is 6533 19.05.10 18:33 Сейчас в теме
(12) На индексирование обязательно будет потрачена часть времени, но это окупится сторицей на соединениях по индексу.
14. Ivon 673 19.05.10 18:48 Сейчас в теме
(13). Возможно, не буду спорить. Как раз проверю на своих запросах. Пока (сейчас как раз делаю большой запрос для отчета) разницы по времени нет.
15. sipoju 27.05.10 11:58 Сейчас в теме
Проверил оба варианта на своем запросе, с временными таблицами выполнение запроса на файловом варианте базы сократилось с 15 до 6,5 секунд, на клиент/серверном варианте (PostgreSQL) -- увеличилось с 0,08 до 0,19 секунды
igor_aviant; +1 Ответить
16. logarifm 1118 21.06.10 12:29 Сейчас в теме
Просьба администрации обратить внимание, что это апгрейд публикации и нарушение правил форума для прокача "+" :evil:
17. Ivon 673 21.06.10 13:39 Сейчас в теме
(16). Мне, в принципе, все равно, сколько у меня плюсов. Плюсы для меня всего-лишь показатель, что работа сделана не зря и статья кому-то помогла. Я делюсь знаниями с пользователями ресурсов бесплатно. При этом стараюсь делать так, чтобы не нужно было использовать платные обработки. У вас тоже есть хорошие статьи, но статьи, подобные этой http://www.infostart.ru/public/72044/ , увы, не красят автора.
18. logarifm 1118 21.06.10 14:00 Сейчас в теме
(17) А зря так. Программисты по своей природе ленивые особи :D и им лень лазить и узнавать какие-то другие ресурсы. Вот подруки подвернулась интересная статья которая имеет право на жизнь надо ее довести людям, поскольку есть фанаты именно таки прямых запросов к данным 1С на базе 8.х.
19. Ivon 673 21.06.10 14:33 Сейчас в теме
(18). Ленивые программисты долго не работают на одном месте, потому что программеру нужно постоянно быть в курсе нововведений, ведь через год-два в компанию придет свежая кровь, которая будет знать и уметь гораздо больше ленивого программиста и у которой будут вполне здоровые амбиции. А про рынок программистов я вообще молчу.
20. logarifm 1118 21.06.10 15:01 Сейчас в теме
(19) очень плохое понимание переносного значения "ленивый программист". Это саммые лучшие программисты им все ручками делать влом, они делают так чтобы программы все считали.
21. Ivon 673 21.06.10 15:17 Сейчас в теме
(20). Тогда кому же делать ту программу, которая будет все считать для ленивого программиста? Я с такими сталкивался. Выше среднего уровня они не поднимаются. И проект с такими программистами становится дороже, менее оптимизированный и страдающий плохой поддержкой. А все потому, что не самостоятельно пишется. Я не говорю, что стоит вообще отказываться от вспомогательных модулей, но это должно быть экономически выгодно, например, написание подобного модуля самостоятельно будет дороже его приобретения.
22. logarifm 1118 21.06.10 16:19 Сейчас в теме
Они сами и делают эти программы :) Ладно не переводим дискуссию - это все офф. Но по правилам форума нужно делать апгрейд публикации. Минус снял, посмотрел наработки и статьи Ваши - интересны и имеют полное право жизни, просто есть тут ходют сами ниче не понимают зато минусами раскидаются.

Мир :?: :!:
23. Ivon 673 21.06.10 16:44 Сейчас в теме
Оставьте свое сообщение