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

Откуда:
Сообщений: 109
Доброго времени суток.
Есть 2 таблицы (для примера): Table1 "легкая" и Table2 "очень тяжелая".
Упрощенный запрос для этих таблиц выглядит так:
select t1.id, (select sum(t2.amount) from table2 t2 where t2.id_table1 = t1.id) as sum_amount
from table1 t1
where t1.....

работает достаточно быстро, но когда появляется необходимость сделать проверку по sum_amount, нужно приписывать запрос так:
select t1.id, tt.sum_amount, iif(tt.sum_amount > 1000, 1, 0)
from table1 t1
left join (select t2.id_table1, sum(t2.amount) as sum_amount
           from table2 t2
           order by t2.id) tt on tt.id_table1 = t1.id)
where t1.....

или так:
select id, sum_amount, iif(sum_amount > 1000, 1, 0)
from
(select t1.id, (select sum(t2.amount) from table2 t2 where t2.id_table1 = t1.id) as sum_amount
from table1 t1
where t1.....)

Первый работает долго, второй - быстро. Есть еще варианты с GROUP BY, но они не всегда подходят. Написан не очень умный парсер запросов, и второй запрос распарсить и подставить что-то в него он не может. Такая же сложность для запросов с group by.
Может есть варианты для проверки суммы в первом запросе, без select в selectе и без group by? Firebird 3.0 поддерживает over, но что-то я не могу понять как его использовать в данном случае.
30 июл 19, 14:13    [21937875]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
Пардон, второй запрос:
select t1.id, tt.sum_amount, iif(tt.sum_amount > 1000, 1, 0)
from table1 t1
left join (select t2.id_table1, sum(t2.amount) as sum_amount
from table2 t2
group by t2.id_table1) tt on tt.id_table1 = t1.id)
where t1.....
30 июл 19, 14:16    [21937881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Dimitry Sibiryakov
Member

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

SHS_SHS
когда появляется необходимость сделать проверку по sum_amount, нужно приписывать запрос так

Левый джоин тут не нужен. Order by в deived table - тоже. Наверняка точками прикрыто
что-то аналогично бессмысленное.

Posted via ActualForum NNTP Server 1.5

30 июл 19, 14:20    [21937885]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
С order by ошибся. left остался от оригинального запрос. В where кончено же ерунда написана, как без этого :). Но с просто join работает все равно дольше, чем с select в selectе.
select t1.id, tt.sum_amount, iif(tt.sum_amount > 1000, 1, 0)
from table1 t1
join (select t2.id_table1, sum(t2.amount) as sum_amount
      from table2 t2
      group by t2.id_table1) tt on tt.id_table1 = t1.id
where t1.....

Для данного запроса подсчет сумм идет для всей Table2, а уже потом объединение?
30 июл 19, 14:32    [21937903]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Старый плюшевый мишка
Member

Откуда:
Сообщений: 598
Dimitry Sibiryakov
SHS_SHS
когда появляется необходимость сделать проверку по sum_amount, нужно приписывать запрос так

Левый джоин тут не нужен. Order by в deived table - тоже. Наверняка точками прикрыто
что-то аналогично бессмысленное.


А ведь я лет 15 назад говорил господам фичереквестерам, что если что-то может быть использовано через анус, то только так оно и будет использоваться.
30 июл 19, 14:35    [21937910]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Симонов Денис
Member

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

в принципе при наличии индекса запрос с LEFT JOIN должен работать достаточно шустро. Приведи полный запрос 2 и его план
30 июл 19, 14:52    [21937935]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
select t1.id, (select sum(t2.amount) from table2 t2 where t2.id_table1 = t1.id) as sum_amount
from table1 t1
where t1.field1 = '1234'

-- 250ms
PLAN (T2 INDEX (TABLE2))
PLAN (T1 NATURAL)


select t1.id, tt.sum_amount, iif(tt.sum_amount > 1000, 1, 0)
from table1 t1
join (select t2.id_table1, sum(t2.amount) as sum_amount
      from table2 t2
      group by t2.id_table1) tt on tt.id_table1 = t1.id
where t1.field1 = '1234'

-- 2s 138ms
PLAN JOIN (TT T2 ORDER TABLE2, T1 INDEX (TABLE1))
30 июл 19, 15:18    [21937956]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
Тут проблема, что самописный парсер не понимает select в selecte, и плохо работает с group by. Есть ли какой-то вариант (как выше сказали "через одно место") сделать что-то быстро в запросе, вроде этого:
select t1.id, (select sum(t2.amount) from table2 t2 where t2.id_table1 = t1.id) as sum_amount, iif(sum_amount > 1000, 1, 0)
from table1 t1
where t1.field = '1234'

или смирится со скоростью выполнения второго запроса.... или дописывать парсер. Сейчас парсер понимает все поля (какими бы кривыми не были), все объединения таблиц (подзапросов) через joinы. С group by работает, но с group by можно динамически добавить в запрос поля, а хотелось бы и таблицы (этого нет).
30 июл 19, 15:34    [21937971]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Dimitry Sibiryakov
Member

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

SHS_SHS
-- 250ms
PLAN (T2 INDEX (TABLE2))
PLAN (T1 NATURAL)

Здесь ты явно забыл нажать "Fetch All".

Posted via ActualForum NNTP Server 1.5

30 июл 19, 15:37    [21937975]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

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

а вот так:
select t1.id, sum(t2.amount), iif(sum(t2.amount) > 1000, 1, 0)
from table1 t1
       inner join table2 t2 on t2.id_table1=t1.id
where t1.....
group by 1
30 июл 19, 15:40    [21937977]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Симонов Денис
Member

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

чёт планы у тебя какие-то кривоватые мягко говоря или ты индексы по уродливому назвал. Лучше уж explain план. Да ну ладно время не такое уж и большое на самом деле.
Сообщи архитектуру и размер страничного кэша.

И вообще твоём случае скорее всего вот такого запроса вполне хватит

select 
  t2.id_table1 as id, 
  sum(t2.amount) as sum_amount, 
  iif(sum(t2.amount)  > 1000, 1, 0)
from table1 t1
join table2 t2 on  t2.id_table1 = t1.id
where t1.field1 = '1234'
group by t2.id_table1
30 июл 19, 15:43    [21937979]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

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

в принципе при наличии индекса запрос с LEFT JOIN должен работать достаточно шустро. Приведи полный запрос 2 и его план


Кстати да, с LEFT JOIN в упрощенной форме запроса работает реально быстрее:
select t1.id, tt.sum_amount, iif(tt.sum_amount > 1000, 1, 0)
from table1 t1
left join (select t2.id_table1, sum(t2.amount) as sum_amount
           from table2 t2
           group by t2.id_table1) tt on tt.id_table1 = t1.id
where t1.field1 = '1234'


--250ms
PLAN JOIN (T1 NATURAL, TT T2 ORDER TABLE2)

В оригинале запроса для этой связки стоит тоже left join, поменял для другого объединения на left join (хотя там связь без null) и переставил объединение ниже этой - все летает. Похоже оптимизатор наконец-то понял мою ересь мой запрос.
30 июл 19, 15:59    [21938000]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
Планы криповаты, т.к. за столько лет без ошибок писать так и не научился.

-- 250ms
PLAN (T2 INDEX (FK_TABLE2_1))
PLAN (T1 NATURAL)

-- 2s 138ms
PLAN JOIN (TT T2 ORDER FK_TABLE2_1, T1 INDEX (PK_TABLE1_1))

-- 250ms
PLAN JOIN (T1 NATURAL, TT T2 ORDER FK_TABLE2_1)

FetchAll не нажимал, т.к. там результат в 15-20 строк. Но на всякий проверил, результаты почти такие же.
30 июл 19, 16:09    [21938013]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

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

ИМХО, левый джойн - костыль. Не пробовал мой или последний запрос Дениса? Вроде всё прозрачно.
30 июл 19, 16:15    [21938018]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
SHS_SHS
Member

Откуда:
Сообщений: 109
В этих запросах group by. Я знаю как написать запрос с ним. Если в двух словах описать почему нельзя, то: Есть "костяк" запроса, который выводит отчет. Но вдруг пользователю нужны еще поля, он в специальной форме выбирает их. Парсер проходит по запросу и подставляет в него поля, и если нужно подключает таблицы. А использование group by "ломает" это. Но все равно спасибо.
30 июл 19, 16:25    [21938026]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Симонов Денис
Member

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

я бы не сказал что костыль, но у автора явно не тот случай когда именно LEFT JOIN нужен к производной таблице или CTE
30 июл 19, 16:27    [21938030]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
KreatorXXI
Member

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

понял. У нас тоже есть что-то подобное. Но и есть возможность сделать настраиваемый group by. Конструкция вложенных селектов (не "select from select", а именно вложенных) легче для конструктора запросов. Но, сами видите, провал по производительности очень часто обеспечен.
30 июл 19, 16:52    [21938068]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить