Форум: "Базы";
Текущий архив: 2003.08.07;
Скачать: [xml.tar.bz2];
Вниз
Оптимизация запроса Найти похожие ветки
← →
McSimm2 (2003-07-15 08:46) [0]Есть запрос один-ко-многим, который в свою очередь то же является запросом один-ко многим. Всё усугубляется тем, что в конечном итоге это ещё и в тройном экземпляре...
SELECT s.*, kv.*
FROM sobes_analiz s,
(SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj,
v.otch AS votch
FROM (SELECT *
FROM ksm k2
WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
OR k2.kkom IS NULL) k,
vet v
WHERE ((k.jeu = v.jeu(+))
AND (k.rajon = v.rajon(+))
AND (k.n_lc = v.n_lc(+)))) kv
WHERE (( s.ksmstreet = kv.ulica(+)
AND s.ksmhouse = kv.dom(+)
AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
)
)
UNION
SELECT s.*, kv.*
FROM sobes_analiz s,
(SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, f.famil AS vfamil, "" AS vimj, "" AS votch
FROM (SELECT *
FROM ksm_ved k2
WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
OR k2.kkom IS NULL) k,
vet_ved f
WHERE ((k.jeu = f.jeu(+))
AND (k.rajon = f.rajon(+))
AND (k.n_lc = f.n_lc(+)))) kv
WHERE (( s.ksmstreet = kv.ulica(+)
AND s.ksmhouse = kv.dom(+)
AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
)
)
UNION
SELECT s.*, kv.*
FROM sobes_analiz s,
(SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, 0, k.famil, "",
f.famil AS vfamil, "" AS vimj, "" AS votch
FROM ksm_chs k, vet_chs f
WHERE ((k.jeu = f.jeu(+))
AND (k.rajon = f.rajon(+))
AND (k.n_lc = f.n_lc(+)))) kv
WHERE (( s.ksmstreet = kv.ulica(+)
AND s.ksmhouse = kv.dom(+)
AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
)
)
Причём кусок запроса
SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj,
v.otch AS votch
FROM (SELECT *
FROM ksm k2
WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
OR k2.kkom IS NULL) k,
vet v
WHERE ((k.jeu = v.jeu(+))
AND (k.rajon = v.rajon(+))
AND (k.n_lc = v.n_lc(+)))
Этот кусок отрабатывается меньше секунды, а уже если его использовать в общем запросе, то 3 минуты. Весь запрос работает минут 7.
База Ksm - 180.000 записей, Vet - 450.000. Все остальные - мелкие - порядка 10.000
Мастера, как запрос соптимизировать можно ?
← →
Sergey13 (2003-07-15 09:09) [1]2McSimm2 (15.07.03 08:46)
1.Попробуй избавиться от конструкций типа
NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
Если тебе надо по умолчанию -1 то так и сделай.
2.Отрабатывай каждый "кусок" отдельно. Планчики смотри.
3.Попробуй хинтами пользоваться.
4.Попробуй сделать вьюху по твоему подзапросу.
5.Индексы
6.Статистику собираешь?
n-1. Вопросы у тебя, однако. Тут до хрена чего можно крутить. 8-)
← →
Dan (2003-07-15 09:17) [2]если нет цели убрать повторяющиеся строки , может попробовать поставить UNION ALL.
← →
McSimm2 (2003-07-15 09:20) [3]Индексы сделаны - по всем таблицам; Каждый кусок я отрабытаю - в вопросе я это и описал.
Как можно от NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)избавиться? Для моей задачи именно NVL и нужен , что бы можно было коннектить по полям если значение null в них
← →
passm (2003-07-15 09:23) [4]McSimm2 (15.07.03 08:46)> СУБД?
← →
McSimm2 (2003-07-15 09:25) [5]Dan (15.07.03 09:17)
Unioon all я ставил - скорость увеличивается, но не намного.
Конкретно по работе отдельных кусков, как предлогал
Sergey13 © (15.07.03 09:09)
получается, что
SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj,
v.otch AS votch
FROM (SELECT *
FROM ksm k2
WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
OR k2.kkom IS NULL) k,
vet v
WHERE ((k.jeu = v.jeu(+))
AND (k.rajon = v.rajon(+))
AND (k.n_lc = v.n_lc(+)))
работает 16 милисекунд, а
sELECT s.*, kv.*
FROM sobes_analiz s,
(SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj,
v.otch AS votch
FROM (SELECT *
FROM ksm k2
WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
OR k2.kkom IS NULL) k,
vet v
WHERE ((k.jeu = v.jeu(+))
AND (k.rajon = v.rajon(+))
AND (k.n_lc = v.n_lc(+)))) kv
WHERE (( s.ksmstreet = kv.ulica(+)
AND s.ksmhouse = kv.dom(+)
AND NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)
)
)
отрабатывает хорошо за 3 минуты
← →
Dan (2003-07-15 09:25) [6]а ты построй 2 индекса по выражениям NVL (s.ksmflat, -1) и NVL (kv.kvart, -1)
← →
McSimm2 (2003-07-15 09:25) [7]passm © (15.07.03 09:23)
[Oracle]
← →
McSimm2 (2003-07-15 09:28) [8]Dan (15.07.03 09:25)
Индексы стоят так:
по sobes_analiz стоит ksmstreet,ksmhouse,ksmflat
по vet,vetchs,vetved - jeu,rajon,n_lc
по ksm,ksmchs,ksmved - jeu,rajon,n_lc
7 индексов итого - вроде никакие не пересекаются
← →
Dan (2003-07-15 09:34) [9]план выполнения запроса смотрел ??? чего он рисует ?
← →
Johnmen (2003-07-15 09:34) [10]1.Весь этот немыслимый запрос можно переписать так, чтобы не было "запрос из запроса", а лишь соединение таблиц, хоть и левое.
2.Серьезно и вдумчиво подойти к проектированию БД. Чтобы не было мучительно больно в ожидании выполнения многоэтажных запросов...:)
← →
McSimm2 (2003-07-15 09:36) [11]Dan (15.07.03 09:34)
Как это можно осуществить и где ? Пользуюсь Toad`ом
← →
Dan (2003-07-15 09:41) [12]там есть, но не помню где :-(( просто я пользуюсь SQL Navigator.
Должно быть что-то вроде Explain plan tool
← →
Sergey13 (2003-07-15 09:43) [13]2McSimm2 (15.07.03 09:25)
А планы то смотрел? Что они показывают?
>Индексы сделаны - по всем таблицам
С NVL они работать скорее всего не будут. Смотри планы выполнения. Хотя это видно и по скорости выполнения которые ты привел.
>Как можно от NVL (s.ksmflat, -1) = NVL (kv.kvart(+), -1)избавиться? Для моей задачи именно NVL и нужен , что бы можно было коннектить по полям если значение null в них
Я предлагал изменить структуру данных - если это возможно. Если в этих полях не нужны NULL, то и избавься от них раз и навсегда.
Еще можно попробовать не каждый зпрос соединять с твоим подзапросом, а сначала сделать юнион и затем соединить его с подзапросом (который можно сделать вьюхой - по крайней мере компилиться каждый раз не будет).
>WHERE ( (k2.kkom <> "5")
AND (k2.kkom <> "7")
AND (k2.kkom <> "8")
AND (k2.kkom <> "9")
)
Вот эту часть попробовать сделать через равенство а не через неравенство. Ускоряет иногда, за счет лучшего использования индекса. Или можно попробовать переделать на NOT IN (5,7,8,9).
Да дофига вариантов. Оптимизация - дело тонкое.
← →
Dan (2003-07-15 09:45) [14]елки-моталки, а если попробовать сделать сначала 3 юниона, потом уже соединять полученное множество с таблицей sobes_analiz.
← →
McSimm2 (2003-07-15 09:51) [15]Dan (15.07.03 09:45)
Нужно попробовать
← →
Dan (2003-07-15 10:05) [16]До сих пор работает ? :-))
← →
McSimm2 (2003-07-15 10:29) [17]ВЕСЬ запрос стал работать 3 минуты после того, как я его переделал.
Превратилось это вот во что:
SELECT s.*, kv_all.*
FROM sobes_analiz s,
(SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, v.famil AS vfamil, v.imj AS vimj,
v.otch AS votch
FROM (SELECT *
FROM ksm k2
WHERE (k2.kkom NOT IN ("5", "7", "8", "9"))
OR k2.kkom IS NULL) k,
vet v
WHERE ( (k.jeu = v.jeu(+))
AND (k.rajon = v.rajon(+))
AND (k.n_lc = v.n_lc(+))
)
UNION ALL
SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, k.p_kom,
k.famil, k.kkom, f.famil AS vfamil, "" AS vimj, "" AS votch
FROM (SELECT *
FROM ksm_ved k2
WHERE (k2.kkom NOT IN ("5", "7", "8", "9"))
OR k2.kkom IS NULL) k,
vet_ved f
WHERE ( (k.jeu = f.jeu(+))
AND (k.rajon = f.rajon(+))
AND (k.n_lc = f.n_lc(+))
)
UNION ALL
SELECT k.rajon, k.jeu, k.n_lc, k.ulica, k.dom, k.kvart, 0, k.famil, "",
f.famil AS vfamil, "" AS vimj, "" AS votch
FROM ksm_chs k, vet_chs f
WHERE ( (k.jeu = f.jeu(+))
AND (k.rajon = f.rajon(+))
AND (k.n_lc = f.n_lc(+))
)) kv_all
WHERE (( s.ksmstreet = kv_all.ulica(+)
AND s.ksmhouse = kv_all.dom(+)
AND NVL (s.ksmflat, -1) = NVL (kv_all.kvart(+), -1)
)
)
Самое обидное, что все union работают ~20 милисекунд и возвращает ~500000 записей,а когда я сверху этот sobes_analiz приляпываю , вкотором 9000 записей, то запрос начинает работать 3 минуты (правда это уже не 7 как раньше)
Так что спасибо всем кто откликнулся и указал в каком направлении копать ....
← →
Desdechado (2003-07-15 10:38) [18]есть подозрение, что подзапрос отрабатывает для каждой записи главного запроса
сделай лучше ХП, в которой FOR SELECT из подзапроса, а внутри анализируй результат и возвращай только нужное
← →
DenK_vrtz (2003-07-15 10:40) [19]McSimm2 (15.07.03 10:29), согласен на все 100% с Johnmen © (15.07.03 09:34). Пересмотри структуру, поможет!
И еще. Я не вникал с сам запрос, но на вскидку, по собственному опыту, много индексов не есть хорошо. Лучше меньше, но лучше!
← →
McSimm2 (2003-07-15 10:44) [20]DenK_vrtz © (15.07.03 10:40)
Индексы у таблицы только по тем полям - по которым идёт связывание таблиц.
← →
DenK_vrtz (2003-07-15 10:48) [21]McSimm2 (15.07.03 10:44), не всегда помогает. Эти поля хорошей селективностью должны обладать, по отдельности или в совокупности.
← →
McSimm2 (2003-07-15 10:49) [22]Каждый индекс состоит из савокупности 3 полей, которые все вместе создают уникальность
← →
DenK_vrtz (2003-07-15 10:54) [23]Хорошей селективностю считается совокупность тех полей, которая составляет 3-5% всех данных. Вот и смотри.
← →
Sergey13 (2003-07-15 11:03) [24]Исчо раз повторю - избавься от NVL в конечном Where. Что за поля s.ksmflat и kv_all.kvart - каков их смысл. Почему нельзя сразу вставлять туда -1?
И еще - а у тебя все алиасы - таблицы, или и вьюхи есть? Проблемы могут быть и в этом.
Без приведения планов выполнения спор становится бессмысленным и беспредметным.
Какая версия Оракла? Какой оптимизатор включен по умолчанию? Если стоимостной то статистика когда последний раз собиралась?
← →
McSimm2 (2003-07-15 11:15) [25]>>Исчо раз повторю - избавься от NVL в конечном Where.
>>Что за поля s.ksmflat и kv_all.kvart - каков их смысл.
>>Почему нельзя сразу вставлять туда -1?
Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.
>>И еще - а у тебя все алиасы - таблицы, или и вьюхи есть?
>>Проблемы могут быть и в этом.
что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.
>>Какая версия Оракла?
8
>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...
← →
petr_v_a (2003-07-15 11:26) [26]>McSimm2 (15.07.03 10:29)
>Самое обидное, что все union работают ~20 милисекунд и
>возвращает ~500000 записей,а когда я сверху этот sobes_analiz
>приляпываю , вкотором 9000 записей, то запрос начинает работать
>3 минуты (правда это уже не 7 как раньше)
За ~20 мс выбираются первые несколько записей, поставьте alter session set optimizer_goal=all_rows, и время кардинально изменится. Т.к. подзапрос участвует в union, Oracle приходится выбирать все, да еще сортировать ( все же union all нужен )
Разобраться в запросе сходу тяжело, :))) могу дать общий совет - построить подзапросы так, чтобы они возвращали минимальное кол-во записей, а потом уже делать join и union, пусть даже количество "этажей" при этом вырастет.
← →
DenK_vrtz (2003-07-15 11:29) [27]McSimm2 (15.07.03 11:15), 8 - это еще не версия! :-)
Бывает 8.0.4, 8.0.5, 8.1.6, 8.1.7 ..., а 8.0 и 8i две вещи разные!
← →
McSimm2 (2003-07-15 11:39) [28]8i
← →
Sergey13 (2003-07-15 11:44) [29]2McSimm2 (15.07.03 11:15)
>Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.
А почему не принять что -1 это и есть отсутствие квартиры.
>что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.
Вююха (view) - это представление, иначе сохраненный запрос.
>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...
А не почитать ли тебе книжек по Ораклу. Это все же не ИБ, тут просто так ничего не добьешься. Оракл - рулез, но им рулить надо!
← →
ЮЮ (2003-07-16 03:16) [30]20 милисекунд * 9000 записей = 180 сек = 3 мин
Арифметика-с. Твой запрос выполняется заново для каждой из 9000 записей. Добавь связь с sobes_analiz в каждый из подзапросов в Union - и то выиграешь неимоверно
← →
petr_v_a (2003-07-16 11:56) [31]> ЮЮ © (16.07.03 03:16)
> Твой запрос выполняется заново для каждой из 9000 записей
Ну это Вы перебрали, все ж в Oracle ( да и других компаниях ) не совсем дураки сидят, 9000 раз запрос выполнять при join
← →
ЮЮ (2003-07-17 03:35) [32]>9000 раз запрос выполнять при join
Тогда запрос надо писать так, чтобы он был похож на JOIN, а не прятать весь смысл в маленьком +
WHERE ((s.ksmstreet = kv_all.ulica (+)
Это же форум программирования в Delphi, а не на C :-)
← →
ЮЮ (2003-07-17 03:42) [33]P.S. И если это не так, то на что тратятся остальные сотни тысяч милисекунд? Не на связывание же таблицы и запроса по трём полям :-)
← →
ShuraGrp (2003-07-17 12:34) [34]Not in () - это отвратительно, медленно и иногда не предсказуемо.
Предложения:
- все OR заменяются на UNION, тогда не везде нужны (+), если там NULL, так может и вязать не нужно
- Not in () заменяется на MINUS
Это пока не влезая в логику запроса
Страницы: 1 вся ветка
Форум: "Базы";
Текущий архив: 2003.08.07;
Скачать: [xml.tar.bz2];
Память: 0.54 MB
Время: 0.015 c
