| Форумы
ЧАТ |
Орешник |
|
| Форумы -» | Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG |
|
| ||
|
|
Новости |
Поиск |
FAQ |
Статьи |
Книги |
Новости VCL
| | Форумы ЧАТ |
Орешник |
|
| |||
| |||
| |||
« предыдущая ветвь | форум | следующая ветвь »
Оптимизация запроса [D7]
McSimm2 © (2003-07-15 08:46:00)Есть запрос один-ко-многим, который в свою очередь то же является запросом один-ко многим. Всё усугубляется тем, что в конечном итоге это ещё и в тройном экземпляре...
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
Мастера, как запрос соптимизировать можно ?
ShuraGrp (2003-07-17 12:34:00) [1]Not in () - это отвратительно, медленно и иногда не предсказуемо.
Предложения:
- все OR заменяются на UNION, тогда не везде нужны (+), если там NULL, так может и вязать не нужно
- Not in () заменяется на MINUS
Это пока не влезая в логику запроса
ЮЮ (2003-07-17 03:42:00) [2]P.S. И если это не так, то на что тратятся остальные сотни тысяч милисекунд? Не на связывание же таблицы и запроса по трём полям :-)
ЮЮ (2003-07-17 03:35:00) [3]>9000 раз запрос выполнять при join
Тогда запрос надо писать так, чтобы он был похож на JOIN, а не прятать весь смысл в маленьком +
WHERE ((s.ksmstreet = kv_all.ulica (+)
Это же форум программирования в Delphi, а не на C :-)
petr_v_a (2003-07-16 11:56:00) [4]> ЮЮ © (16.07.03 03:16)
> Твой запрос выполняется заново для каждой из 9000 записей
Ну это Вы перебрали, все ж в Oracle ( да и других компаниях ) не совсем дураки сидят, 9000 раз запрос выполнять при join
ЮЮ (2003-07-16 03:16:00) [5]20 милисекунд * 9000 записей = 180 сек = 3 мин
Арифметика-с. Твой запрос выполняется заново для каждой из 9000 записей. Добавь связь с sobes_analiz в каждый из подзапросов в Union - и то выиграешь неимоверно
Sergey13 (2003-07-15 11:44:00) [6]2McSimm2 (15.07.03 11:15)
>Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.
А почему не принять что -1 это и есть отсутствие квартиры.
>что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.
Вююха (view) - это представление, иначе сохраненный запрос.
>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...
А не почитать ли тебе книжек по Ораклу. Это все же не ИБ, тут просто так ничего не добьешься. Оракл - рулез, но им рулить надо!
McSimm2 (2003-07-15 11:39:00) [7]8i
DenK_vrtz (2003-07-15 11:29:00) [8]McSimm2 (15.07.03 11:15), 8 - это еще не версия! :-)
Бывает 8.0.4, 8.0.5, 8.1.6, 8.1.7 ..., а 8.0 и 8i две вещи разные!
petr_v_a (2003-07-15 11:26:00) [9]>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, пусть даже количество "этажей" при этом вырастет.
McSimm2 (2003-07-15 11:15:00) [10]>>Исчо раз повторю - избавься от NVL в конечном Where.
>>Что за поля s.ksmflat и kv_all.kvart - каков их смысл.
>>Почему нельзя сразу вставлять туда -1?
Связь таблиц происходит по полям улица, дом, квартира.
Очень возможна ситуация, когда квартиры у дома может
не быть - тогда запись не присоединится, поэтому я
пользуюсь NVL.
>>И еще - а у тебя все алиасы - таблицы, или и вьюхи есть?
>>Проблемы могут быть и в этом.
что такое вьюха ? Область видимости. Если - "да", то
все таблицы у меня ссылаются на одну и ту же схему.
>>Какая версия Оракла?
8
>>Какой оптимизатор включен по умолчанию?
не знаю - где узнать ?
>>Если стоимостной то статистика когда последний раз собиралась?
не понял ...
Sergey13 (2003-07-15 11:03:00) [11]Исчо раз повторю - избавься от NVL в конечном Where. Что за поля s.ksmflat и kv_all.kvart - каков их смысл. Почему нельзя сразу вставлять туда -1?
И еще - а у тебя все алиасы - таблицы, или и вьюхи есть? Проблемы могут быть и в этом.
Без приведения планов выполнения спор становится бессмысленным и беспредметным.
Какая версия Оракла? Какой оптимизатор включен по умолчанию? Если стоимостной то статистика когда последний раз собиралась?
DenK_vrtz (2003-07-15 10:54:00) [12]Хорошей селективностю считается совокупность тех полей, которая составляет 3-5% всех данных. Вот и смотри.
McSimm2 (2003-07-15 10:49:00) [13]Каждый индекс состоит из савокупности 3 полей, которые все вместе создают уникальность
DenK_vrtz (2003-07-15 10:48:00) [14]McSimm2 (15.07.03 10:44), не всегда помогает. Эти поля хорошей селективностью должны обладать, по отдельности или в совокупности.
McSimm2 (2003-07-15 10:44:00) [15]DenK_vrtz © (15.07.03 10:40)
Индексы у таблицы только по тем полям - по которым идёт связывание таблиц.
DenK_vrtz (2003-07-15 10:40:00) [16]McSimm2 (15.07.03 10:29), согласен на все 100% с Johnmen © (15.07.03 09:34). Пересмотри структуру, поможет!
И еще. Я не вникал с сам запрос, но на вскидку, по собственному опыту, много индексов не есть хорошо. Лучше меньше, но лучше!
Desdechado (2003-07-15 10:38:00) [17]есть подозрение, что подзапрос отрабатывает для каждой записи главного запроса
сделай лучше ХП, в которой FOR SELECT из подзапроса, а внутри анализируй результат и возвращай только нужное
McSimm2 (2003-07-15 10:29:00) [18]ВЕСЬ запрос стал работать 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 как раньше)
Так что спасибо всем кто откликнулся и указал в каком направлении копать ....
Dan (2003-07-15 10:05:00) [19]До сих пор работает ? :-))
McSimm2 (2003-07-15 09:51:00) [20]Dan (15.07.03 09:45)
Нужно попробовать
| |
Страницы: 