gifts2017

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

Опубликовал Ivon (Ivon) в раздел Программирование - Практика программирования

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

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

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

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


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

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

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


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

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

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

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


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

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

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

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

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


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

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

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

См. также

Подписаться Добавить вознаграждение

Комментарии

1. hren hren (hren) 19.05.10 02:39
Ничегоне понял, но очень интересно :)
2. Димок ддд (ДимокШ) 19.05.10 07:11
Типичные причины неоптимальной работы запросов и методы оптимизации
Автор: Рупасов Константин (1С, Москва) http://kb.1c.ru/articleView.jsp?id=44#subquery_in_on
3. Алексей Константинов (alexk-is) 19.05.10 07:12
(0) Методы оптимизации очень сильно зависят от техники, состава данных, состояния базы данных. В убитой горем базе данных сканирование может дать больший эффект. Для понимания достигнутого эффекта не хватает замеров производительности до и после, а также параметров техники и базы.

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

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

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

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

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

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

То есть выберем по ссылке документа различных сотрудников, объединим с регистром и сгруппируем.
5. Ivon (Ivon) 19.05.10 09:42
(2). Не читал, к сожалению, нет доступа.
(3). По поводу оптимизации для разной техники и баз согласен, но в большинстве случаев такая оптимизация даст прирост в скорости выполнения.
(4). В твоем примере вначале произойдет соединение, а после группировка. Смысл оптимизации в том, чтобы между соединениями был как можно меньший объем данных.
6. Алексей Роза (DoctorRoza) 19.05.10 09:53
(5) Пу почему же? Идея была в том, чтобы одним запросом, из текущего документа по ссылке выбрать Сотрудников и Даты! То есть тут и так будет минимум данных с учетом, что и регистр сведений тоже будет содержать параметры, а именно Сотрудников/Даты из текущего документа!
P.s. Может сказал глупость, но я только начинаю разбираться .. новичок! :oops:
7. Ivon (Ivon) 19.05.10 10:25
(6). А зачем нужно
ГДЕ

Договор.Ссылка = СсылкаДокумента
?
8. Алексей Константинов (alexk-is) 19.05.10 11:42
(7) Видимо ещё не разобрался :)
9. Алексей Константинов (alexk-is) 19.05.10 13:37
(6) Исключительно для тренировки предлагаю поразбираться с запросами, например, тут http://www.infostart.ru/public/68269/ и тут http://www.infostart.ru/public/69707/ :)
10. Ivon (Ivon) 19.05.10 14:34
(9). С календарем интересное решение.
11. Сергей (MoneG2) 19.05.10 18:04
> запросы с временными таблицами выполняются медленнее

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

Мир :?: :!:
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа