PHP DM Gate v0.9 » Оптимизация запроса <span class="vd">[FB2]</span>

kadr (12.05.08 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 (12.05.08 10:35) [1]

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

<Цитата>
Sergey13 © (12.05.08 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) предложил.

<Цитата>
ЮЮ © (12.05.08 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 и более миллионов разобъется на сомножители.

<Цитата>
ЮЮ © (12.05.08 10:47) [4]

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

<Цитата>
ANB (12.05.08 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 (12.05.08 13:56) [6]

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

<Цитата>
kadr (12.05.08 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 (12.05.08 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 © (12.05.08 15:41) [9]

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

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

<Цитата>
kadr (12.05.08 16:13) [10]

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

<Цитата>
ANB (12.05.08 16:50) [11]

> kadr (12.05.08 14:53) [7]

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

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

<Цитата>
ANB (12.05.08 16:51) [12]

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

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

<Цитата>
kadr (12.05.08 17:34) [13]

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

<Цитата>
Игорь Шевченко © (12.05.08 21:52) [14]

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

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

<Цитата>
PEAKTOP © (13.05.08 00:20) [15]

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

Дима Еманов, Влад Хорсун, 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. Тогда вообще все будет шустро

<Цитата>
Виталий Панасенко(дом) (13.05.08 00:44) [16]

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

<Цитата>
kadr (13.05.08 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_ на бОльших объемах данных

<Цитата>