СУПЕРЗАПРОСЫ: У кого самый большой запрос?
Выношу на обсуждение запрос с которым мучился в общей сложности часов 12.
В запрос используется код из задача на инфостарте. Образец запроса в 14м комменте
если в образце сумма к распределению дается как параметр то здесь распределяются суммы рассчитанные в самом запросе с учетом все группировок.
В приведенном запросе сумма доставки(из закладки Услуги ЗаказаПокупателя в пиленной перепиленной УТ10.30) распределятеся по реализациям заказа пропорционально весам реализации.
Потом распределяются по мотивациям(товарная группа) реализации пропорционально весу товарных позиций внутри реализации.
Показано как применить приведенный тестовый запрос к реальным данным. Когда у вас нет данных в регистрах(мопед не ваш - вы пришли на базу которую вы не пилили) и приходится вытаскивать суммы из сторонних реквизитов.
Отчет используется для расчета PKI
Длина запроса 310 строк.
У кого длинше есть запрос?:)
p.s. с тем что запрос можно слегка сократить - согласен
В запрос используется код из задача на инфостарте. Образец запроса в 14м комменте
если в образце сумма к распределению дается как параметр то здесь распределяются суммы рассчитанные в самом запросе с учетом все группировок.
В приведенном запросе сумма доставки(из закладки Услуги ЗаказаПокупателя в пиленной перепиленной УТ10.30) распределятеся по реализациям заказа пропорционально весам реализации.
Потом распределяются по мотивациям(товарная группа) реализации пропорционально весу товарных позиций внутри реализации.
Показано как применить приведенный тестовый запрос к реальным данным. Когда у вас нет данных в регистрах(мопед не ваш - вы пришли на базу которую вы не пилили) и приходится вытаскивать суммы из сторонних реквизитов.
Отчет используется для расчета PKI
Длина запроса 310 строк.
У кого длинше есть запрос?:)
ВЫБРАТЬ
ПродажиОбороты.ЗаказПокупателя,
ВЫРАЗИТЬ(МАКСИМУМ(ВесаЗаказов.ВесЗаказа) КАК ЧИСЛО(15, 2)) КАК ВесЗаказа,
ВЫРАЗИТЬ(МАКСИМУМ(ВесаЗаказов.СуммаЗаказа) КАК ЧИСЛО(15, 2)) КАК СуммаКРаспределению,
МАКСИМУМ(ДоставкаВУслугах.СуммаЗаказа) КАК СтоимостьДоставки,
МАКСИМУМ(ЗНД.ФактическиеРасходы) КАК СебестоимостьДоставки
ПОМЕСТИТЬ Заказы
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаявкаНаДоставку КАК ЗНД
ПО (ЗНД.Заказ = ПродажиОбороты.ЗаказПокупателя)
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ЗПТовары.Ссылка КАК Ссылка,
СУММА(ЗПТовары.Количество * ЕСТЬNULL(ЗПТовары.ЕдиницаИзмерения.Вес, 0)) КАК ВесЗаказа,
СУММА(ВЫБОР
КОГДА ЗПТовары.Ссылка.СуммаВключаетНДС
ТОГДА ЗПТовары.Сумма + ЗПТовары.СуммаНДС
ИНАЧЕ ЗПТовары.Сумма
КОНЕЦ) КАК СуммаЗаказа
ИЗ
Документ.ЗаказПокупателя.Товары КАК ЗПТовары
СГРУППИРОВАТЬ ПО
ЗПТовары.Ссылка) КАК ВесаЗаказов
ПО (ВесаЗаказов.Ссылка = ПродажиОбороты.ЗаказПокупателя)
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
ЗПУслуги.Ссылка КАК Ссылка,
СУММА(ВЫБОР
КОГДА ЗПУслуги.Ссылка.СуммаВключаетНДС
ТОГДА ЗПУслуги.Сумма
ИНАЧЕ ЗПУслуги.Сумма + ЗПУслуги.СуммаНДС
КОНЕЦ) КАК СуммаЗаказа
ИЗ
Документ.ЗаказПокупателя.Услуги КАК ЗПУслуги
ГДЕ
ЗПУслуги.Номенклатура.Код = "БП000000555" //Услуга "Доставка"
СГРУППИРОВАТЬ ПО
ЗПУслуги.Ссылка) КАК ДоставкаВУслугах
ПО (ДоставкаВУслугах.Ссылка = ПродажиОбороты.ЗаказПокупателя)
СГРУППИРОВАТЬ ПО
ПродажиОбороты.ЗаказПокупателя
;
//////////////////////////////////////////////////////////// Показатьp.s. с тем что запрос можно слегка сократить - согласен
По теме из базы знаний
Ответы
Подписаться на ответы
Сортировка:
Древо развёрнутое
Свернуть все
Решил испытать движок инфостарта на длину постов? Спросил бы напрямую у админов :)
754 строки, зуп 3.1, свой доп. отчет.
(+ 50 строк на определение доп. условий перед этим запросом)
754 строки, зуп 3.1, свой доп. отчет.
(+ 50 строк на определение доп. условий перед этим запросом)
ВЫБРАТЬ РАЗРЕШЕННЫЕ
&ПериодРегистрации,
КадроваяИсторияСотрудниковСрезПоследних.Сотрудник,
КадроваяИсторияСотрудниковСрезПоследних.Должность КАК Должность,
КадроваяИсторияСотрудниковСрезПоследних.Подразделение КАК Подразделение
ПОМЕСТИТЬ ВТ_ДанныеСотрудников
ИЗ
РегистрСведений.КадроваяИсторияСотрудников.СрезПоследних(КОНЕЦПЕРИОДА(&ПериодРегистрации, ДЕНЬ), ) КАК КадроваяИсторияСотрудниковСрезПоследних
ГДЕ
КадроваяИсторияСотрудниковСрезПоследних.ВидСобытия <> ЗНАЧЕНИЕ(Перечисление.ВидыКадровыхСобытий.Увольнение)
И КадроваяИсторияСотрудниковСрезПоследних.Организация = &Организация
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(КадроваяИсторияСотрудников.Период, ДЕНЬ),
КадроваяИсторияСотрудников.Сотрудник,
КадроваяИсторияСотрудников.Должность,
КадроваяИсторияСотрудников.Подразделение
ИЗ
РегистрСведений.КадроваяИсторияСотрудников КАК КадроваяИсторияСотрудников
ГДЕ
КадроваяИсторияСотрудников.Период МЕЖДУ &ПериодРегистрации И КОНЕЦПЕРИОДА(&ПериодРегистрации, МЕСЯЦ)
И КадроваяИсторияСотрудников.Организация = &Организация
И КадроваяИсторияСотрудников.ВидСобытия <> ЗНАЧЕНИЕ(Перечисление.ВидыКадровыхСобытий.Увольнение)
;
//////////////////////////////////////////////////////////// Показать
Большие запросы пишутся горе-программистами 1С из-за горе-рекомендаций фирмы 1С. Суть горе-рекомендаций в том, чтобы программист, не особо знакомый с работой SQL-оптимизатора, планами запросов и прочей внутренней кухней СУБД выделял все используемые таблицы сначала во временные, а потом уже из временных таблиц формировал результат. В итоге такие горе-программисты пишут запрос с сотней временных таблиц, а в конце не редок случай, когда результат этого всего фильтруется по одной ссылке. Видел я как-то обмен данными между ЗУП и порталом Битрикса, где порядка тысячи строк в запросе в основном составляли конструкции "ВЫБРАТЬ А, Б, Ц ПОМЕСТИТЬ ВТТ1 ИЗ Т1; ВЫБРАТЬ Б, Ц, А ПОМЕСТИТЬ ВТТ2 ИЗ ВТТ1; ...".
В общем есть одно достаточно на мой взгляд репрезентативное наблюдение - чем длиннее запрос, тем тупее программист. Понятно, что есть и исключения, но они часто только подтверждают правило.
В общем есть одно достаточно на мой взгляд репрезентативное наблюдение - чем длиннее запрос, тем тупее программист. Понятно, что есть и исключения, но они часто только подтверждают правило.
(18) я согласен что есть моменты когда без подзапросов нельзя обойтись. Но когда нет разницы что с ВТ что без почему бы и нет?
Ты просто создаешь запрос последовательно. готовишь таблицы потом скармливаешь другой. Так нагляднее и быстрее.
Тот же приведенный запрос отрабатывает за 2 секунды на большой реальной базе.
Ты просто создаешь запрос последовательно. готовишь таблицы потом скармливаешь другой. Так нагляднее и быстрее.
Тот же приведенный запрос отрабатывает за 2 секунды на большой реальной базе.
(18)
Ну хотя бы тем, что не всегда этого достаточно или что иногда приходится одни и те же таблицы использовать несколько раз.. или тем, что эти таблицы перед соединением/объединением должны быть как-то обработаны.. или тем, что дофига соединений в одной таблицы почти нечитаемы
Чем ВТ будет нагляднее простого соединения или объединения двух таблиц?
Ну хотя бы тем, что не всегда этого достаточно или что иногда приходится одни и те же таблицы использовать несколько раз.. или тем, что эти таблицы перед соединением/объединением должны быть как-то обработаны.. или тем, что дофига соединений в одной таблицы почти нечитаемы
(25) С временной таблицей это наглядный и понятный запрос, без неё скорее всего тоже получится, но что бы понять, придется сидеть и курить
(26) Должно или не должно, это вопрос ситуационный. Обычно у меня такая потребность возникала посередине другого запроса, и выходить из него, что бы что-то пересчитать и заходить назад как-то странно. Да и пока считаю, что запрос это быстрее сделает. Примеров сравнения производительности не видел
(26) Должно или не должно, это вопрос ситуационный. Обычно у меня такая потребность возникала посередине другого запроса, и выходить из него, что бы что-то пересчитать и заходить назад как-то странно. Да и пока считаю, что запрос это быстрее сделает. Примеров сравнения производительности не видел
(29) ну, например, на хабре про 1С и распределение:
Но важен тут вот этот комментарий:
Но если действительно интересно, то могу поискать соответствующие статьи (да и Вы можете их поискать на том же хабре).
Но важен тут вот этот комментарий:
Вы попали в точку. На специализированном форуме (ссылка выше), где была размещена та же статья, автору указали на это несоответствие.
В том подмножестве T-SQL, которое реализовано в 1С нет, например, оконных функций, которые в этой задаче позволяют быстро посчитать нарастающий итог — это два первых запроса. Также нет и RowNumber(), который помог бы третий запрос оптимизировать. Поэтому как будто все должно было быть наоборот: запрос должен был тормозить (там n^2), а код — снизить затраты до n. Как автор получил прямо противоположный результат — можно только догадываться.
В статье действительно больше лирики, чем физики.
В том подмножестве T-SQL, которое реализовано в 1С нет, например, оконных функций, которые в этой задаче позволяют быстро посчитать нарастающий итог — это два первых запроса. Также нет и RowNumber(), который помог бы третий запрос оптимизировать. Поэтому как будто все должно было быть наоборот: запрос должен был тормозить (там n^2), а код — снизить затраты до n. Как автор получил прямо противоположный результат — можно только догадываться.
В статье действительно больше лирики, чем физики.
Но если действительно интересно, то могу поискать соответствующие статьи (да и Вы можете их поискать на том же хабре).
(33) Но в целом-то указанная статья именно о том, что запрос быстрее работает. Ну и ту задачу, что там упоминается, я бы запросом не стал делать, нарушается принцип читаемости кода. А так, если есть ещё какие-то статьи, где выборки быстрее запроса, с удовольствием посмотрю. Спасибо
(36)
Есть много всего, что можно делать запросами (например, реализация жадного алгоритма от Ильдаровича), но это не значит, что оное нужно делать запросами. По поводу статей -то если Вас забанили в гугле, то я, конечно, могу поискать, но, помнится, их было достаточно, чтобы Вы и сами могли справиться. Например, на том же хабре была статья, в которой достаточно убедительно предлагали даже сортировку в ряде случаев передавать даже не серверу приложений (не говоря уже о сервере базы данных), а клиенту при отображении, чтобы экономить ресурсы сервера. Это может быть справедливо для небольших списков, которые используются достаточно часто в интерфейсе клиента. Т.к. клиентов много и все они работают на отдельном компьютере, то в ряде случаев лучше сэкономить на сортировке и упорядочивать данные списки на стороне клиента, а не писать "упорядочить" в запросе. Но, конечно, это уже при достаточной зрелости проекта и высоком числе пользователей, ибо сортировка в любом случае меньше чем n*log2(n) не займет и при выполнении оной на сервере эта нагрузка будет взята из его ресурсов, при этом клиент будет "курить".
где выборки быстрее запроса
Это не единственная причина. Предположу, что "ВЫБРАТЬ 1+2+3*7+12*34+4...." может работать быстрее, чем А = 1+2+3*7+12*34+4..., но мы же не используем для этого запрос, да?
Есть много всего, что можно делать запросами (например, реализация жадного алгоритма от Ильдаровича), но это не значит, что оное нужно делать запросами. По поводу статей -то если Вас забанили в гугле, то я, конечно, могу поискать, но, помнится, их было достаточно, чтобы Вы и сами могли справиться. Например, на том же хабре была статья, в которой достаточно убедительно предлагали даже сортировку в ряде случаев передавать даже не серверу приложений (не говоря уже о сервере базы данных), а клиенту при отображении, чтобы экономить ресурсы сервера. Это может быть справедливо для небольших списков, которые используются достаточно часто в интерфейсе клиента. Т.к. клиентов много и все они работают на отдельном компьютере, то в ряде случаев лучше сэкономить на сортировке и упорядочивать данные списки на стороне клиента, а не писать "упорядочить" в запросе. Но, конечно, это уже при достаточной зрелости проекта и высоком числе пользователей, ибо сортировка в любом случае меньше чем n*log2(n) не займет и при выполнении оной на сервере эта нагрузка будет взята из его ресурсов, при этом клиент будет "курить".
(37) Со всем согласен. Только, к сожалению, почти все эти справедливые замечания плохо ложатся на 1С. Тонкий клиент провоцирует все делать на сервере (все оптимизации сводятся к кэшированию на клиенте и уменьшению количества серверных вызовов, иначе только хуже будет). СКД провоцирует все делать в запросе (процедурные вкрапления дороги и сложны). Тормоза 1С провоцируют перекладывать нагрузку на сервер СУБД. И так далее. В функционале 1С изначально есть некий дисбаланс, который не позволяет прямо применять сложившиеся best practices. У 1С они, к сожалению, свои.
(38)
е эти справедливые замечания плохо ложатся на 1С.
Не уверен. Достаточно многое можно делать и на клиенте (особенно, если на сервере какое-нить г-но с 100 ядрами и частотой 2ГГц - зион 1-го поколения 65нм, а на клиенте i3 3,5ГГц 14нм, который это же этак раза в три быстрее вычислит, ибо все-равно в один поток). Я, например, стараюсь на сервер бегать из больших форм только без контекста или в общие модули, т.к. большую часть управления формой можно сделать безо всего вот этого серверного дергания контекста. Более того, ради интереса попробуйте строку разложить в массив подстрок вызвав безконтекстную процедуру и вызвав клиентскую процедуру. Даже на файловой базе скорость клиентского вызова на порядок выше, не говоря уже о вызове безконтекстной процедуры в клиент-сервере - это на два порядка медленнее. И если стоит задача размазать сумму по строкам табличной части документа, то серверный вызов тут нафиг не нужен - на клиенте реально будет быстрее. Это не говоря о запросах. Просто MVC нужно уметь готовить, а не просто по-привычке юзать то, что в 1С типа считается стандартом. (- Вы все делаете так, как делали все время... - Но все время делали так...(с) Астерикс и Обеликс: Клеопатра).
(41) хм. вешать на клиента не логично как мне кажется в любом случае. даже простое условное оформление иногда тормозит.
Плюс если мы всегда можем потребовать хорошую мощность сервера, то мощь клиентской машины никогда, а представьте как будут работать процедуры на минимальной конфигурации компов да еще с вебклиента.
Плюс если мы всегда можем потребовать хорошую мощность сервера, то мощь клиентской машины никогда, а представьте как будут работать процедуры на минимальной конфигурации компов да еще с вебклиента.
(42)
даже простое условное оформление иногда тормозит
Ибо оно дергает сервер, если его менять (не меняется оно на клиенте). Или имеется ввиду оформление выводимых строк? Не замечал, что тормоза именно в нем (если, конечно, там не мульон разыменовываний и тыща строк).
(41)
ЕМНИП, если начать лопатить большую ТЧ на клиенте, то это спровоцирует ряд неявных контекстных вызовов платформы для синхронизации серверного контекста. Тогда как на сервере это будет один явный. Так что как раз массовую обработку ТЧ лучше делать на сервере.
И если стоит задача размазать сумму по строкам табличной части документа, то серверный вызов тут нафиг не нужен - на клиенте реально будет быстрее.
ЕМНИП, если начать лопатить большую ТЧ на клиенте, то это спровоцирует ряд неявных контекстных вызовов платформы для синхронизации серверного контекста. Тогда как на сервере это будет один явный. Так что как раз массовую обработку ТЧ лучше делать на сервере.
(44) Ну, про все остальное-то правильно. Если можно минимизировать серверные вызовы - нужно их минимизировать. Если можно обойтись неконтекстными - надо ими обходиться. И про то, что на клиент можно достаточно сложные расчеты выносить - тоже верно. Просто нечасто так бывает, чтобы сложные расчеты не были завязаны на синхронизацию контекста (в рамках одной строки или шапки).
(27)
Обычно помогает перед открытием конструктора запросов (или блокнота, не суть) какое-то время подумать над постановкой задачи, чтобы не возникали ВНЕЗАПНЫЕ потребности.
Архитектор - это не только гараж нарисовать.
Обычно у меня такая потребность возникала посередине другого запроса, и выходить из него, что бы что-то пересчитать и заходить назад как-то странно.
Обычно помогает перед открытием конструктора запросов (или блокнота, не суть) какое-то время подумать над постановкой задачи, чтобы не возникали ВНЕЗАПНЫЕ потребности.
Архитектор - это не только гараж нарисовать.
(23)
Есть на входе таблица номенклатура + цена. Надо по ней пропорционально сумме строки размазать какую-то общую сумму.
Это вообще не должно решаться запросом. Запросы не для этого созданы. У Ильдааровича есть примеры того, как это делать, и запрос там - далеко не первый по производительности. Запросам запросово, а коду кодово! (с)
(20)
Ну, во-первых, если этого недостаточно, то никто не мешает отфильтровать данные в самом соединении. Если же данные используются повторно (типа списка физиков из ЗУП), то я лично, ни разу не против того, чтобы поместить их в ВТ и проиндексировать. Но реляционные структуры - это связанные данные, которые нужны обычно в одном кортеже из нескольких таблиц. И если сначала все засовывать во временную таблицу, а потом это все обрабатывать, то проблема возникает в том, что временная таблица - это не таблица в памяти - она пишется в tempdb. И если есть очень много документов, из которых нужно отобрать все документы по определенной, например, номенклатуре, то не стоит засовывать этот список во временную таблицу, а нужно просто взять и получить ссылку из табличной части документа, отфильтрованной по нужной номенклатуре.
Временные таблицы вообще должны быть использованы только для повторного использования или для последующей фильтрации через внутреннее соединение больших списков по небольшой группе элементов фильтра. В остальных случаях применение временных таблиц лично мне видится крайне нецелесообразным.
...
Ну, во-первых, если этого недостаточно, то никто не мешает отфильтровать данные в самом соединении. Если же данные используются повторно (типа списка физиков из ЗУП), то я лично, ни разу не против того, чтобы поместить их в ВТ и проиндексировать. Но реляционные структуры - это связанные данные, которые нужны обычно в одном кортеже из нескольких таблиц. И если сначала все засовывать во временную таблицу, а потом это все обрабатывать, то проблема возникает в том, что временная таблица - это не таблица в памяти - она пишется в tempdb. И если есть очень много документов, из которых нужно отобрать все документы по определенной, например, номенклатуре, то не стоит засовывать этот список во временную таблицу, а нужно просто взять и получить ссылку из табличной части документа, отфильтрованной по нужной номенклатуре.
Временные таблицы вообще должны быть использованы только для повторного использования или для последующей фильтрации через внутреннее соединение больших списков по небольшой группе элементов фильтра. В остальных случаях применение временных таблиц лично мне видится крайне нецелесообразным.
(34) в случае простых выборок да.
но бывает что
- ты приходишь на базу с большой историей и с недоработанным регистрами а отчет нужен срочно. Тогда выхода нет.
- сложные случаи там "Распределение сумм","Расчет по календарю","Поиск пустых мест", "Отчеты по просрочке" и прочее прочее
но бывает что
- ты приходишь на базу с большой историей и с недоработанным регистрами а отчет нужен срочно. Тогда выхода нет.
- сложные случаи там "Распределение сумм","Расчет по календарю","Поиск пустых мест", "Отчеты по просрочке" и прочее прочее
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот