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

Откуда:
Сообщений: 70
Приветствую

Firebird 3.0

Пытаюсь разобраться, как получить срез последних по времени данных
есть таблица:
CREATE TABLE MESSAGES (
    UUID            SYS$UUID NOT NULL /* SYS$UUID = CHAR(16) */,
    ON_UUID         SYS$UUID /* SYS$UUID = CHAR(16) */,
    TICK            SYS$TIMETICK NOT NULL /* SYS$TIMETICK = BIGINT */,
    MSG_TIME        SYS$TIMESTAMP /* SYS$TIMESTAMP = TIMESTAMP */,
    MSG_LOCAL_TIME  COMPUTED BY (dateadd(hour, RDB$GET_CONTEXT('USER_SESSION', 'TIMEZONE'), MSG_TIME)),
    SITE            SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    DEVICE          SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    MSG_TYPE        SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    MSG_KIND        SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    JSON            SYS$JSON /* SYS$JSON = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
);

ALTER TABLE MESSAGES ADD CONSTRAINT MESSAGES PRIMARY KEY (UUID);
CREATE INDEX MESSAGES_TICK ON MESSAGES (TICK, DEVICE, MSG_TYPE, MSG_KIND);
CREATE INDEX MESSAGES_TIME ON MESSAGES (MSG_TIME, DEVICE, MSG_TYPE, MSG_KIND);
CREATE DESCENDING INDEX MESSAGES_TIME_DESC ON MESSAGES (MSG_TIME, DEVICE, MSG_TYPE, MSG_KIND);

набросал запрос который, вроде работает
with MDATA as (
  select UUID, DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND, first_value(UUID) over (partition by DEVICE order by MSG_TIME desc) as OVER_UUID
  from MESSAGES
  where MSG_TIME between current_timestamp - 1 and current_timestamp
  order by DEVICE
)
select DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND
from MDATA
where UUID = OVER_UUID

План: PLAN SORT (SORT (MD M INDEX (MESSAGES_TIME)))

Можно ли обойтись без with, и какие нужны индексы чтобы не вычитывать все записи за сутки?
11 апр 19, 18:39    [21859839]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
vvvait
Member

Откуда:
Сообщений: 70
Корректный план
PLAN SORT (SORT (MDATA MESSAGES INDEX (MESSAGES_TIME)))
11 апр 19, 18:42    [21859841]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9393
vvvait
Можно ли обойтись без with


разве что заменить на derived table

vvvait
какие нужны индексы чтобы не вычитывать все записи за сутки


никакие. Оконные функции никаких индексов применять не умеют, кроме тех по условию во внутреннем WHERE.

Посмотри лучше explain план, тогда поймёшь как оконные функции выполняются.
11 апр 19, 19:46    [21859883]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
vvvait
Member

Откуда:
Сообщений: 70
похоже оконные функции не очень подходят для этой задачи
если сделать временную таблицу:
CREATE GLOBAL TEMPORARY TABLE LAST_DEV_MESSAGE (
    DEVICE    SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    UUID      SYS$UUID /* SYS$UUID = CHAR(16) */,
    ON_UUID   SYS$UUID /* SYS$UUID = CHAR(16) */,
    TICK      SYS$TIMETICK /* SYS$TIMETICK = BIGINT */,
    MSG_TIME  SYS$TIMESTAMP /* SYS$TIMESTAMP = TIMESTAMP */,
    SITE      SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    MSG_TYPE  SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    MSG_KIND  SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    JSON      SYS$JSON /* SYS$JSON = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
) ON COMMIT PRESERVE ROWS;
ALTER TABLE LAST_DEV_MESSAGE ADD PRIMARY KEY (DEVICE);

и выполнить такой блок:
execute block
returns (
    DEVICE type of column LAST_DEV_MESSAGE.DEVICE,
    UUID type of column LAST_DEV_MESSAGE.UUID,
    ON_UUID type of column LAST_DEV_MESSAGE.ON_UUID,
    TICK type of column LAST_DEV_MESSAGE.TICK,
    MSG_TIME type of column LAST_DEV_MESSAGE.MSG_TIME,
    SITE type of column LAST_DEV_MESSAGE.SITE,
    MSG_TYPE type of column LAST_DEV_MESSAGE.MSG_TYPE,
    MSG_KIND type of column LAST_DEV_MESSAGE.MSG_KIND,
    JSON type of column LAST_DEV_MESSAGE.JSON
)
as
begin
  delete from LAST_DEV_MESSAGE;
  for select m.DEVICE, m.UUID, m.ON_UUID, m.TICK, m.MSG_TIME, m.SITE, m.MSG_TYPE, m.MSG_KIND, m.JSON
      from MESSAGES m
      where m.MSG_TIME between current_timestamp - 1 and current_timestamp
      order by m.MSG_TIME desc
      into :DEVICE, :UUID, :ON_UUID, :TICK, :MSG_TIME, :SITE, :MSG_TYPE, :MSG_KIND, :JSON
   do begin
      if (exists (select * from LAST_DEV_MESSAGE where DEVICE = :DEVICE))
         then continue;
      insert into LAST_DEV_MESSAGE (DEVICE, UUID, ON_UUID, TICK, MSG_TIME, SITE, MSG_TYPE, MSG_KIND, JSON)
      values (:DEVICE, :UUID, :ON_UUID, :TICK, :MSG_TIME, :SITE, :MSG_TYPE, :MSG_KIND, :JSON);
      suspend;
      end
end

то получится тоже самое, только в 2,5 раза быстрее чем у оконной функции:
------ Информация о производительности ------
Время подготовки запроса = 32ms
Время выполнения запроса = 2s 515ms
Среднее время на получение одной записи = 104,79 ms
Current memory = 42 256 536
Max memory = 113 440 272
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Чтений из кэша = 91 461


------ Информация о производительности ------
Время подготовки запроса = 15ms
Время выполнения запроса = 6s 406ms
Среднее время на получение одной записи = 206,65 ms
Current memory = 40 992 888
Max memory = 113 440 272
Memory buffers = 2 048
Reads from disk to cache = 1
Writes from cache to disk = 8
Чтений из кэша = 26 189
12 апр 19, 00:10    [21860027]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
Симонов Денис
Member

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

а чего такой кеш мизерный?

Ну и во вторых, конечную сортировку лучше ставить после всех фильтраций, т.е. вот так

with MDATA as (
  select UUID, DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND, 
         first_value(UUID) over (partition by DEVICE order by MSG_TIME desc) as OVER_UUID
  from MESSAGES
  where MSG_TIME between current_timestamp - 1 and current_timestamp
)
select DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND
from MDATA
where UUID = OVER_UUID
order by DEVICE
12 апр 19, 08:47    [21860122]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
Симонов Денис
Member

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

и ещё зачем столько составных индексов? У вас есть запросы которые по всем полям одновременно фильтрацию делают? Достаточно одного индекса

CREATE INDEX MESSAGES_TIME ON MESSAGES (MSG_TIME);
12 апр 19, 08:50    [21860123]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
vvvait
Member

Откуда:
Сообщений: 70
Индексы может и не все нужны, но это замедляет только вставку.

Без сортировки, запрос идет 5 секунд.

Кэш маленький, т.к. firebird крутится на роутере с 256 Мб памяти.
12 апр 19, 10:52    [21860254]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
Симонов Денис
Member

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

Одиночный индекс в данном случае лучше

Потому что Range Scan (full match) лучше чем Range Scan (partial match: 1/4)
12 апр 19, 11:10    [21860273]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
vvvait
Member

Откуда:
Сообщений: 70
перенес базу на core i5 8400, 16gb, ssd m.2
срез за 30 дней

с окнами:
------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 8s 234ms
Среднее время на получение одной записи = 111,27 ms
Current memory = 36 679 880
Max memory = 111 302 032
Memory buffers = 2 048
Reads from disk to cache = 7 498
Writes from cache to disk = 4
Чтений из кэша = 437 180

без окон:
------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 1s 297ms
Среднее время на получение одной записи = 17,53 ms
Current memory = 36 768 864
Max memory = 111 302 032
Memory buffers = 2 048
Reads from disk to cache = 6 266
Writes from cache to disk = 6
Чтений из кэша = 2 156 679
12 апр 19, 11:13    [21860275]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
Симонов Денис
Member

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

а ещё GUID в качестве ключа здесь играет злую шутку. Был бы обычный авто инкремент можно было бы сделать так

WITH T AS (
  SELECT MAX(ID) as ID
  FROM MESSAGES
  WHERE MSG_TIME between current_timestamp - 1 and current_timestamp
  GROUP BY DEVICE)
SELECT
  MESSAGES.DEVICE, MESSAGES.MSG_TIME, MESSAGES.MSG_TYPE, MESSAGES.MSG_KIND
FROM T
JOIN MESSAGES ON T.ID = MESSAGES.ID
ORDER BY MESSAGES.DEVICE


потому что чем позже пришло сообщение тем больше ID

В вашем случае можно попробовать

WITH T AS (
  SELECT MAX(MSG_TIME) as MSG_TIME, DEVICE 
  FROM MESSAGES
  WHERE MSG_TIME between current_timestamp - 1 and current_timestamp
  GROUP BY DEVICE)
SELECT
  MESSAGES.DEVICE, MESSAGES.MSG_TIME, MESSAGES.MSG_TYPE, MESSAGES.MSG_KIND
FROM T
JOIN MESSAGES ON T.DEVICE= MESSAGES.DEVICE AND T.MSG_TIME= MESSAGES.MSG_TIME
ORDER BY MESSAGES.DEVICE
12 апр 19, 11:31    [21860302]     Ответить | Цитировать Сообщить модератору
 Re: Оконные функции для получения среза последних данных  [new]
vvvait
Member

Откуда:
Сообщений: 70
там есть такое поле, но оно не уникально, и есть очень маленькая вероятность что будет две записи с одинаковым тиком
WITH T AS (
  SELECT MAX(m.TICK) as TICK
  FROM MESSAGES m
  WHERE MSG_TIME between current_timestamp - 30 and current_timestamp
  GROUP BY DEVICE)
SELECT
  MESSAGES.DEVICE, MESSAGES.MSG_TIME, MESSAGES.MSG_TYPE, MESSAGES.MSG_KIND
FROM T
JOIN MESSAGES ON T.TICK = MESSAGES.TICK
ORDER BY MESSAGES.DEVICE

это запрос быстрее всех, на 30 дней:

------ Информация о производительности ------
Время подготовки запроса = 16ms
Время выполнения запроса = 703ms
Среднее время на получение одной записи = 9,50 ms
Current memory = 36 809 368
Max memory = 111 302 032
Memory buffers = 2 048
Reads from disk to cache = 7 523
Writes from cache to disk = 4
Чтений из кэша = 437 233

второй запрос, с макс по времени, сработал за такое же время
12 апр 19, 12:00    [21860348]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить