skorohod2 vybegallo

> Есть такой старый анекдот
> Сидит чукча на дереве и пилит под собой ветку. Прохожий ему
> - что ты делаешь, ты же упадешь !
> чукча продолжает пилить, падает, встает - "Колдун, однако !"
> Я понятно намекаю ?

Амвросий Амбруазович!
Я тут посмотрел по форуму...
Вы оказывается не только в нашем топике проверяете, чтобы народ на нужных ветках сидел и в руки ничего лишнего не брал.
Как, не тяжело за всеми сразу следить??? На пенсию не пора еще?
Я понятно намекаю ?

-- Ничего, ничего, -- сказал Выбегалло. -- Это вам не Португалия.
Критики не любите. Лет триста назад я бы с тобой тоже не особенно
церемонился, кафолик недорезанный.

adisk VybegalloTo adisk :

Это ваше "универсальное хранилище" придумывается каждым программистом баз даных на определенном этапе профессионального роста. Позже приходит понимание, что выигрыщ в простоте проектирования не стоит сооруженного на свою голову геморроя с запросами. А потом приходит заказчик и закатывает истерику, что все работает недопустимо медленно...

Что ж.
Возможно еще не дорос.
Прекрасно осознаю, что будет падение производительности.
Будет ли оно столь ощутимым? Думаю нет.

Ну что ж, раз никто из авторов не рискнул хлебнуть супца собственного приготовления - пришлось протестировать собственноручно.
Напомню задачу :
Дано :
таблица customers, 100 000 записей
customer_id int
customer_desc char(50)

Таблица orders, 1 000 000 записей
order_id int
customer_id int
order_desc char(50)
order_cost decimal (9,2)
order_date date
order_flag char(1)

Требуется :
найти сумму отгруженных договоров (order_flag="Y") с 1.1.2004 по 31.12.04 по каждому покупателю и описание покупателя.

select customer_id, customer_desc , sum(order_cost) from customers, orders
where customers.customer_id = orders.customer_id
and order_flag = "Y"
and order_date between "01/01/2004" and "12/31/2004"
group by 1, 2

-----
Результат решения "в лоб" -
от
No indexes, order_date between 1/1/04 and 12/31/04
optimized uses dynamic hash join
real 0m36.64s
user 0m1.31s
sys 0m0.16s
до
---- 3 : indexes on c.customer_id, o.customer_id, o.order_date ,
real 1m13.51s
user 0m1.72s
sys 0m0.14s

т.е от 36 до 74 секунд.

Нарисовал я рекомендуемую схему, загнал тестовые данные, построил индексы, статистику обновил. Нарисовал запрос (естественно, с использованием view), запустил...

real 37m26.49s
user 0m0.04s
sys 0m0.06s

:-)))))))

Наблюдается небольшое падение производительности - всего лишь в 30-60 раз !

--------- скрипт для желающих проверить лично -----

create table data_s
(id serial,
id_s int,
id_p int,
data varchar(50)) extent size 1000000 next size 200000;

create table data_i
(id serial,
id_s int,
id_p int,
data int) extent size 100000 next size 20000;

create table data_dc
(id serial,
id_s int,
id_p int,
data decimal(12,2)) extent size 100000 next size 20000;

create table data_dt
(id serial,
id_s int,
id_p int,
data date) extent size 100000 next size 20000;

create table data_c
(id serial,
id_s int,
id_p int,
data char(1)) extent size 100000 next size 20000;

create table attr
(id int,
value char(50));

insert into attr values (1, "customer_id");
insert into attr values (2, "customer_desc");
insert into attr values (3, "order_id");
insert into attr values (4, "customer_id2");
insert into attr values (5, "order_desc");
insert into attr values (6, "order_cost");
insert into attr values (7, "order_date");
insert into attr values (8, "order_flag");


create view vcustomers as
select a.data as customer_id, b.data as customer_desc
from data_i a, data_s b
where a.id_p = b.id_p
and a.id_s in (select id from attr where value = "customer_id")
and b.id_s in (select id from attr where value = "customer_desc");

create view vorders as
select a.data as order_id, b.data as customer_id, d.data as order_cost,
e.data as order_date, f.data as order_flag
from data_i a, data_i b, data_dc d, data_dt e, data_c f
where
a.id_p = b.id_p
and a.id_p = d.id_p
and a.id_p = e.id_p
and a.id_p = f.id_p
and a.id_s = 3
and b.id_s = 4
and d.id_s = 6
and e.id_s = 7
and f.id_s = 8;

--drop procedure ins_cust;
create procedure ins_cust (nm int)
define i int;
define c char(30);

for i = 1 to nm
insert into data_i values (0, 1, i, i);
let c = "Customer desc" || i;
insert into data_s values (0, 2, i, c);
end for
end procedure;

create procedure ins_orders (nprev int, nm int)
define i, j int;
define c char(30);
define cost decimal (12,2);
define dt date;

for i = 1 to nm
for j = 1 to 10
insert into data_i values (0, 3, (nprev + i), i); -- order_id
insert into data_i values (0, 4, i, i); -- customer_id
let c = "Order desc" || i || "_" || j; -- order_desc
insert into data_s values (0, 5, i, c);
let cost = i * 1.0;
insert into data_dc values (0, 6, i, cost); -- order_cost
let dt = today - (i / 100);
insert into data_dt values (0, 7, i, dt); -- order_date
if (mod (i,2) = 0) then
insert into data_c values (0, 8, i, "Y");
else
insert into data_c values (0, 8, i, "N");
end if
end for
end for
end procedure;

execute procedure ins_cust (100000);

execute procedure ins_orders(1000000, 100000);


create index is1 on data_s (id_p);
create index is2 on data_s (id_s);

create index ii1 on data_i (id_p);
create index ii2 on data_i (id_s);

create index idc1 on data_dc (id_p);
create index idc2 on data_dc (id_s);

create index idt1 on data_dt (id_p);
create index idt2 on data_dt (id_s);

update statistics;

select customer_id, customer_desc , sum(order_cost) from vcustomers, vorders
where vcustomers.customer_id = vorders.customer_id
and order_flag = "Y"
and order_date between "01/01/2004" and "12/31/2004"
group by 1, 2

ChA vybegalloА если И построить вместо как один индекс для IOT(правильно ? бишь кластерный) по (id_p, id_s) для всех таблиц свойств ?
А также для таблицы data_dt не забыть проиндексировать еще и по полю Data, если конечно не выбирается практически весь диапазон.
Кстати, а какой смысл поля serial в данном контексте ?
Проверил бы сам, но под рукой только MSSQL, а переводить лениво :)

Да я еще поиграюсь в понедельник с индексами. Запустил update statistics high, так он мне новый план построил - на 1 час 4 минуты :-)
Проиндексировать по дате есть смысл, но если прикинуть всю систему в целом, скажем, 100 таблиц, и все сливают свои даты в одну таблицу...
view так же переделал, но тестировать уже не было времени - уж больно я задачу упростил этими id_s = 3 :-) вместо where id_s in (select ... from attr ...).
Короче, нашим юным пионерам есть о чем задуматься в плане производительности.

Я, кстати, не хочу сказать, что такая схема всегда и обязательно зло. Скажем, кто-то на форуме постил задачу - требуется учесть атрибуты товаров, причем товаров много, и атрибуты у них разнообразные. Ну не заводить же 1000 таблиц для каждого товара отдельно ? Имеет смысл атрибуты слить в одну таблицу. Но при этом надо ясно понимать и отдавать себе отчет в том, какой геморрой в плане запросов и производительности тебе предстоит - а не эти вот заявления "думаю, падение производительности будет неощутимым". Ты, блин, не думай - ты попробуй !

Очередные результаты тестирования.
Ничего утешительного для любителей простых схем и сложных запросов :
real 50m53.88s
user 0m0.05s
sys 0m0.07s

Самое пикантное, что SELECT остался прежним, только вместо реальных таблиц приходится использовать вьюхи. Поскольку сложность написания запросов "влоб", без view, зашкаливает за пределы разумного. Стоило ли огород городить ?


Изменения, внесенные в схему :
1. create view vcustomers as
select a.data as customer_id, b.data as customer_desc
from data_i a, data_s b
where a.id_p = b.id_p
and a.id_s = 1 --in (select id from attr where value = "customer_id")
and b.id_s = 2; --in (select id from attr where value = "customer_desc");

create view vorders_ip (id_p, order_id, customer_id) as
SELECT id_p
,MAX(CASE id_s WHEN 3 THEN data END)
,MAX(CASE id_s WHEN 4 THEN data END)
FROM data_i
GROUP BY id_p;

create view vorders as
select
v.order_id,
v.customer_id,
d.data as order_cost,
e.data as order_date,
f.data as order_flag
from vorders_ip v, data_dc d, data_dt e, data_c f
where d.id_p = v.id_p
and e.id_p = v.id_p
and f.id_p = v.id_p
and d.id_s = 6
and e.id_s = 7
and f.id_s = 8;

3.
create index is1 on data_s (id_p, id_s);
create index is2 on data_s (id_s, id_p);

create index ii1 on data_i (id_p, id_s);
create index ii2 on data_i (id_s, id_p);

create index idc1 on data_dc (id_p, id_s);
create index idc2 on data_dc (id_s, id_p);

create index idt1 on data_dt (id_p, id_s);
create index idt2 on data_dt (id_s, id_p);