Оптимизация запроса

← →
kadr (2008-05-12 10:30) [0]

Здравствуйте.
Имеем таблицу
CREATE TABLE DATA (
ID DOM_ID NOT NULL /* DOM_ID = INTEGER NOT NULL */,
DT_NAME DOM_STR20 /* DOM_STR20 = VARCHAR(20) */,
DT_VALUE DOM_BIGINT /* DOM_BIGINT = BIGINT */,
DATE_ DOM_DATE_ /* DOM_DATE_ = TIMESTAMP DEFAULT current_timestamp */
);
CREATE INDEX IDX_DATE_ ON DATA (DATE_);
CREATE INDEX IDX_NAME ON DATA (DT_NAME);

Необходимо выбрать значения dt_value на последнюю дату и dt_name

Использую запрос:
select d.dt_name, s.dt_value
from (select dt_name, max(date_) as date_ from data group by 1) d
inner join (select dt_value, dt_name, date_ from data) s
on s.dt_name = d.dt_name and s.date_ = d.date_

или хранимку
CREATE PROCEDURE LAST_DATA
returns (
dt_value integer,
dt_name varchar(20))
as
declare variable max_date timestamp;
declare c cursor for (select distinct(dt_name) from data
);
begin

open C;
while (1 = 1) do
begin
fetch C into :dt_name;
if (row_count = 0) then
leave;

select first 1 dt_name, dt_value
from data
where dt_name = :dt_name
order by date_ DESC
into
:dt_name, dt_value;

suspend;
end
close C;

end^

вроде все ничего, но приблизительный объем записей таблицы 3 и более миллионов. На таких объемах что запрос, что хранимка отрабатывает около 30 сек.
Есть ли мысли по поводу оптимизации?

← →
ANB (2008-05-12 10:35) [1]

1. Сделать составной индекс Имя, дата

← →
Sergey13 © (2008-05-12 10:40) [2]

> [0] kadr (12.05.08 10:30)

> inner join (select dt_value, dt_name, date_ from data) s

А стОит ли джойнить с селектом? Почему не просто с таблицей? Ну и индекс составной, как [1] ANB (12.05.08 10:35) предложил.

← →
ЮЮ © (2008-05-12 10:42) [3]

Нормализовать таблицу, ибо, исходя из желаеемого,
ID DOM_ID NOT NULL /* DOM_ID = INTEGER NOT NULL */,
DT_NAME DOM_STR20 /* DOM_STR20 = VARCHAR(20) */,
 это одна сущность, связанная связью один ко многим с другой.

Связь по s.dt_name = d.dt_name всяко тяжелее, ИМХО, чем по целым полям.
да и 3 и более миллионов разобъется на сомножители.

← →
ЮЮ © (2008-05-12 10:47) [4]

А сколько из этих 30 секунд выполняется подзапрос
select dt_name, max(date_) as date_ from data group by 1
?

← →
ANB (2008-05-12 12:11) [5]

> ЮЮ © (12.05.08 10:47) [4]
> А сколько из этих 30 секунд выполняется подзапрос
> select dt_name, max(date_) as date_ from data group by
> 1
> ?

Если количество вариантов по dt_name невелико (в пределах 1000), то должен при наличии составного индекса шустро работать.
А если велико (500 тысяч, например), то никакая оптимизация не поможет. 30 сек - это уже ОЧЕНЬ быстро.

← →
kadr (2008-05-12 13:56) [6]

количество dt - до 2500.
Сейчас провоже тесты - результаты через полчаса-час будут в ветке

← →
kadr (2008-05-12 14:53) [7]

Результаты испытаний на 1 млн записей:

/////////////////////////////////////////////////////////////////////////
ИСПОЛЬЗОВАНИЕ ИНДЕКСОВ IDX1 dt_name, IDX2 date_
/////////////////////////////////////////////////////////////////////////

select dt_name, max(date_) as date_ from data group by 1

>План
>PLAN (DATA ORDER IDX_NAME)
------ Performance info ------
Prepare time = 0ms
Execute time = 57s 562ms

//*********************************************
select d.dt_name, s.dt_value
from (select dt_name, max(date_) as date_ from data group by 1) d
inner join (select dt_value, dt_name, date_ from data) s
on s.dt_name = d.dt_name and s.date_ = d.date_

>План
>PLAN JOIN (D DATA ORDER IDX_NAME, S DATA INDEX (IDX_DATE_))
------ Performance info ------
Prepare time = 15ms
Execute time = 57s 282ms

//*********************************************
select d.dt_name, s.dt_value
from (select dt_name, max(date_) as date_ from data group by 1) d
inner join data s
on s.dt_name = d.dt_name and s.date_ = d.date_

>План
>PLAN JOIN (D DATA ORDER IDX_NAME, S INDEX (IDX_DATE_))
------ Performance info ------
Prepare time = 0ms
Execute time = 57s 672ms

/////////////////////////////////////////////////////////////////////////
ИСПОЛЬЗОВАНИЕ СОСТАВНОГО ИНДЕКСА (DT_NAME, DATE_)
/////////////////////////////////////////////////////////////////////////

select dt_name, max(date_) as date_ from data group by 1

>План
>PLAN (DATA ORDER IDX_NAME)
------ Performance info ------
Prepare time = 0ms
Execute time = 20s 140ms

//*********************************************
select d.dt_name, s.dt_value
from (select dt_name, max(date_) as date_ from data group by 1) d
inner join (select dt_value, dt_name, date_ from data) s
on s.dt_name = d.dt_name and s.date_ = d.date_

>План
>PLAN JOIN (D DATA ORDER IDX_NAME, S DATA INDEX (IDX_NAME))
------ Performance info ------
Prepare time = 0ms
Execute time = 20s 109ms

//*********************************************
select d.dt_name, s.dt_value
from (select dt_name, max(date_) as date_ from data group by 1) d
inner join data s
on s.dt_name = d.dt_name and s.date_ = d.date_

>План
>PLAN JOIN (D DATA ORDER IDX_NAME, S INDEX (IDX_NAME))
------ Performance info ------
Prepare time = 0ms
Execute time = 20s 203ms

← →
kadr (2008-05-12 14:56) [8]

>> 3 Попытался немного нормализовать...
Создал таблицу

CREATE TABLE DATA_VALUE ( ID DOM_ID NOT NULL /* DOM_ID = INTEGER NOT NULL */, ID_DT DOM_INT /* DOM_INT = INTEGER */, DT_VALUE DOM_INT /* DOM_INT = INTEGER */, DATE_ DOM_DATE_ /* DOM_DATE_ = TIMESTAMP DEFAULT current_timestamp */ ); ALTER TABLE DATA_VALUE ADD CONSTRAINT FK_DATA_VALUE_1 FOREIGN KEY (ID_DT) REFERENCES DATA (ID);

Теперь не догоню, как выбрать все нужное запросом...
На ум приходит только такое:

CREATE PROCEDURE LAST_DATA1 returns ( dt_name varchar(20), dt_value integer) as declare variable dt_date_ timestamp; declare variable dt_id integer; declare c cursor for (select id_dt, max(date_) from data_value group by 1); begin open C; while (1 = 1) do begin fetch C into :dt_id, :dt_date_; if (row_count = 0) then leave; select first 1 dn.dt_name, dv.dt_value from data dn inner join data_value dv on (dv.id_dt = dn.id) where dv.id_dt = :dt_id and dv.date_ = :dt_date_ into :dt_name, dt_value; suspend; end close C; end

← →
Sergey13 © (2008-05-12 15:41) [9]

> [8] kadr (12.05.08 14:56)
> Попытался немного нормализовать...

А я бы попробовал немного ДЕнормализовать. Добавить признак актуальности в TABLE DATA из [0] и при вводе новой записи в нее ставить (в тригере) актуальность для нее в истину, при этом бывшей актуальной соответствующей записи менять на ложь. Этим самым избавляешься от поиска максимальной даты и значения для него.

← →
kadr (2008-05-12 16:13) [10]

>> 9 Уже думал об этом. Это будет САМЫЙ быстрый способ выборки. Хотелось обойтись тем, что есть, да видно не получиться :)

← →
ANB (2008-05-12 16:50) [11]

> kadr (12.05.08 14:53) [7]

Судя по индексам у тебя к планам новый индекс не прикрутился.
Грохни одиночные индексы для чистоты эксперимента. Или прохинтуй запрос.
(я не умею хинтовать ФБ).

Некоторое ускорение - только кажущееся. Эт просто сервер таблицу закэшировал.

← →
ANB (2008-05-12 16:51) [12]

> Sergey13 © (12.05.08 15:41) [9]
> > [8] kadr (12.05.08 14:56)
> > Попытался немного нормализовать...
>
> А я бы попробовал немного ДЕнормализовать. Добавить признак
> актуальности в TABLE DATA из [0] и при вводе новой записи
> в нее ставить (в тригере) актуальность для нее в истину,
> при этом бывшей актуальной соответствующей записи менять
> на ложь. Этим самым избавляешься от поиска максимальной
> даты и значения для него.

Если отчет нужно гонять не чаще раза в день - то излишне. Взамен ускорения отчета получим тормоза при вставке записей.

← →
kadr (2008-05-12 17:34) [13]

Это не отчет.
Есть датчики и их значения должны писаться в БД с интервалом 10 сек. Датчиков максимум 400 (пока). Ну и затем ессесно отчеты по периодам

> (я не умею хинтовать ФБ).

Интересно, а кто-нибудь умеет ?

> Интересно, а кто-нибудь умеет ?

Дима Еманов, Влад Хорсун, Ded - точно умеют :)
----------------------------------------------
Топик-стартеру:
1) А что, классика не канает ?
......................... FOR SELECT D.DT_NAME, MAX(D.DATE_) AS MAX_DATE FROM DATA D GROUP BY D.DT_NAME INTO :DT_NAME, :MAX_DATE DO BEGIN SELECT FIRST 1 D1.DT_VALUE FROM DATA D1 WHERE (D1.DATE_ = :MAX_DATE) AND (D1.DT_NAME = :DT_NAME) INTO :DT_VALUE; SUSPEND; END ..................
Интересен план такого запроса.

2)Если БД реалтайм и по этим данным буду строится отчеты, то имеет смысл ее денормализовать. Создаешь еще одну таблицу с полями DT_NAME, DT_VALUE, MAX_DATE и обновляешь ее записи на триггерах вставки+изменения таблицы DATA. Тогда вообще все будет шустро

← →
Виталий Панасенко(дом) (2008-05-13 00:44) [16]

а создать использовать индекс по убыванию не пробовал?

← →
kadr (2008-05-13 13:36) [17]

>> 15 А процедура в посте 8 не то, что подсказываете Вы? :)

>> 16
А вот слона то мы и не заметили :))
Действительно создал индекс по убыванию и получил:

CREATE PROCEDURE LAST_DATA_COPY returns ( dt_value integer, dt_name varchar(20)) as declare variable max_date timestamp; declare c cursor for (select distinct(dt_name) from data ); begin open C; while (1 = 1) do begin fetch C into :dt_name; if (row_count = 0) then leave; select first 1 dt_name, dt_value from data where dt_name = :dt_name order by date_ DESC into :dt_name, dt_value; suspend; end close C; end

Plan:
(DATA ORDER DATA_IDX1)
------ Performance info ------
Prepare time = 0ms
Execute time = 8s 781ms

При использовании рецепта [9] - [10] следующие результаты:
CREATE PROCEDURE LAST_DATA returns ( dt_value integer, dt_name varchar(20)) as declare variable max_date timestamp; declare c cursor for (select distinct(dt_name) from data ); begin /*$$IBEC$$ open C; while (1 = 1) do begin fetch C into :dt_name; if (row_count = 0) then leave; select first 1 dt_name, dt_value from data where dt_name = :dt_name order by date_ DESC into :dt_name, dt_value; suspend; end close C; $$IBEC$$*/ for select dt_name, dt_value from data where is_new = 1 into :dt_name, :dt_value do suspend; end

Plan:
(DATA NATURAL)
------ Performance info ------
Prepare time = 0ms
Execute time = 1s 469ms

Но возникает вопрос, что будет более нагружать сервер при записи в БД отработка триггера при вставке в DATA (использование рецепта [9])
CREATE trigger data_bi1 for data active before insert position 1 AS begin if (new.dt_value > 0) then begin update data set is_new = 0 where dt_name = new.dt_name and is_new = 1; new.is_new = 1; end end
или изменения индекса по data_ на бОльших объемах данных

Оптимизация запроса Найти похожие ветки