В какую сторону можно оптимизировать запрос?

← →
k2 © (2004-12-29 14:41) [0]

И вообще што неправильно(запрос рабочий, выдает правильные данные, но это первый запрос, с ораклом только начала работать, хотелось бы знать на што в первую очередь внимание обратить)

SELECT
CASE WHEN objs_name IS NULL AND GROUPING (cnt_class_modif) = 0 THEN "Итого : " ELSE inn END inn,
CASE WHEN GROUPING (cnt_class_modif) = 1 THEN "Итого к строке баланса :" ELSE objs_name END objs_name,
SUM (sum_deb_zdl),
SUM (sum_deb_zdl_pr),
cnt_class_modif
FROM (SELECT cnt01.inn inn, objs_name,
NVL (sum_deb_val, 0) - NVL (sum_cred_val, 0) sum_deb_zdl,
NVL (sum_deb_val_pr, 0) - NVL (sum_cred_val_pr, 0) sum_deb_zdl_pr,
CASE
WHEN NVL (sum_deb_val, 0) - NVL (sum_cred_val, 0) < 1000 THEN 891 -- 891
WHEN NOT(cnt03cnt10.cnt10_id = 882 OR cnt03cnt10.cnt10_id = 883) THEN 890 -- 890
WHEN cnt03cnt10.cnt10_id IS NULL THEN 890 -- 890
ELSE cnt03cnt10.cnt10_id
END cnt_class_modif
FROM (SELECT val_set.objs_pk, val_set.objs_name,
val_set.objs_code,
SUM (CASE WHEN pr_type = "deb" THEN val_int ELSE 0 END) sum_deb_val,
SUM (CASE WHEN pr_type = "cr" THEN val_int ELSE 0 END) sum_cred_val,
SUM (CASE WHEN pr_type = "deb" AND TRUNC (contr_posting_date) <= ADD_MONTHS (:end_date, -3) THEN val_int ELSE 0 END) sum_deb_val_pr,
SUM (CASE WHEN pr_type = "cr" AND TRUNC (contr_posting_date) <= ADD_MONTHS (:end_date, -3) THEN val_int ELSE 0 END) sum_cred_val_pr,
MIN (contr_posting_date) min_posting_date
FROM (SELECT contr.val_int val_int, "deb" pr_type,
contr.posting_date contr_posting_date,
objs.pk objs_pk, objs.NAME objs_name,
objs.code objs_code
FROM gl_contrecs contr,
gl_accounts acc,
gl_anlprops props,
gl_anlobjs objs
WHERE acc.pk = contr.acc_db_pk
AND acc.acpl_code = :acpl_code
AND acc.code LIKE :acc_code
AND contr.posted = :posted
AND contr.bu_code = :bu_code
AND props.crec_db_pk = contr.pk
AND objs.pk = props.anob_pk
AND objs.anca_code = :anca_code
AND TRUNC (contr.posting_date) <= :end_date
UNION ALL
SELECT contr.val_int val_int, "cr" pr_type,
contr.posting_date contr_posting_date,
objs.pk objs_pk, objs.NAME objs_name,
objs.code objs_code
FROM gl_contrecs contr,
gl_accounts acc,
gl_anlprops props,
gl_anlobjs objs
WHERE acc.pk = contr.acc_cd_pk
AND acc.acpl_code = :acpl_code
AND acc.code LIKE :acc_code
AND contr.posted = :posted
AND contr.bu_code = :bu_code
AND props.crec_cd_pk = contr.pk
AND objs.pk = props.anob_pk
AND objs.anca_code = :anca_code
AND TRUNC (contr.posting_date) <= :end_date) val_set
GROUP BY objs_pk, objs_name, objs_code),
cnt_01_hoz_subjects cnt01,
cnt_cnt03_cnt10 cnt03cnt10,
cnt_03_contragents cnt03,
cnt_10_classes cnt10
WHERE NVL (sum_deb_val, 0) - NVL (sum_cred_val, 0) > 0
AND objs_code = cnt01.ID(+)
AND cnt03cnt10.cnt02_cnt01_id(+) = :balunit
AND cnt03cnt10.cnt01_id(+) = objs_code
AND cnt03.cnt01_id(+) = cnt01.ID
AND cnt03.cnt02_cnt01_id(+) = :balunit
AND cnt10.ID(+) = cnt03cnt10.cnt10_id)
GROUP BY
GROUPING SETS ((inn, objs_name, cnt_class_modif), ROLLUP (cnt_class_modif))
ORDER BY
cnt_class_modif, objs_name

← →
Соловьев © (2004-12-29 14:44) [1]

пипец.. я бы засстрелился :)

← →
k2 © (2004-12-29 14:48) [2]

to Соловьев © (29.12.04 14:44) [1]

Все настолько запущенно? :(((

← →
Johnmen © (2004-12-29 14:49) [3]

>k2 ©

Да. Это тяжело для понимания...
Я бы попробовал избавиться от вложенных запросов, левых/правых соединений...
И возможно будет понятней и быстрей, если раздробить на несколько мелких запросов с обработкой на клиенте, или эти мелкие запросы использовать в хранимой процедуре.

← →
Соловьев © (2004-12-29 14:50) [4]

та нет, просто разобраться сложно. Надо долго сидеть и высматривать. С ораклом не знаком, потому конструции вида - select * from (select * from ) может и оправданы. но запутывают ужасно

← →
Sergey13 © (2004-12-29 14:53) [5]

2 k2 © (29.12.04 14:41)
А что конкретно не нравится то тебе? Если работает и правильно при том - уже хорошо.
Настораживает обилие CASE, NVL и (+), но ведь не зная таблиц - чего скажешь то?
Смотри план выполнения - все фул сканы на больших таблицах - повод для оптимизации.

← →
k2 © (2004-12-29 14:59) [6]

план безобразный :( два фулскана на самом нижнем уровне вложения :(( cost вообще не скажу сколько штобы не пугать :)

← →
Sergey13 © (2004-12-29 15:03) [7]

2[6] k2 © (29.12.04 14:59)
план безобразный :( два фулскана на самом нижнем уровне вложения :((
Если таблицы небольшие - тоже ничего страшного.

>cost вообще не скажу сколько штобы не пугать :)
Это то как раз ничего не значит - это аналог "а в попугаях то я гораздо длиннее". Он показателен только внутри всего вопроса, т.е. показывает относительный вес, по сравнению с другими частями.

← →
Sergey13 © (2004-12-29 15:04) [8]

2[6] k2 © (29.12.04 14:59)
В догонку. А статистика собирается регулярно?

← →
msguns © (2004-12-29 15:18) [9]

>k2 © (29.12.04 14:41)
>И вообще што неправильно(запрос рабочий, выдает правильные данные, но это первый запрос, с ораклом только начала работать

НУ НИ ФИГА Ж СЕБЕ !!! И это ПЕРВЫЙ запрос !?!? И ТОЛЬКО НАЧАЛА ???!!!

Что же будет во втором ? А в 22-м ???
Все, ухожу в монастырь. Но сначала в пивную ;((((((

← →
Наталия © (2004-12-29 15:21) [10]

Катя, привет!
Как я тебя зауважала! :)))
Такой ЗАПРОСИЩЕ!
У меня какой-то комлекс неполноценности начал вырабатываться - пойду поищу у себя самый большой запрос...

← →
Наталия © (2004-12-29 15:22) [11]

А если серьезно, то, конечно, нужно смотреть планы, индексы, статистику, возможно, хинты подключать, хотя...
Опять посмотрела на твой запрос... :)))

← →
Соловьев © (2004-12-29 15:25) [12]

Если запрос работает, то Кате надо ставить памятник :)

← →
Johnmen © (2004-12-29 15:26) [13]

>Наталия ©

Габариты и многоэтажность запроса может говорить о двух противоположных вещах...:)
Я склоняюсь ко второй.

← →
Sergey13 © (2004-12-29 15:27) [14]

2[13] Johnmen © (29.12.04 15:26)
Да ладно тебе, завидуешь. 8-)

← →
Наталия © (2004-12-29 15:28) [15]

Johnmen © (29.12.04 15:26) [13]
Я же пошутила :))
Но мне стало интересно - все ещё ищу у себя огромные запросы... :)))

← →
Johnmen © (2004-12-29 15:33) [16]

>Sergey13 © (29.12.04 15:27) [14]
>Да ладно тебе, завидуешь. 8-)

Вообще-то нет :)
По причине отсутствия объекта...

← →
Ольга (2004-12-29 17:07) [17]

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

← →
Sergey13 © (2004-12-29 17:09) [18]

2[17] Ольга (29.12.04 17:07)
Чуть подробнее Оля, чуть подробнее.

← →
Ольга (2004-12-29 17:21) [19]

Подробнее - с смысле чем этот запрос плох?
Если уже сейчас воникло желание оптимизировать, значит что-то не устраивает. Я подозреваю, что скорость выполнения. И чем больше будет данных, тем будет хуже. Чочется, конечно, получить все габузом с сервера, а в программе сделать только отображаловку, но ...
Мне пришлось свое громадье порушить года через 2, хотя надо это было сделать сразу. Кроме того, громоздкий запрос трудно сопровождать, если меняются условия выборки или структура таблиц.

← →
Sergey13 © (2004-12-29 17:26) [20]

2[19] Ольга (29.12.04 17:21)
Т.е. ты предлагаешь разбивать запрос типа
select * from t1,t2 where t1.id=t2.id
на 2 запроса типа
select * from t1
select * from t2
и на клиенте все это "сращивать"?
Так что-ли?

← →
Ольга (2004-12-29 17:40) [21]

В принципе да. Логику запроса, не зная поставленной задачи, сложно разобрать. Но я уверена, что этот запрос надо разбить на n штук, добавить пару-тройку if-ов, циклов и эта конструкция будет более скоростной и сопровождаемой. Что касается клиента - от этого по возможности нужно уйти, например создать хранимую процедуру, возвращающую клиенту готовую таблицу.

← →
Sandman25 © (2004-12-29 17:51) [22]

Запрос действительно можно переделать:
избавиться от флага deb и cr, сделав 2 разных поля, а не одно с признаком.

← →
Ольга (2004-12-29 17:53) [23]

Sergey13, мне все же кажется, что вы, хоть и встаете на защиту этого запроса, разделяете мое мнение, которое уже и было высказано Johnmen [3]
Или вы это искренне?

← →
Sandman25 © (2004-12-29 17:58) [24]

[23] Ольга (29.12.04 17:53)

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

← →
k2 © (2004-12-29 18:33) [25]

to Sergey13 © (29.12.04 15:04) [7, 8]
таблицы не очень большие, статистика второй день собирается, заметила, што пару раз вместо всего этого страшного плана появлялся маленький и симпатишный только вместо названий таблиц моих какието с префиксами sys, не догадалась правда сохранить, а связать от каких моих действий пока не соображу, возможно это создаются какие то временные таблицы самим ораклом, но не уверена

to Наталия © (29.12.04 15:21) [10]
приветики :) я ж не знала што так нельзя :) я думала што так можно :) на праздники кайтом угостили вот как прочитаю :)

to Ольга (29.12.04 17:40) [21]
собственно и наворотила, штобы вернуть на клента готовый датасет, скорость выполнения пока в ms, волнует больше сопровождаемость и вообще правильность, а то привыкнешь к плохому потом отвыкать тяжело :( в процедуру попробую завтра переделать, этот убивать не буду уж совсем, в заповедник отправлю :-/

извените што все это горе без комментариев, плохо подготовилась

← →
Johnmen © (2004-12-29 19:04) [26]

>k2 © (29.12.04 18:33) [25]
>в заповедник отправлю

:)))

← →
Vemer © (2004-12-29 20:31) [27]

Несколько мыслей вслух после бутылки Текизы и прочтения головоломног запроса сверху (чур ногами не пинать):
Запрос однозначно надо разбить на несколько. Мой пример: все ХП получают текущую себестоимость товара путем
Select Tovar_SST From Get_SST(Tovar_ID);
Если поменяется алгоритм вычисления себестоимости - я меняю одну Get_SST, остальное происходит автоматически.
Думаю запрос выше также надо разбить на "блоки" из нескольких ХП.

← →
Petr V. Abramov © (2004-12-29 20:57) [28]

> Я бы попробовал избавиться от вложенных запросов, левых/правых соединений...
Зачем?
> И возможно будет понятней и быстрей, если раздробить на
> несколько мелких запросов с обработкой на клиенте,
IMHO, понятней точно не будет
> или эти мелкие запросы использовать в хранимой процедуре.
Все это верно для IB/FB

> план безобразный :( два фулскана на самом нижнем уровне вложения
Ну так и давайте попробуем разобраться разобраться с тем, который на самом нижнем.

> запрос рабочий, выдает правильные данные
Вот и не трожьте его, пишите второй :)))

А вообще-то, Катя, будете у нас на Колыме - встречаю с миллионом алых роз ;)

← →
Thick (2004-12-29 22:41) [29]

Фиговые у тебя запросы!
Во первых нах так загружать сеть такими запросами???Кто тебе не даёт использовать встроеные процедуры?
А во вторых попробуй упростить запрос - может тогда и сам чё нить поймёш

Оптимизатор может это не сжевать - зависит от настроек. Мне иногда помогал хинт FIRST_ROWS (даже когда напрашивался ALL_ROWS)
Это если коротко, т.к. читать этот запрос я не собираюь - башка заболит 8)

>Petr V. Abramov © (29.12.04 20:57) [28]
>> Я бы попробовал избавиться от вложенных запросов, левых/правых соединений...
> Зачем?

Как зачем? снизить нагрузку, однако....

>> И возможно будет понятней и быстрей, если раздробить на
>> несколько мелких запросов с обработкой на клиенте,
> IMHO, понятней точно не будет

Будет, и однозначно... Без ИМХО

>> или эти мелкие запросы использовать в хранимой процедуре.
> Все это верно для IB/FB

Это верно для всех SQL серверов, поддерживающих хранимые процедуры, и Оракул не исключение...

Я ещё тут ночью подумала :)))
Судя по терминам, упоминающимся в запросе, это что-то связанное с бухгалтерией, причем данные выбираются за несколько месяцев, то есть это, скорее всего, какой-то сводный отчет, получаемый после закрытия какого-либо периода (месяца,квартала и т.п.)
Возможно, есть смысл организовать некое подобие хранилища данных, то есть создать какую-либо таблицу (совокупность таблиц), которая будет использоваться для подобных отчетов, куда периодически заносить предварительно обработанные данные.
Или хотя бы вьюшки наделать, чтобы наглядность повысить :))

to Наталия © (30.12.04 06:29) [32]
:) Наташ надеюсь кошмары не снились :) а то я себе не прощу :)
отчет квартальный, пользователей немного, я думала што вьюшки используются наоборот - при частом обращении, и к тому же пока не очень хотелось бы плодить новые объекты, база итак очень большая (хотя ей конешно с моей одной таблички ни жарко ни холодно не будет :)), разработчиков было несколько, постоянно натыкаюсь на какието таблицы-связки, вобщем пока более-менее осматриваюсь

2[25] k2 © (29.12.04 18:33)
>таблицы не очень большие,
Тогда фул скан не особо страшен. Хотя определение "не очень большие" расплывчатое слегка. Кому и мульен записей - фигня. 8-)

>статистика второй день собирается,
Это уже лучше. Но я бы попробовал еще с хинтом RULE. Иногда СВО бывает уж шибко вумным. 8-)

>заметила, што пару раз вместо...
Это и правда интересно. Может оптимизатор меняли на RULE.

>два фулскана на самом нижнем уровне вложения
Возможно это LIKE делает

2[29] Thick (29.12.04 22:41)
>Фиговые у тебя запросы!
>Во первых нах так загружать сеть такими запросами???
Где тут нагрузка на сеть, не подскажешь?

2[30] Fay © (29.12.04 22:57)
>Мне иногда помогал хинт FIRST_ROWS
В чем помогал?

2[31] Johnmen © (29.12.04 23:36)
>Как зачем? снизить нагрузку, однако....
А мне не раз удавалось повысить производительность запроса на порядки именно используя вложенные запросы.

>>> или эти мелкие запросы использовать в хранимой процедуре.
>> Все это верно для IB/FB
>Это верно для всех SQL серверов
А какая разница при использовании ХП и того же кода/логики в запросе? Разве что компиляция при выполнении.

233] k2 © (30.12.04 07:15)
>отчет квартальный, пользователей немного
Тогда, учитывая что "запрос рабочий, выдает правильные данные", вообще все зашибись. 8-)

>Sergey13 © (30.12.04 09:40) [34]
>А какая разница при использовании ХП и того же кода/логики в
>запросе? Разве что компиляция при выполнении.

Разница, если в ХП неск.мелких легких запросов. Вместо одно монстроидального...

2[35] Johnmen © (30.12.04 09:57)
>Разница, если в ХП неск.мелких легких запросов. Вместо одно монстроидального...
Так ить иногда суммарный вес мелких помноженый на "оптимальный" код в ХП может с лихвой уделать и не одного монстра. 8-)

[37] Johnmen © (30.12.04 10:05)

Вот именно, всё зависит от запросов. В одном случае произошло огромное ускорение при разбивке большого запроса из ХП на несколько отдельных запросов (помещенных к тому же в отдельные ХП), в другом - пришлось переделать ХП в обратном направлении, задействовав запросы на несколько страниц.

Мое ИМХО - подзапросы (в том же Оракле) и ХП (возвращающие НД) в ИБ - это практически одно и то-же. Я даже думаю, что часто в ИБ использование ХП - это просто способ обойти отсутствие подзапросов. Вот вроде в ФБ2 обещают поддержку подзапросов, к чему бы это?
Кроме того сами по себе много мелких запросов не всегда дают нужный результат - их результаты надо еще объединить - а это уже интересная (как минимум) задача.
Хотя конечно в каждом конкретном случае - конкретное решение.

В какую сторону можно оптимизировать запрос? Найти похожие ветки