Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

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

Это забавно.
+ Структура БД
CREATE TABLE A (
    ID INTEGER NOT NULL,
    VAL VARCHAR(32) CHARACTER SET UTF8);


CREATE TABLE B1 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE B2 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE C (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    FLAG SMALLINT);

ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID);
ALTER TABLE B1 ADD CONSTRAINT PK_B1 PRIMARY KEY (ID);
ALTER TABLE B2 ADD CONSTRAINT PK_B2 PRIMARY KEY (ID);
ALTER TABLE C ADD CONSTRAINT PK_C PRIMARY KEY (ID);

ALTER TABLE B1 ADD CONSTRAINT FK_B1_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE B2 ADD CONSTRAINT FK_B2_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE C ADD CONSTRAINT FK_C_A FOREIGN KEY (ID_A) REFERENCES A (ID);

CREATE INDEX IDX_B1_FIX ON B1 (FIX);
CREATE INDEX IDX_B2_FIX ON B2 (FIX);
CREATE INDEX IDX_C_FLAG ON C (FLAG);


Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

Как блин?.. И можно ли это сделать ещё быстрее? :)
30 май 20, 22:17    [22142887]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
В общем на данный момент самым оптимальным запросом под мои задачи, из тех что я смог придумать и протестировать, оказался такой:
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID

План:
PLAN (B2 INDEX (FK_B2_A, IDX_B2_FIX))
PLAN (B1 INDEX (FK_B1_A, IDX_B1_FIX))
PLAN JOIN (F C ORDER FK_C_A, A INDEX (PK_A))

Вопрос: добавил к B2 ещё один внешний ключ к B1 (т.к. они всё-таки взаимосвязаны). Запрос стал выполняться на 60% дольше (было 3.5, стало 5.2 секунды)... хотя этот ключ непосредственно в запросе не используется. Это можно как-то нивелировать?

Также приветствуются любые советы по оптимизации.

Если вдруг кому интересно, то бэкап тестовой базы с тестовыми данными тут (~10 Мб, сама база ~200 Мб).

Сообщение было отредактировано: 31 май 20, 01:55
31 май 20, 01:53    [22142932]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov
alekcvp,
А связь A к B и A к C один к одному?
Если нет, то этот способ еще хуже.

Нет, один ко многим.
А почему хуже? По триггеру пересчитать одно поле одной записи вроде не должно занимать много времени?

Я имел ввиду, что хуже потому, что усложнится логика.
20578934 )))
Например: добавлена запись в A. В таблице С соответствующих записей нет.
Что произойдет в триггере, если в таблицу С будет вставлена запись?
Что-то типа
update А set MIN_FLAG = ( select min( FLAG ) from C where ID_A = new.ID_A );
И аналогичные действия при апдейте и удалении записи из таблицы C?

Хотя, согласен - в принципе, схема работоспособная.
Ну, и не допускать ошибочной деактивации триггера )
31 май 20, 13:08    [22143033]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov,

Это забавно.
+ Структура БД
CREATE TABLE A (
    ID INTEGER NOT NULL,
    VAL VARCHAR(32) CHARACTER SET UTF8);


CREATE TABLE B1 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE B2 (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    NUM INTEGER,
    FIX DATE);


CREATE TABLE C (
    ID INTEGER NOT NULL,
    ID_A INTEGER NOT NULL,
    FLAG SMALLINT);

ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID);
ALTER TABLE B1 ADD CONSTRAINT PK_B1 PRIMARY KEY (ID);
ALTER TABLE B2 ADD CONSTRAINT PK_B2 PRIMARY KEY (ID);
ALTER TABLE C ADD CONSTRAINT PK_C PRIMARY KEY (ID);

ALTER TABLE B1 ADD CONSTRAINT FK_B1_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE B2 ADD CONSTRAINT FK_B2_A FOREIGN KEY (ID_A) REFERENCES A (ID);
ALTER TABLE C ADD CONSTRAINT FK_C_A FOREIGN KEY (ID_A) REFERENCES A (ID);

CREATE INDEX IDX_B1_FIX ON B1 (FIX);
CREATE INDEX IDX_B2_FIX ON B2 (FIX);
CREATE INDEX IDX_C_FLAG ON C (FLAG);


Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

Как блин?.. И можно ли это сделать ещё быстрее? :)

Я уже предлагал способ - обеспечить в таблице A наличие фиктивных записей, подзпаросы оформить в виде CTE и использовать inner join.
31 май 20, 13:12    [22143038]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
Polesov,
> обеспечить в таблице A наличие фиктивных записей
обеспечить для таблицы A наличие фиктивных записей
31 май 20, 13:15    [22143042]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Polesov,
Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

А как измерял?
Я окончания полного фетча для этого запроса ждал 2 минуты - не дождался.
31 май 20, 14:16    [22143083]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

Откуда:
Сообщений: 10955
alekcvp
В общем на данный момент самым оптимальным запросом под мои задачи, из тех что я смог придумать и протестировать, оказался такой:
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID
1. Индекс по FIX в данном случае только мешает, его нужно либо отключить, либо добавить в него ID_A
WITH
  FLAGS AS (SELECT ID_A, MIN(FLAG) AS FLAG FROM C GROUP BY ID_A)
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM2
FROM A INNER JOIN FLAGS F ON F.ID_A = A.ID


2. Т.к. полей из С в результате нет, то можно избавиться от агрегата с ней
SELECT
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A = A.ID AND FIX+0 BETWEEN '10.05.2020' AND '20.05.2020') SUM2
  FROM A
 WHERE EXISTS (SELECT * FROM C WHERE C.ID_A = A.ID)
31 май 20, 15:14    [22143104]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

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

2. Т.к. полей из С в результате нет

Думаю, ТС, приводя запрос, просто ошибся.
В исходном посте сказано, что из таблицы C надо выводить min( FLAG ).

Кстати, есть ли существенные минусы в добавлении фиктивных записей в дочерние таблицы для обеспечения использования inner join?
31 май 20, 16:19    [22143152]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
FROM
(
SELECT
    ID_A,
    SUM(NUM) SUM1,
    NULL SUM2,
    NULL FLAG
FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    SUM(NUM),
    NULL
FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    NULL,
    MIN(C.FLAG)
FROM C
GROUP BY ID_A
) B1B2C
LEFT JOIN A ON A.ID = B1B2C.ID_A
GROUP BY ID_A
31 май 20, 16:40    [22143162]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Polesov
alekcvp
Polesov,
Запрос
SELECT 
   A.ID,
   (SELECT SUM(B1.NUM) FROM B1 WHERE B1.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM1,
   (SELECT SUM(B2.NUM) FROM B2 WHERE B2.ID_A=A.ID AND FIX BETWEEN '10.05.2020' AND '20.05.2020') SUM2,
   (SELECT MIN(C.FLAG) FROM C WHERE C.ID_A=A.ID) FLAG
FROM A
отрабатывает за 3.5 секунды, при Count(*) A = 100К, B1 = 400K, B2 = 700K, C = 150K...

А как измерял?
Я окончания полного фетча для этого запроса ждал 2 минуты - не дождался.

А запрос во время выполнения разве не все данные считает, а тоже порциями? Я имел в виду время выполнения самого запроса.
Фетч-то на 100к записей может и сам по себе долго выполняться, нет?
31 май 20, 16:53    [22143172]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

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

2. Т.к. полей из С в результате нет, то можно избавиться от агрегата с ней
Пропустил F.FLAG в запросе :(
31 май 20, 16:56    [22143176]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

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

Охренеть. Спасибо!
Я так понимаю этот запрос разворачивает все три таблицы в одну, а потом тупо строит по ней аггрегатный запрос?

Сообщение было отредактировано: 31 май 20, 17:00
31 май 20, 17:01    [22143180]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

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

нужно ещё HAVING MAX(FLAG) IS NOT NULL добавить, иначе запрос не эквивалентен.

А вот так будет ещё немножко лучше

SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
  FROM
  (
    SELECT ID_A, SUM(NUM) SUM1, NULL SUM2, NULL FLAG
      FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
    GROUP BY ID_A
    
    UNION ALL
    
    SELECT ID_A, NULL, SUM(NUM), NULL
      FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
    GROUP BY ID_A
    
    UNION ALL
    
    SELECT ID_A, NULL, NULL, MIN(C.FLAG)
      FROM C
    GROUP BY ID_A
  ) B1B2C JOIN A ON A.ID +0= B1B2C.ID_A   -- заставим делать HASH JOIN
GROUP BY ID_A
HAVING MAX(FLAG) IS NOT NULL
31 май 20, 17:26    [22143195]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
alekcvp, таблицы агрегируются независимо друг от друга, чтобы избежать коррелированных подзапросов.
Потом результаты склеивается одну строку для каждого ID_A и берётся полный их список из A.

hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
HASH JOIN
31 май 20, 17:49    [22143210]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
А я эпично обломался. Решил начать от простого к сложному, хаха :(

Внезапно, даты в BETWEEN у меня не задаются в параметрах запроса, а вычисляются из поля таблицы A, причём сложно.
Т.е. там есть поле "начало учётного периода", которое содержит в себе число. И интервал Between - это либо прошлый, либо текущий месяц. Т.е. если там "20", а сегодня 10е мая, то надо просуммировать либо с 20 марта по 19 апреля (SUM2), либо с 20 апреля по сегодняшний день, включительно (SUM1). Блин.

Что-то я всё больше склоняюсь к кэшированию в A всех сумм и обновлению их по триггерам...

Сообщение было отредактировано: 31 май 20, 17:50
31 май 20, 17:51    [22143212]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
Flashpoke
SELECT ID_A, MAX(SUM1), MAX(SUM2), MAX(FLAG)
FROM
(
SELECT
    ID_A,
    SUM(NUM) SUM1,
    NULL SUM2,
    NULL FLAG
FROM B1 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    SUM(NUM),
    NULL
FROM B2 WHERE FIX BETWEEN '10.05.2020' AND '20.05.2020'
GROUP BY ID_A
UNION ALL
SELECT
    ID_A,
    NULL,
    NULL,
    MIN(C.FLAG)
FROM C
GROUP BY ID_A
) B1B2C
LEFT JOIN A ON A.ID = B1B2C.ID_A
GROUP BY ID_A

Исходное условие - должны быть все записи из A.
В результат данного запроса не попадут записи из A, которых нет ни в B1, ни B2, ни в C.
Тогда уж
RIGHT JOIN A ON A.ID = B1B2C.ID_A
31 май 20, 18:15    [22143228]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

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

Исходное условие - должны быть все записи из A.
В результат данного запроса не попадут записи из A, которых нет ни в B1, ни B2, ни в C.

Ну by design записи в C создаются одновременно с A, если запись есть в A, но нет ни одной записи в C - это нештатная ситуация, так что если такие записи не будет видно - не страшно. Вот отстутствие записей в B1, B2 - это нормально.

Сообщение было отредактировано: 31 май 20, 18:15
31 май 20, 18:17    [22143229]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
alekcvp
Ну by design записи в C создаются одновременно с A

Аднака, паходу выявилась масса нюансоффф )))
31 май 20, 18:35    [22143245]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
hvlad
Member

Откуда:
Сообщений: 10955
Flashpoke
hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
Вот здесь 22142932 - не все, только те, для которых есть запись в C.
Всю ветку не читал, скучно :)
31 май 20, 19:18    [22143272]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Polesov
Member

Откуда:
Сообщений: 746
hvlad
Flashpoke
hvlad, вроде же в исходном запросе все записи из таблицы А достаются и показываются?
Вот здесь 22142932 - не все, только те, для которых есть запись в C.

22143229
Выяснилось, что в C есть все, что есть в А.
31 май 20, 22:35    [22143345]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
А вот этого монстра можно как-нибудь оптимизировать?..
-- RD1, RD2 - день месяца (1..31)
SELECT ID_A, RD1, RD2, SUM(SNUM1), SUM(SNUM2), MIN(FLAG) AS FLAG
  FROM
  (
    SELECT ID_A, FIX FIX1, NULL FIX2, SUM(NUM) SNUM1, NULL SNUM2, NULL FLAG FROM B1
    GROUP BY ID_A, FIX
    
    UNION ALL
    
    SELECT ID_A, NULL, FIX, NULL, SUM(NUM), NULL FROM B2
    GROUP BY ID_A, FIX
    
    UNION ALL
    
    SELECT ID_A, NULL, NULL, NULL, NULL, MIN(C.FLAG) FROM C
    GROUP BY ID_A
  ) B1B2C JOIN A ON A.ID = B1B2C.ID_A
WHERE
  (FIX1 BETWEEN DFST(A.RD1) AND DLST(A.RD1) OR FIX1 IS NULL) AND
  (FIX2 BETWEEN DFST(A.RD2) AND DLST(A.RD2) OR FIX2 IS NULL)
GROUP BY ID_A, RD1, RD2
-- HAVING MIN(FLAG) IS NOT NULL -- наличие этого условия не обязательно

+ DFST/DLST
SET TERM ^ ;

create or alter function DFST (
    RDAY smallint not null)
returns date not null
as
declare variable RDIFF smallint;
begin
  RDIFF = RDAY - EXTRACT(DAY FROM CURRENT_DATE);
  return IIF(RDIFF < 0,  DATEADD(DAY, RDIFF, CURRENT_DATE), DATEADD(MONTH, -1, DATEADD(DAY, RDIFF, CURRENT_DATE)));
end
^

create or alter function DLST (
    RDAY smallint not null)
returns date not null
as
declare variable RDIFF smallint;
begin
  RDIFF = RDAY - EXTRACT(DAY FROM CURRENT_DATE);
  return IIF(RDIFF < 0,  DATEADD(MONTH, 1, DATEADD(DAY, RDIFF, CURRENT_DATE)), DATEADD(DAY, RDIFF, CURRENT_DATE)) - 1;
end
^

SET TERM ; ^

Polesov
Выяснилось, что в C есть все, что есть в А.
Предполагается что есть, судьба записей для которых нет - не важна (показывать или нет без разницы).

Сообщение было отредактировано: 31 май 20, 22:47
31 май 20, 22:48    [22143357]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
Flashpoke
Member

Откуда:
Сообщений: 14
alekcvp
А вот этого монстра можно как-нибудь оптимизировать?..
За 8 секунд отрабатывает, вроде нормально.
1 июн 20, 02:11    [22143411]     Ответить | Цитировать Сообщить модератору
 Re: Вложенные запросы или как правильно это сделать?  [new]
alekcvp
Member

Откуда:
Сообщений: 2177
Flashpoke
alekcvp
А вот этого монстра можно как-нибудь оптимизировать?..
За 8 секунд отрабатывает, вроде нормально.

Ну исходный-то за секунду срабатывал, пока я в него кривыми ручками не залез :)
1 июн 20, 02:13    [22143414]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Firebird, InterBase Ответить