Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Оптмизация вызова ХП в предложении where / 20 сообщений из 20, страница 1 из 1
26.10.2016, 16:44
    #39334628
KreatorXXI
KreatorXXI
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
FB3. Хочется ускорить запрос. Или для начала понять. Вот такой запрос:
Код: sql
1.
2.
3.
select a.id_sotr
from sotr a
where a.dat_uv is null and a.ruk is null and ( select out1 from sotr_rab(a.id_sotr, 91, '2016-10-01', '2016-10-25') ) = '1'


Его план:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
План
PLAN (SOTR_RAB NATURAL)
PLAN (A NATURAL)

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 250ms
Среднее время на получение одной записи = 0,85 ms
Current memory = 59 596 944
Max memory = 111 757 384
Memory buffers = 12 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Чтений из кэша = 189 506


Нормально я бы сказал. Всё устраивает.
Делаю запрос сложнее:

Код: sql
1.
2.
3.
4.
5.
6.
7.
select a.id_sotr, sum(r.trud)
from sotr a
     inner join rz r on r.id_sotr=a.id_sotr
     inner join umass u on u.id_ums=r.id_ums
where a.id_sotr=r.id_sotr and a.dat_uv is null and a.ruk is null and ( select out1 from sotr_rab(a.id_sotr, 91, '2016-10-01', '2016-10-25') ) = '1'
      and u.dat_master between '2016-10-01' and '2016-10-25'
group by 1


Его план:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
План
PLAN (SOTR_RAB NATURAL)
PLAN SORT (JOIN (U INDEX (UMASS_IDX5), R INDEX (FK_RZ_RELATIONS_UMASS), A INDEX (PK_SOTR)))

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 3s 73ms
Среднее время на получение одной записи = 21,49 ms
Current memory = 59 603 488
Max memory = 111 757 384
Memory buffers = 12 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Чтений из кэша = 2 437 738


Получаю сильный перепробег по таблицам, задействованным в хранимке. От перемены порядка джойнов таблиц разницы нет. Как можно уменьшить количество "вызовов" хранимки?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 17:05
    #39334654
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

для оптимизатора хранимая процедура - "черный ящик", о чем говорит PLAN (SOTR_RAB NATURAL). "Ускорить" можно разве что разглядывая планы запросов в процедуре (если они там есть). А вообще, если процедура написана как функция, то разумеется, она будет вызываться столько раз, к скольким записям она может быть "присоединена".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 17:12
    #39334659
Arioch
Arioch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI
Код: sql
1.
2.
3.
4.
5.
select a.id_sotr, sum(r.trud)
from sotr a
     inner join rz r on r.id_sotr=a.id_sotr
     inner join umass u on u.id_ums=r.id_ums
where a.id_sotr=r.id_sotr and a.dat_uv is null




Оппа - смесь явных и неявных джойнов в одном запросе.
По-моему это даже в FAQ было, что жарптиц от такого зачастую "пухнет и дохнет"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 17:21
    #39334669
MikeDD
MikeDD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI, я бы переделал процедуру так, чтобы она возвращала не одно значение, а набор данных id_sotr, out1. А затем пользовал ее как-то так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.id_sotr
 from sotr a
 join (
  select id_sotr, out1
   from sotr_rab(91, '2016-10-01', '2016-10-25')
 ) p on p.id_sotr=a.id_sotr
 where a.dat_uv is null 
     and a.ruk is null 
     and p.out1 = '1'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 17:32
    #39334675
Arioch
Arioch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
Если бы была конструкция, превращающая функцию в рекордсет, который можно потом сджойнить....

Что-то аналогичное MAP из функциональных языков

И потом использовать CTE....

Что-то навроде такого (текст намеренно неправильный, просто показать идею)

WITH DATA as
select a.id_sotr as sotr, sum(r.trud) as trud from sotr a ... join ... join .... where (условия не касающиеся SP)

SELECT d.sotr, d.trud
FROM DATA D
JOIN (Select out1, x.id_sotr from sotr_rab( x.sotr, ....) from (select distinct sotr from DATA) as x ) as SP
ON x.sotr = d.sotr
WHERE x.out1 = '1'

Собственно говоря, с обычной функцией, у которой результат только один и безымянный так бы и сработало наверное...

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
WITH DATA as 
   select a.id_sotr as sotr, sum(r.trud) as trud from sotr a ... join  ... join .... where (условия не касающиеся SP)
DO

SELECT d.sotr, d.trud, sp.out1 
FROM DATA D, 
 (Select DISTINCT(x.sotr), sotr_rab( x.sotr, ....) as out1 from DATA x) SP
WHERE sp.out1 = '1' and SP.sotr = D.sotr



Или с обещанным Lateral Join ....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 17:37
    #39334683
Мимопроходящий
Мимопроходящий
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
где вы понабирали таких хрустальных шаров?
или содержимое ХП пациента уже таки опубликовано в газетах?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 18:16
    #39334715
dimitr
dimitr
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
искомое достигается началом джойна с таблицы SOTR - либо через left join, либо через + 0 в нужном месте
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 11:28
    #39335134
KreatorXXI
KreatorXXI
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
dimitr,
А можно пример? Как? У меня с sotr'а начинается.
"a.id_sotr=r.id_sotr" в where, конечно полная хрень. Не удалил после эксперимента. Но на результат не влияет.
И ещё. Я бы вытащил записи из sotr'а list'ом. И в конструкцию "in" засунул бы. Работает нормально, есть индекс. Но боюсь упереться в ограничение 1500 "членов". Это ограничение в "in" актуально для FB3?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 11:35
    #39335152
WildSery
WildSery
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

Это как бы ты её засунул? Через Execute Statement что ли?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 11:37
    #39335157
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

попробуй вот так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
WITH A (
SELECT
    SOTR.ID_SOTR
FROM
    SOTR
    LEFT JOIN SOTR_RAB(SOTR.ID_SOTR, 91, '2016-10-01', '2016-10-25') P ON 1=1
WHERE SOTR.DAT_UV IS NULL
  AND SOTR.RUK IS NULL
  AND P.OUT1  = '1'
)
SELECT
    A.ID_SOTR,
    SUM(R.TRUD)
FROM
    A
    JOIN RZ R ON R.ID_SOTR = A.ID_SOTR+0
    JOIN UMASS U ON U.ID_UMS = R.ID_UMS
WHERE U.DAT_MASTER BETWEEN '2016-10-01' AND '2016-10-25'
GROUP BY 1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 14:12
    #39335376
Arioch
Arioch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
Симонов Денис,

Мне кажется, ты пропустил (или мы по разному поняли ) пожелания:

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

То есть на вход хранимки нужно подать не все ID_SOTR куак у тебя (в этом реально нет сложностей), а DISTINCT ID_SOTR

И вот тут уже сложности...

Может быть группировкой сымитировать distinct ?

Симонов Денис
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
WITH A (
SELECT
    SOTR.ID_SOTR, Count(SOTR.*)
FROM
    SOTR
    LEFT JOIN SOTR_RAB(SOTR.ID_SOTR, 91, '2016-10-01', '2016-10-25') P ON 1=1
WHERE SOTR.DAT_UV IS NULL
  AND SOTR.RUK IS NULL
  AND P.OUT1  = '1'
GROUP BY SOTR.ID_SOTR
)
SELECT......



Хотч что-то мне кажется не согласиться FB на такой финт ушами...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 14:31
    #39335400
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
Arioch,

по идее в моём предложении он должен уменьшится, по крайней мере быть не сильно большим чем в запросе 1, за счёт однократного выполнения CTE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 14:33
    #39335404
KreatorXXI
KreatorXXI
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
Симонов Денис,

Нет, перепробовал очень много разных вариантов, и CTE в том числе (и Ваш попробовал, у Вас "as" отутствует). Везде либо также либо хуже.
Единственный более-менее вот такой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select * from (
                     select a.id_sotr idsotr, sum(r.trud) s
                     from sotr a
                            inner join rz r on r.id_sotr=a.id_sotr
                            inner join umass u on u.id_ums=r.id_ums
                     where a.dat_uv is null and a.ruk is null
                              and u.dat_master between '2016-10-01' and '2016-10-25'
                     group by 1
                  )
where s is not null and ( select out1 from sotr_rab(idsotr, 91, '2016-10-01', '2016-10-25') ) = '1'


И то, наверно из-за того, что фильтр по umass за месяц. За год уже похуже. Но это и не надо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 14:40
    #39335416
Arioch
Arioch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

а вот такой запрос сработает ?

select sp.out1, tab.id_sotr
from sotr_rab(tab.id_sotr, 91, '2016-10-01', '2016-10-25'),
(select distinct ID_SOTR from sotr where ............. )

усли да - то завернуть его ещё в одно derived table и джойнить уже с ним ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 14:41
    #39335417
Arioch
Arioch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
ну либо придется тебе всё-таки не выпендриваться, а делать явную GTT
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 15:19
    #39335483
dimitr
dimitr
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXIУ меня с sotr'а начинается.
в запросе, но не в плане

KreatorXXIА можно пример?
Код: sql
1.
2.
3.
4.
5.
6.
7.
select a.id_sotr, sum(r.trud)
from sotr a
     inner join rz r on r.id_sotr=a.id_sotr+0
     inner join umass u on u.id_ums=r.id_ums
where a.dat_uv is null and a.ruk is null and ( select out1 from sotr_rab(a.id_sotr, 91, '2016-10-01', '2016-10-25') ) = '1'
      and u.dat_master between '2016-10-01' and '2016-10-25'
group by 1



но вообще тут все сильно зависит от селективности between-а...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 16:07
    #39335567
KreatorXXI
KreatorXXI
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
dimitr,

Этот запрос, действительно, работает по другому. К сожалению:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
План
PLAN (SOTR_RAB NATURAL)
PLAN JOIN (A ORDER PK_SOTR, R INDEX (FK_RZ_RELATIONS_SOTR), U INDEX (PK_UMASS))

------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 11s 825ms
Среднее время на получение одной записи = 82,69 ms
Current memory = 62 812 144
Max memory = 93 915 800
Memory buffers = 12 000
Reads from disk to cache = 584 431
Writes from cache to disk = 0
Чтений из кэша = 13 206 410


Он перестаёт фетчить umass по индексированному полю dat_master, что совсем неправильно, потому что и umass и rz очень большие. Изначально планировщик запросов FB ведёт себя абсолютно правильно - сначала нужно фильтровать umass по dat_master. Всё остальное - плохо.
Видимо, надо ждать когда разработчики сделают что-нибудь с конструкцией "in". Где-то я слышал об этом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.10.2016, 16:11
    #39335575
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

в данном случае оптимизация in не поможет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.10.2016, 10:45
    #39335985
KreatorXXI
KreatorXXI
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
Симонов Денис,

это смотря какую оптимизацию сделать. Не знаю Ваших планов. В моём случае нужно вытащить одним запросом id-шники, запихнуть их в "in" и будет счастье (может и не кул с точки зрения феншуя, но проблему решает). Другое дело, что я могу упереться в ограничение 1500 элементов. Откуда, кстати, оно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.10.2016, 10:50
    #39335998
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптмизация вызова ХП в предложении where
KreatorXXI,

с 1500 элементами никаких изменений скорее всего не будет.
А вот in/exists в ряде случаев можно заставить работать через SEMI JOIN с различными вариантами NESTED LOOP/MERGE/HASH или преобразовывать (если это более оптимально) в JOIN + DISTINCT
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Оптмизация вызова ХП в предложении where / 20 сообщений из 20, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 172ms
Закрыть
start [/forum/topic.php?fid=40&tablet=1&tid=1561885]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 50ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 15ms
total:  172ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]