Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 Оптимизация времени выполнения скрипта  [new]
PavelBardRu
Member

Откуда:
Сообщений: 2
Столкнулся с проблемой значительного увеличения времени работы запроса при связывании двух простых подзапросов.
Каждый из них работает с таблицами примерно по миллиону записей и выдаёт несколько десятков строк и 5-6 полей, при этом длится менее минуты.
Когда соединяю их через left join, время работы такого объединённого скрипта возрастает до часа!
Конструкция такая:
select t1.a, t1.b, t1.c, t2.d from (
select a, b, c from 
....
) t1
left join (
select a, b, d from
...
) t2 on t1.a=t2.a and t1.b=t2.b

Сервер Firebird 2.5.7, работаю через IBExpert версия 2016.11.7.2
Как можно убыстрить процесс?
16 июн 17, 15:38    [20570402]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 192
PavelBardRu,

Из примера трудно понять чего к чему.
Посмотри в IBExpert'е закладку "Анализ производительности". Скорее всего при left join идёт перемножение 1лям на 1лям.
16 июн 17, 16:32    [20570622]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
PavelBardRu
Member

Откуда:
Сообщений: 2
KreatorXXI, я недавно работаю с FireBird'ом, возможно, проблемы от этого.
Раньше я в таких случаях запихивал результаты подзапросов во временные таблицы, а потом их джойнил. Если результаты подзапросов были маленькие (как сейчас у меня, 60 записей), то сджойнивание их дополнительного времени практически не занимало. Время счета равнялось сумме времён подзапросов.
То, что "перемножаются" миллионы записей, я подозреваю, но возникает вопрос: можно ли объяснить FireBird'у, что надо джойнить уже результаты подзапросов, не прокручивая полные таблицы?
Вот сейчас замерил. Два подзапроса.
Первый 3 колонки 87 строк Execute time = 6s 172ms
Второй 4 колонки 74 записи Execute time = 3s 906ms
Джойню - Execute time = 3m 42s 62ms
16 июн 17, 17:58    [20570841]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 43379

PavelBardRu
можно ли объяснить FireBird'у, что надо джойнить уже результаты подзапросов, не
прокручивая полные таблицы?

С левым соединением - нет.

Posted via ActualForum NNTP Server 1.5

16 июн 17, 18:02    [20570854]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
PavelBardRu,

firebird пока не умеет делать внешние соединения с использованием алгоритмов hash/merge. На внутренних соединениях в 2.5 будет использован алгоритм соединения merge, в 3.0 - hash join. Поэтому такие соединения будут делаться относительно быстро.
Возможно 4.0 будет уметь делать внешние соединения с использованием hash join.
17 июн 17, 15:19    [20571987]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
PavelBardRu,

Если данных из двух подзапросов на несколько десятков записей, то "убивание" внешних условий должно облегчить ситуацию.

...
) t2 on t1.a+0=t2.a+0 and t1.b+0=t2.b+0
17 июн 17, 18:21    [20572152]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
afgm,

Чё это вдруг?
19 июн 17, 10:24    [20573841]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
afgm,

не должно, а может и то далеко не всегда, причём только для join, а не left join. Это не правило, а скорее исключение когда hash/merge join работает лучше чем nested loop с использованием индексов
19 июн 17, 10:29    [20573855]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
Симонов Денис,

Чтобы MERGE сработал, ещё сортировка нужна, скорее всего.
19 июн 17, 12:08    [20574130]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
WildSery,

конечно нужна, вот только есть оптимизатор выберет алгоритм MERGE JOIN, то сортировку он сам воткнёт не зависисмо от того отсортированы ли данные по необходимым полям соединения или нет.

Ну и сортировка "десятков строк" это не дорого. Опять же надо пробовать, как я уже сказал принудительный merge/hash это скорее исключение чем правило. Когда нет возможность использования индексов для соединения он и так будет выбран, но не для left join. Надеюсь в 4.0 это поправят (про outer join).
19 июн 17, 12:17    [20574152]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
Симонов Денис,

У меня почему-то никогда не получалось "в обратную сторону".
Когда выполняешь все условия, и записи выбираются в порядке соединения (дополнительно сортировкой или в порядке индекса), тогда MERGE подхватывается.
А вот наоборот - чтобы сервер сам добавил сортировку, мне как-то не попадалось.
19 июн 17, 13:03    [20574372]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
WildSery,

ну так ты не отключал использование индексов. Просто попробуй

select count(*)
from T1
join T2 on T1.F1+0 = T2.F2+0


вот на моей базе примерчик на 2.5

SELECT
    COUNT(*)
FROM
    FARM
    JOIN OWNERSHIP ON OWNERSHIP.CODE_OWNERSHIP+0 = FARM.CODE_OWNERSHIP+0


план
PLAN MERGE (SORT (FARM NATURAL), SORT (OWNERSHIP NATURAL))
19 июн 17, 13:11    [20574411]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
WildSery
afgm,

Чё это вдруг?

Есть подозрение, что условия джойна объединяются с внутренними, из-за этого тормоза (могу ошибаться, т.к. работаю и с 3 и 4 версией, там проброс изменился). "Материализовать" и уже потом перемножением соединить записи не должно быть долго, с учётом их небольшого количества.
19 июн 17, 13:21    [20574458]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
afgm,

в 3.0 merge join заменён на hash join. В остальном почти всё так же.
19 июн 17, 13:29    [20574486]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
afgm,

Так в том-то и дело, что это всё до 4-ки не сработает.
19 июн 17, 13:41    [20574526]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
WildSery, afgm

в оптимизаторе 4.0 по сравнению с 3.0 пока ни чего не менялось
19 июн 17, 13:51    [20574555]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
Симонов Денис,

Так я и не утверждаю, что в 4 точно будет :) Я надеюсь!
19 июн 17, 14:04    [20574588]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
WildSery
Так в том-то и дело, что это всё до 4-ки не сработает.

Чёйта?
2 запроса по минуте
выдают не сколько десятков (пусть сотен записей) и 6 полей (сделаем побольше).
итого 2 минуты на формирование поздапросов. Далее неэффективным пребирающим left_join-ом объединяем.
И тут вдруг час. С какого?
select count(*) from rdb$types
-- 228

with A as (
  select t1.rdb$type_name as type_name,
    --поля для объёма
    t1.RDB$TYPE,
    t1.RDB$DESCRIPTION,
    t1.RDB$SYSTEM_FLAG,
    'text string text string text string text string text string text string text string text string' as txt1,
    'text string text string text string text string text string text string text string text string' as txt2,
    'text string text string text string text string text string text string text string text string' as txt3,
    'text string text string text string text string text string text string text string text string' as txt4,
    'text string text string text string text string text string text string text string text string' as txt5,
    'text string text string text string text string text string text string text string text string' as txt6,
    'text string text string text string text string text string text string text string text string' as txt7,
    'text string text string text string text string text string text string text string text string' as txt8
   from rdb$types t1
)
select count(*) from A A1
left join A A2 on A1.type_name||'' = A2.type_name||''
-- PLAN JOIN (A1 T1 NATURAL, A2 T1 NATURAL)
-- Execute time = 62ms
19 июн 17, 15:46    [20575022]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
Симонов Денис
в 3.0 merge join заменён на hash join. В остальном почти всё так же.

Ещё проброс условий внутрь представлений.
19 июн 17, 15:46    [20575026]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
afgm,

Я не понял, что ты хотел сказать. И к чему этот странный пример.
19 июн 17, 15:55    [20575062]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 7137
afgm,

попробовал. Твоё условие A1.type_name||'' = A2.type_name||'' скорости не добавило, как я и ожидал
19 июн 17, 16:02    [20575102]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
WildSery
afgm,

Я не понял, что ты хотел сказать. И к чему этот странный пример.

Я хотел сказать что неэффективный левый джойна на плевом объеме все равно должен отработать за вменяемое время.
19 июн 17, 17:49    [20575639]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
afgm
Member

Откуда:
Сообщений: 539
Симонов Денис
afgm,

попробовал. Твоё условие A1.type_name||'' = A2.type_name||'' скорости не добавило, как я и ожидал

Так оно должно было замедлить.
19 июн 17, 17:51    [20575649]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация времени выполнения скрипта  [new]
WildSery
Member

Откуда: да, оттуда.
Сообщений: 12674
afgm,

Ага. Но пример неудачный.
20 июн 17, 09:11    [20576466]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить