iBase.ru Forum

Форум по InterBase, Firebird и Yaffil
http://ibaseforum.ru/

Убивающий IN?

http://ibaseforum.ru/viewtopic.php?f=4&t=4152

Страница 1 из 2

Убивающий IN?

Добавлено: 20 дек 2007, 12:05
stix-s
балуюсь с запросами от скуки
запрос

Код: Выделить всё

select c.phone_caller from calls c
where c.phone_caller in
(select rx.xml_phone from
(select substring(lx.fxml_operation from 30 for 10) as xml_phone,
 lx.fxml_operation_type as o_type
from log_xml lx
where
 (lx.fxml_operation_type='delete_' and
 lx.fxml_operation containing('346'))
union all
select substring(lx.fxml_operation from 31 for 10) as xml_phone,
 lx.fxml_operation_type as o_type
from log_xml lx
where
 (lx.fxml_operation_type='insert_' and
  lx.fxml_operation containing('346'))) as rx
group by
rx.xml_phone
having
count(rx.xml_phone)=1)
and c.date_call>'01.08.2007'
group by c.phone_caller
заставил FB2 сожрать процесор на 100% (мучается уже 6-й час :) ), но с других клиентов запросы выполняет, правда заторможенно.
аналогичный по результату запрос без IN
выполняется меньше секунды
в phone_caller 1,5 млн записей
в log_xml 3 тыс
IN в запросе для FB так страшен?

Добавлено: 20 дек 2007, 12:28
kdv
IN в запросе для FB так страшен?
www.ibase.ru/devinfo/joins.htm
там про in объяснено.

Добавлено: 20 дек 2007, 13:09
stix-s
kdv писал(а):
IN в запросе для FB так страшен?
www.ibase.ru/devinfo/joins.htm
там про in объяснено.
дык не 6 часов молотить-то :(
мне вообще интересно-запрос выполнится когда нить или это уход в вечность
пока надоело, прибил клиента, но на ночь запущу :)

Добавлено: 20 дек 2007, 13:35
kdv
дык не 6 часов молотить-то
а почему бы и нет? полтора миллиона раз выполнить вложенный запрос, а потом все это сложить...
Ты план смотрел?
мне вообще интересно-запрос выполнится когда нить или это уход в вечность
см. выше. вместо баловства с запросами учи join, методы доступа, смотри планы.

Добавлено: 20 дек 2007, 13:39
stix-s
kdv писал(а):
дык не 6 часов молотить-то
а почему бы и нет? полтора миллиона раз выполнить вложенный запрос, а потом все это сложить...
Ты план смотрел?
мне вообще интересно-запрос выполнится когда нить или это уход в вечность
см. выше. вместо баловства с запросами учи join, методы доступа, смотри планы.
а вот с джойном как раз меньше секунды и выполняется :)
угу читаю, учу матчасть и постоянно на практике проверяю :)

Добавлено: 20 дек 2007, 13:49
stix-s
а этта, а почему полтора миллиона раз подзапрос?
я полагал, что сначала фильтр

Код: Выделить всё

and c.date_call>'01.08.2007'
отработает, там всего-то порядка 50 тыс на выходе
блин, торможу я :( у меня условие в HAVING :( а не в Where

Добавлено: 20 дек 2007, 14:27
WildSery
stix-s писал(а):

Код: Выделить всё

and c.date_call>'01.08.2007'
отработает, там всего-то порядка 50 тыс на выходе
Без плана некурится.

Добавлено: 20 дек 2007, 14:33
stix-s
WildSery писал(а):
stix-s писал(а):

Код: Выделить всё

and c.date_call>'01.08.2007'
отработает, там всего-то порядка 50 тыс на выходе
Без плана некурится.

Код: Выделить всё

PLAN (RX LX NATURAL) PLAN (RX LX2 NATURAL) PLAN (C ORDER PHONE_CALLS)
угу, надо было мне сразу его привести :(
LX2 - нижний запрос в UNION

Добавлено: 20 дек 2007, 14:52
stix-s
тэкс, переделаем

Код: Выделить всё

select c.phone_caller from calls c
where c.phone_caller in
(select rx.xml_phone from
(select substring(lx.fxml_operation from 30 for 10) as xml_phone,
 lx.fxml_operation_type as o_type
from log_xml lx
where
 (lx.fxml_operation_type='delete_phone' and
 lx.fxml_operation containing('346'))
union all
select substring(lx2.fxml_operation from 31 for 10) as xml_phone,
 lx2.fxml_operation_type as o_type
from log_xml lx2
where
 (lx2.fxml_operation_type='insert_phone_migration' and
  lx2.fxml_operation containing('346'))) as rx
where c.date_call>'01.08.2007'
group by
rx.xml_phone
having
count(rx.xml_phone)=1)
--and c.date_call>'01.08.2007'
group by c.phone_caller

Адаптированный план
PLAN SORT ((RX LX NATURAL) PLAN (RX LX2 NATURAL)) PLAN (C ORDER PHONE_CALLS)

Добавлено: 20 дек 2007, 16:08
WildSery
Фигасе. Такое работает вообще? Хотя вроде ничего не препятствует.
Мне бы и в голову так написать не пришло :shock:

Добавлено: 21 дек 2007, 06:32
stix-s
WildSery писал(а):Фигасе. Такое работает вообще? Хотя вроде ничего не препятствует.
Мне бы и в голову так написать не пришло :shock:
это плод труда воспаленного мозга :)
насчет работает - не уверен, запуск на ночь результата не выдал :(
никак не могу понять эту часть плана

Код: Выделить всё

PLAN (C ORDER PHONE_CALLS)
выглядит, как объединение с предыдущими частями с навигацией по индексу
но почему объединение?

Добавлено: 21 дек 2007, 09:26
kdv
но почему объединение?
где ты видишь объединение? объединение это JOIN. у тебя в плане есть JOIN? Нет. Там три отдельных слова план.

Добавлено: 21 дек 2007, 09:31
stix-s
kdv писал(а):
но почему объединение?
где ты видишь объединение? объединение это JOIN. у тебя в плане есть JOIN? Нет. Там три отдельных слова план.
вот и я про тоже в "Методах доступа" http://ibase.ru/devinfo/dataaccesspaths.htm join это слияние, а union - объединение
В плане выполнения объединение отображается отдельными планами на каждый из входных потоков.

Добавлено: 21 дек 2007, 11:35
kdv
не путай постное с пресным.

Добавлено: 21 дек 2007, 11:46
stix-s
kdv писал(а):не путай постное с пресным.
не асилил :(
все, прибил запрос, 20 часов птичка мучалась - результата нет

Добавлено: 21 дек 2007, 12:45
kdv
прочитай еще раз dataaccesspaths. оцени объемы данных в таблицах.
посмотри на план. подумай.
нафига такие запросы выполнять - неясно. сразу же видно, что это жопное тормозилище.

1. containing не даст использовать индекс.
2. по fxml_operation_type как я вижу, индекса нет

значит перебор этой таблицы будет от 1 до n записей (RX LX NATURAL). в среднем, считай 1500, или 2000, если у тебя в той таблице 3000 записей.

3. group by приводит к перебору таблицы в 1.5 миллионов записей в порядке индекса(C ORDER PHONE_CALLS) . Косвенно это означает, что таблица будет перебираться с повторяющимися чтениями одних и тех же страниц. т.е. будет жуткий дисковый ввод-вывод.
об этом читать тут

http://www.sql.ru/forum/actualthread.as ... 14#2535391

4. теперь, умножаем 1.5 миллиона, выбираемые вот таким мощным способом, на 2000. получаем 3 миллиарда перебираемых "записей". Причем, полтора миллиона раз еще и выполняем подзапрос.

Ты когда пишешь такой запрос, по нему (и по его плану) совершенно ясно видно, как и чего будет делаться. А раз есть такое понимание, значит можно этот запрос переделать, например на процедуру.

Добавлено: 21 дек 2007, 13:17
stix-s
kdv писал(а): прочитай еще раз dataaccesspaths. оцени объемы данных в таблицах.
посмотри на план. подумай.
нафига такие запросы выполнять - неясно. сразу же видно, что это жопное тормозилище.
перечитываю постоянно
я вот не могу план понять, вот в чем беда моя :(
kdv писал(а): 1. containing не даст использовать индекс.
2. по fxml_operation_type как я вижу, индекса нет
с containing понятно и индекса нет, так и задумывалось
kdv писал(а): значит перебор этой таблицы будет от 1 до n записей (RX LX NATURAL). в среднем, считай 1500, или 2000, если у тебя в той таблице 3000 записей.
3. group by приводит к перебору таблицы в 1.5 миллионов записей в порядке индекса(C ORDER PHONE_CALLS) . Косвенно это означает, что таблица будет перебираться с повторяющимися чтениями одних и тех же страниц. т.е. будет жуткий дисковый ввод-вывод.
об этом читать тут

http://www.sql.ru/forum/actualthread.as ... 14#2535391

4. теперь, умножаем 1.5 миллиона, выбираемые вот таким мощным способом, на 2000. получаем 3 миллиарда перебираемых "записей". Причем, полтора миллиона раз еще и выполняем подзапрос.
вот я и хочу это научиться из плана понимать, пока плохо получается :(
кстати, а вот диск-то как раз и спокоен, очередь к диску около 0 болтается, проц на 100% загружен, но другим потокам время выделяет, FB с других клиентов другие запросы выполняет
kdv писал(а): Ты когда пишешь такой запрос, по нему (и по его плану) совершенно ясно видно, как и чего будет делаться. А раз есть такое понимание, значит можно этот запрос переделать, например на процедуру.
по плану мне пока не ясно, а запрос-то, это не проблема
есть нормальный вариант:

Код: Выделить всё

--выполнение-порядка секунды
select c.phone_caller from calls c
join
(select rx.xml_phone from
(select substring(lx.fxml_operation from 30 for 10) as xml_phone,
 lx.fxml_operation_type as o_type
from log_xml lx
where
 (lx.fxml_operation_type='delete_phone' and
 lx.fxml_operation containing('346'))
union all
select substring(lx2.fxml_operation from 31 for 10) as xml_phone,
 lx2.fxml_operation_type as o_type
from log_xml lx2
where
 (lx2.fxml_operation_type='insert_phone_migration' and
  lx2.fxml_operation containing('346'))) as rx
group by
rx.xml_phone
having
count(rx.xml_phone)=1)as r
on c.phone_caller=r.xml_phone
where c.date_call>'01.11.2007'
group by c.phone_caller

План
PLAN SORT (JOIN ((R RX LX NATURAL)
PLAN (R RX LX2 NATURAL), C INDEX (PHONE_CALLS)))
Задача-то не реальная, попались под руку нормальные объемы данных, вот и решил потренировать мозг

Добавлено: 21 дек 2007, 13:33
kdv
а вот диск-то как раз и спокоен, очередь к диску около 0 болтается, проц на 100% загружен
значит, ситуация еще хуже.
1. отсутствие обращений к диску и 100% загруз проца означает fetches.
т.е. обращение к страницам в памяти.
2. от механизма выполнения order в плане никуда не деться. Значит, в некоторые интервалы между загрузкой проца сервер еще и таким способом перебирает данные на диске.

исходя из двух пунктов, я вообще не могу оценить, закончится-ли такой запрос в обозримое время.
по плану мне пока не ясно
ну, мне тоже не сразу стало ясно. И статьи DE тогда еще не было. :)
зато я читал Тиори и Фрая.
есть нормальный вариант
его основное отличие - INDEX вместо ORDER для PHONE_CALLS. А по времени?

Добавлено: 21 дек 2007, 13:44
stix-s
kdv писал(а):
есть нормальный вариант
его основное отличие - INDEX вместо ORDER для PHONE_CALLS. А по времени?

Код: Выделить всё

Адаптированный план
PLAN SORT (JOIN ((R RX LX NATURAL) PLAN (R RX LX2 NATURAL), C INDEX (PHONE_CALLS)))

------ Performance info ------
Prepare time = 0ms
Execute time = 360ms
Avg fetch time = 17,14 ms
Current memory = 4 527 988
Max memory = 4 545 216
Memory buffers = 20 480
Reads from disk to cache = 1 725
Writes from cache to disk = 0
Fetches from cache = 94 272

Добавлено: 21 дек 2007, 14:07
kdv
C INDEX (PHONE_CALLS)
что за индекс? можешь ответить себе. мне не надо, ты и так уже умучил информацию по частям сообщать :)
Часовой пояс: UTC+03:00
Страница 1 из 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/