Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Агрегат в подзапросе сильно тормозит FETCH / 17 сообщений из 17, страница 1 из 1
28.11.2020, 14:50
    #40022985
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
Доброго!

БД FB SS 2.1.7. (поменять не могу - это сервер пользователя)
Сервер и клиент Win10 Prof

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

Мне нужно выбрать карточки изделий по некоторым параметрам, и у тех которые имеют не завершенные операции указать количество этих не завершенных операций.
Пробовал по всякому, подзапрос в запросе, джойнить подзапрос к основному запросу (поскольку не все изделия имеют не завершенные операции то тут соединял по ЛЕФТ),
создавал вьювер и джойнил к вьюверу, создавал временную таблицу и пытался залить туда агрегированные значения (вообще не вариант, более 20 минут идет запись в таблицу)...
Пытался делать запрос в виде Execute block
Бегу по выбранным изделиям и во внутреннем запросе отбираю подзапрос для данного изделия (ну это скорее уже от безисходности ))))


Если из запроса убрать все справочники, то он примет вид
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select T1.*,
       (select count(*) as OP
        from TABLE2 T2
        where T2.DATE_CLOSE is null and
              T2.CODECARDNUMBER = T1.CODECARDNUMBER) as OP
from TABLE1 T1
where T1.OTK = 1
order by T1.CARDNUM



Вот реальный запрс
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select CARDNUMBER.OTK, ZAKAZ.CLIENTCODE, ZAKAZ.NUMBERZAKAZ, ZAKAZ.DATEIN, CARDNUMBER.CARDNUM, CARDNUMBER.DATEOTK,
       RECEIPTS.CODECARDNUMBER, RECEIPTS.CARD, CARDNUMBER.CODEMODEL, CARDNUMBER.CODECOLORR, CARDNUMBER.CODERAZMER,
       (select count(*) as OP
        from OPERATIONCARD OPERATIONCARD
        where OPERATIONCARD.DATE_CLOSE is null and
              OPERATIONCARD.CODECARDNUMBER = CARDNUMBER.CODECARDNUMBER) as OP
from CARDNUMBER CARDNUMBER
inner join RECEIPTS RECEIPTS on (CARDNUMBER.CODECARDNUMBER = RECEIPTS.CODECARDNUMBER)
inner join ZAKAZ ZAKAZ on (CARDNUMBER.CODEZAKAZ = ZAKAZ.CODEZAKAZ)
inner join MODEL MODEL on (CARDNUMBER.CODEMODEL = MODEL.CODEMODEL)
inner join RAZMER RAZMER on (RAZMER.CODERAZMER = CARDNUMBER.CODERAZMER)
inner join COLORR COLORR on (COLORR.CODECOLORR = CARDNUMBER.CODECOLORR)
inner join CLIENT CLIENT on (CLIENT.CLIENTCODE = ZAKAZ.CLIENTCODE)

where RECEIPTS.ADDSALES = 'F' and
      CARDNUMBER.OTK = 1 and
      CARDNUMBER.CODEBOX_CLIENT = 0 and
      RECEIPTS.CARD = 'готовая'
order by CARDNUMBER.CARDNUM



На картинке план запроса из IBE. (приношу извинения не разобрался как вставить картинку в нужное место)
[img=]
Вариант с джойном

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select CARDNUMBER.OTK, ZAKAZ.CLIENTCODE, ZAKAZ.NUMBERZAKAZ, ZAKAZ.DATEIN, CARDNUMBER.CARDNUM, CARDNUMBER.DATEOTK,
       RECEIPTS.CODECARDNUMBER, RECEIPTS.CARD, CARDNUMBER.CODEMODEL, CARDNUMBER.CODECOLORR, CARDNUMBER.CODERAZMER,
       DD.OP
from CARDNUMBER CARDNUMBER
inner join RECEIPTS RECEIPTS on (CARDNUMBER.CODECARDNUMBER = RECEIPTS.CODECARDNUMBER)
inner join ZAKAZ ZAKAZ on (CARDNUMBER.CODEZAKAZ = ZAKAZ.CODEZAKAZ)
inner join MODEL MODEL on (CARDNUMBER.CODEMODEL = MODEL.CODEMODEL)
inner join RAZMER RAZMER on (RAZMER.CODERAZMER = CARDNUMBER.CODERAZMER)
inner join COLORR COLORR on (COLORR.CODECOLORR = CARDNUMBER.CODECOLORR)
inner join CLIENT CLIENT on (CLIENT.CLIENTCODE = ZAKAZ.CLIENTCODE)
left join(select OPERATIONCARD.CODECARDNUMBER, count(*) as OP
          from OPERATIONCARD OPERATIONCARD
          where OPERATIONCARD.DATE_CLOSE is null
          group by OPERATIONCARD.CODECARDNUMBER) as DD on DD.CODECARDNUMBER = CARDNUMBER.CODECARDNUMBER
where RECEIPTS.ADDSALES = 'F' and
      CARDNUMBER.OTK = 1 and
      CARDNUMBER.CODEBOX_CLIENT = 0 and
      RECEIPTS.CARD = 'готовая'
order by CARDNUMBER.CARDNUM



Результат тот же

В общем проблема следующая в таблице операций более 4 миллионов записей.
Запрос выполняется приемлимо около 2сек (запрс не оч. частый поэтому выполняется практически на "холодную". На горячую порядка 25мс),
однако ФЕТЧ все сводит на нет.

При выборке (среднее колмчество отбираемое основным запросом) около 4000 записей фетч идет где то около 30 сек.
Может есть какой то другой способ подсоединить агрегированные данные из одной таблицы к другой?

Спасибо.

З.Ы. Сильно не пинайте, искал по форуму, ничего не нашел. Если где то кто то помнит что есть такая тема или похожая ткните носом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 15:51
    #40022996
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоид,

посторонний вопрос - а на таблицах что, первичных и вторичных ключей вообще нет???
кроме того, зачем "картинка с планом", если план запроса вы можете вынуть текстом из IBE после prepare запроса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 16:04
    #40023002
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
CREATE UNIQUE INDEX NUM0 ON CARDNUMBER (CODECARDNUMBER);
CREATE INDEX CARDNUMBER_IDX1 ON CARDNUMBER (CARDNUM);
CREATE INDEX CARDNUMBER_IDX2 ON CARDNUMBER (CODERAZMER);
CREATE INDEX CARDNUMBER_IDX3 ON CARDNUMBER (CODECOLORR);
CREATE INDEX CARDNUMBER_IDX4 ON CARDNUMBER (CODEMODEL);
CREATE INDEX CARDNUMBER_IDX5 ON CARDNUMBER (CODEZAKAZ);
CREATE INDEX CARDNUMBER_ZAKAZDETAILED ON CARDNUMBER (CODEZAKAZDETAILED);

CREATE UNIQUE INDEX OPERATIONCARD_IDX1 ON OPERATIONCARD (CODEOPERATIONCARD);
CREATE INDEX OPERATIONCARD_IDX2 ON OPERATIONCARD (CODECARDNUMBER);
CREATE INDEX OPERATIONCARD_IDX3 ON OPERATIONCARD (NUMBERPERSON);
CREATE INDEX OPERATIONCARD_IDX4 ON OPERATIONCARD (CODEOPERATIONMODEL);
CREATE INDEX OPERATIONCARD_IDX5 ON OPERATIONCARD (ETAP);
CREATE INDEX OPERATIONCARD_IDX6 ON OPERATIONCARD (DATE_CLOSE);
CREATE INDEX OPERATIONCARD_IDX7 ON OPERATIONCARD (NUMBEROPERATION);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 16:21
    #40023004
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
kdv,
Ну вот по CTRL+F9 план запроса к первому варианту

Plan
PLAN (OPERATIONCARD INDEX (OPERATIONCARD_IDX2, OPERATIONCARD_IDX6))
PLAN SORT (JOIN (RECEIPTS INDEX (RECEIPTS_IDX4), CARDNUMBER INDEX (NUM0), RAZMER INDEX (RAZMER0), COLORR INDEX (COLORR0), ZAKAZ INDEX (ZAKAZ0), CLIENT INDEX (CLIENT0), MODEL INDEX (MODEL0)))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 16:45
    #40023011
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоид,

извиняюсь, а ЧТО ЭТО? база создавалась во времена царя Гороха, или разработчик не знал про ПК и ФК?
Это вообще все индексы? На других таблицах никаких индексов нет?
Это вопрос почти риторический, я не предлагаю тут вываливать метаданные.
Просто вот есть таблица RAZMER. И на ней что, индексов нет вообще?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:01
    #40023014
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
Сколько времени занимает выполнение запроса без агрегата (с полным фетчем!) ?
Изменился ли план запроса в этом случае ?

Если тормозит таки агрегат, то для начала я бы (хинтом) отключил индекс по OPERATIONCARD.DATE_CLOSE.
Или создал бы композит по CODECARDNUMBER, DATE_CLOSE.



PS Алиасы к таблицам - это хорошо и правильно, но когда они совпадают с именами таблиц - глупо и не нужно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:12
    #40023015
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
kdv,

Да база создавалась в InterBase, "до революции при немцах", когда еще ФБ даже не был в проекте.
Вот состав базы и диалект там еще старый. (На картинке.)
Вопрос не в том что это устарело, а в том можно ли его заставить работать шустрее.

hvlad,

Проверенно, тормозит именно агрегат.
Вот в таком виде
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select CARDNUMBER.OTK, ZAKAZ.CLIENTCODE, ZAKAZ.NUMBERZAKAZ, ZAKAZ.DATEIN, CARDNUMBER.CARDNUM, CARDNUMBER.DATEOTK,
       RECEIPTS.CODECARDNUMBER, RECEIPTS.CARD, CARDNUMBER.CODEMODEL, CARDNUMBER.CODECOLORR, CARDNUMBER.CODERAZMER
from CARDNUMBER CARDNUMBER
inner join RECEIPTS RECEIPTS on (CARDNUMBER.CODECARDNUMBER = RECEIPTS.CODECARDNUMBER)
inner join ZAKAZ ZAKAZ on (CARDNUMBER.CODEZAKAZ = ZAKAZ.CODEZAKAZ)
inner join MODEL MODEL on (CARDNUMBER.CODEMODEL = MODEL.CODEMODEL)
inner join RAZMER RAZMER on (RAZMER.CODERAZMER = CARDNUMBER.CODERAZMER)
inner join COLORR COLORR on (COLORR.CODECOLORR = CARDNUMBER.CODECOLORR)
inner join CLIENT CLIENT on (CLIENT.CLIENTCODE = ZAKAZ.CLIENTCODE)
where RECEIPTS.ADDSALES = 'F' and
      CARDNUMBER.OTK = 1 and
      CARDNUMBER.CODEBOX_CLIENT = 0 and
      RECEIPTS.CARD = 'готовая'
order by CARDNUMBER.CARDNUM


Запрос выполняется 600мс фетчится полностью - мгновенно
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:19
    #40023016
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоидДа база создавалась в InterBase, "до революции при немцах"
да что вы говорите. в Interbase 4 в 1995 году были стандартные primary/foreign key. И при чем тут FB?
Короче, по уму надо
- все индексы, имитирующие ПК и ФК убить
- создать нормальные ПК и ФК НА ВСЕХ таблицах
- после чего запрос повторить.
теоретически, агрегат этот ускорить никак не получится, надо его превратить в хранимый вместо вычисляемого. Тогда сильно полегчает.
А так - нахрена постоянно count/group by базу долбать, непонятно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:44
    #40023018
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоид
Запрос выполняется 600мс фетчится полностью - мгновенно
Я задавал ещё и вопрос о его плане.
Отвечайте на все вопросы или не отвечайте вообще, уважайте чужое время.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:45
    #40023019
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
Попробую индексы переделать.
Извините за непонятливость
авторнадо его превратить в хранимый вместо вычисляемого.
Имеется в виду ввести поле в таблицу куда помещать результат count или можно создать ХП и ее джойнить к запросу, хотя это тоже вычислимое выходит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:46
    #40023020
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
hvlad,
Прошу прощения.
Вот план от этого запроса
Plan
PLAN SORT (JOIN (RECEIPTS INDEX (RECEIPTS_IDX4), CARDNUMBER INDEX (NUM0), RAZMER INDEX (RAZMER0), COLORR INDEX (COLORR0), ZAKAZ INDEX (ZAKAZ0), CLIENT INDEX (CLIENT0), MODEL INDEX (MODEL0)))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 17:49
    #40023021
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
hvlad,

авторЕсли тормозит таки агрегат, то для начала я бы (хинтом) отключил индекс по OPERATIONCARD.DATE_CLOSE.
Или создал бы композит по CODECARDNUMBER, DATE_CLOSE.
Попробую чуть позже. Сейчас вынужден отлучиться.
Спасибо за помощь.
А да, композит по DATE_CLOSE "рассыпет " выборку на множество записей изделий по разным датам закрытия операций. Это не вариант. Создавал такой во вьювере.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE VIEW COUNTOP(
    CODECARDNUMBER,
    DATECLOSE,
    OP)
AS
select OPERATIONCARD.CODECARDNUMBER,OPERATIONCARD.DATE_CLOSE,iif(OPERATIONCARD.DATE_CLOSE is null ,count(*),null) as OP
from OPERATIONCARD OPERATIONCARD
--where OPERATIONCARD.DATE_CLOSE is null
group by OPERATIONCARD.CODECARDNUMBER,OPERATIONCARD.DATE_CLOSE


В выборке нужна одна запись с количеством не закрытых операций.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 19:41
    #40023040
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоид
А да, композит по DATE_CLOSE "рассыпет " выборку на множество записей изделий по разным датам закрытия операций. Это не вариан
Композит в данном контексте, - это композитный индекс по нескольким полям.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 20:17
    #40023047
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоидИмеется в виду ввести поле в таблицу куда помещать результат count или можно создать ХП и ее джойнить к запросу, хотя это тоже вычислимое выходит?
Не процедуру, спаси господи, это будет то же самое, только "сбоку".
Насчет хранимого агрегата - смотрите, у вас сейчас там 4 миллиона записей, и "тормозит". Завтра, через месяц, через год у вас будет 10 миллионов записей (допустим), и тормозить будет еще больше.
Зачем постоянно считать одно и то же? сделайте таблицу, в которой будут вот эти самые агрегированные значения. Пересчитывать их можно процедурой, чем угодно, периодически, с нужным интервалом.
Например, в бухгалтериях так всегда делается - типа "закрытие месяца" - считаются данные за месяц, и сохраняются, чтобы их потом не считать каждый раз снова.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2020, 21:06
    #40023051
Старый плюшевый мишка
Старый плюшевый мишка
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
kdv
гуманоидИмеется в виду ввести поле в таблицу куда помещать результат count или можно создать ХП и ее джойнить к запросу, хотя это тоже вычислимое выходит?

Не процедуру, спаси господи, это будет то же самое, только "сбоку".
Насчет хранимого агрегата - смотрите, у вас сейчас там 4 миллиона записей, и "тормозит". Завтра, через месяц, через год у вас будет 10 миллионов записей (допустим), и тормозить будет еще больше.
Зачем постоянно считать одно и то же? сделайте таблицу, в которой будут вот эти самые агрегированные значения.

По скромному моему разумению тут и отдельная таблица не нужна. Поле в таблице CARDNUMBER, содержащее количество записей с null в OPERATIONCARD.DATE_CLOSE, ведущееся на триггерах OPERATIONCARD.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.11.2020, 01:40
    #40023092
гуманоид
гуманоид
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
kdvНапример, в бухгалтериях так всегда делается - типа "закрытие месяца" - считаются данные за месяц, и сохраняются, чтобы их потом не считать каждый раз снова.
Ну, тут задача немного не бухгалтерская. Информация динамическая и нужно знать ее в момент запроса пользователем. Посему раз в месяц не катит.
Старый плюшевый мишка


По скромному моему разумению тут и отдельная таблица не нужна. Поле в таблице CARDNUMBER, содержащее количество записей с null в OPERATIONCARD.DATE_CLOSE, ведущееся на триггерах OPERATIONCARD.

Да, да. Так и сделал. Поле туда завет и попытался сперва его отапдейтить. Как писал выше, если по полной апдейтить, то более 20 минут. Завтра поганяю. Тут тоже не все так просто. Одновременно получают дату закрытия до нескольких тысячей записей. Надо посмотреть как триггерок с этим совладает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.11.2020, 02:12
    #40023096
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегат в подзапросе сильно тормозит FETCH
гуманоидНу, тут задача немного не бухгалтерская. Информация динамическая и нужно знать ее в момент запроса пользователем
бухгалтерия тут просто как пример.
А насчет динамической информации и "нужно знать" - это сказки. Прямо вот пользователь хочет узнать, какие там количества по 4м миллионам единиц информации? Да он будет тупо смотреть в такой отчет полдня.

К примеру, тут вероятно не хранимые агрегаты нужны, а раделение на "закрытые операции" и незакрытые. У нас была система, в которой хранилось дофига данных, и вот заполнено полезной информацией было только 30%. А считать приходилось всё, целиком.
Ну и взяли, создали еще одну таблицу, в которую заносили только "заполненную" информацию. А остальная шлабуда была в большой таблице. Поскольку дополнительная таблица была короче, ее объем получился раз в 20 меньше исходной. И отчеты стали просто летать.
Это при том, что часть информации дублировалась в старой и новой таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Агрегат в подзапросе сильно тормозит FETCH / 17 сообщений из 17, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 171ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1560180]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 17ms
total:  171ms
x
x
Закрыть


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