"Тормозной" запрос...

[AnryGTR]

Здравствуйте, уважаемые ГУРУ!
Создаю запросы мастер-деталь следующего вида(FIBPlus):

Код: Выделить всё

  fdset_datas.Close;//мастер-таблица
  fdset_datas.SQLs.SelectSQL.Clear;
  fdset_datas.SQLs.SelectSQL.Add('select distinct(iD_otd),NAim_OTD from rsx_mat');
  fdset_detal.SQLs.SelectSQL.Clear;//деталь-таблица
  fdset_detal.SQLs.SelectSQL.Add('select distinct(rsx_mat.id) as f_2,naim_mat,ed_izm,rasxod.id_mat,');
  fdset_detal.SQLs.SelectSQL.Add('(select rsx_mat.cena*count(visits.id) from visits where opl=1 and dat>='+d1+' and dat<='+d2+
   'and kod_usl in (select id_usl from rsx_mat where rsx_mat.id_otd=:ID_otd and rsx_mat.id=rasxod.id_mat))');
  fdset_detal.SQLs.SelectSQL.Add('from rsx_mat,rasxod where rsx_mat.id_otd=:ID_otd and rasxod.id_mat=rsx_mat.id');
  fdset_datas.Open;

Так вот - это запрос для отчёта... Но данный запрос тормозит нереально! В смысле, он тормозит как в самом начале при первом получении данных, а при движении в сетке мастер-таблицы вообще зависы - иногда до нескольких минут!!! .Все подозрения у меня на конструкцию "IN", или я ошибаюсь?
Подскажите пожалуйста может запрос коряво написан? Может без IN можно как-то написать, а то без этой конструкции, по-другому я те же данные получить не могу - наверное просто знаний не хватает...

[Merlin]

Ты всерьёз думаешь, что кто-то будет ломать глаза и выковыривать за тебя текст запроса из этой писанины? Оптимист.

[AnryGTR]

Ну, а как по-другому...скажите как?
Я постараюсь по-другому объяснить

[AnryGTR]

Извиняюсь, попробую объяснить словами...
Есть следующие таблицы:

отделы (id,наименование)
услуги (id,id_отдела,...)
расходный_материал (id,наименование,количество,...)
заказы (id,id_услуги,дата,...)
услуги_расход (id_услуги,id_расходный_материал,...) //т.е. одна услуга может использовать несколько расх.материалов

Количество строго прописано у каждого материала. Вот мне нужно получить в мастер-таблице список отделов(ну это легко), а в деталь-таблице наименования расходного материала, который был использован отделом в заданный временной период и общее потраченное количество каждого материала...

[Кузнецов Евгений]

Доброго времени суток!

To Attid

Да, запрос действительно неудачный.

Метаданные тоже вызывают вопросы:
Почему услуга жестко связана с отделом - каждый отдел выполняет раз и навсегда утвержденные работы?

Расценка привязана к материалу - она никогда не меняется
или прошлое Вас не интересует?

Количество в таблице расходных материалов - остатки материалов на текущий момент?

услуги_расход привязаны к услугам, а не к заказам - для каждой услуги при любых обстоятельствах расходуются одни и те же материалы, причем одинаковое количество?

[AnryGTR]

2 Кузнецов Евгений
По порядку:
1) насчёт отделов - ДА каждый отдел выполняет строго свои услуги и одну и ту же услугу несколько отделов выполнять не могут!!!
2) Насчёт расценки - цены здесь лишь формальность(на данный момент прошлое не интересует )... главное количество!
3) количество в таблице расходных материалов - это то количество с которым идёт сам расходный материал.
4) услуги_расход привязаны к услугам т.к. в этой конторе специфика работы такая - услуга всегда производится со строго прописанным для неё расходным материалом, ПРИЧЁМ с одинаковым количеством!

Вот так...
ответа конечно не получил, но всё равно спасибо что рассмотрели мой вопрос!

[Кузнецов Евгений]

Доброго времени суток!

3) количество в таблице расходных материалов - это то количество с которым идёт сам расходный материал.

Замечательно! Услуга 1 использует k шт материала A, услуга 2 использует n шт. материала A - какое количество будет храниться?
Вообще говоря, БД должна быть более гибкой - все течет, все изменяется и завтра может оказаться, что для услуги 1 в одних обстоятельствах требуется k1 шт. материала A, в других - k2...
Поскольку в Ваших метаданных я ничего не понимаю, приведу свой скрипт

Код: Выделить всё

/* Таблица материалов */
create table materials
(id integer not null,
 name varchar(80),
 current_price double precision not null); /* Текущая расценка */

/* Таблица отделов */
create table departments
(id integer not null,
 name varchar(80));

/* Таблица услуг */
create table services
(id integer not null,
 name varchar(80),
 department_id integer not null); /* Код отдела */

/* Таблица заказов */
create table orders
(id integer not null,
 service_id integer not null,
 exec_date DATE); /* Дата выполнения */

/* Расход материала */
create table material_expenses
(service_id integer not null,
 material_id integer not null,
 quantity double precision not null); /* Количество израсходованного материала */

/* Здесь должны быть объявления первичных и внешних ключей */

А теперь - сам запрос

Код: Выделить всё

select m.id,min(m.name) as material_name,
         sum(me.quantity) as quantity,
         sum(me.quantity*m.current_price) as Summ
  from orders o left join material_expenses me
         on me.service_id = o.service_id
       left join materials m
         on me.material_id = m.id
       inner join services s
         on o.service_id = s.id
  where s.department_id = :dep_id
  and o.exec_date between :start_date and :end_date
  group by m.id

Как нетрудно понять, параметры start_date, end_date - период выполнения заказа, dep_id - код отдела

[AnryGTR]

2 Кузнецов Евгений:

Большое Вам спасибо, запрос работает чётко и главное не тормозит!!!
Только я так и не понял зачем к текстовому полю применять функцию MIN? И без GROUP BY тож не пашет...
А ещё в некоторых случаях в конце почему-то добавляется пустая запись... , так я и не смог понять почему и откуда она берётся - просто тупо фильтрую конечный датасэт!

Ещё раз СПАСИБО!!!

[Кузнецов Евгений]

Доброго времени суток!

Только я так и не понял зачем к текстовому полю применять функцию MIN? И без GROUP BY тож не пашет...

Смотрите - у нас в общем случае есть набор заказов на разные услуги, которые требуют расхода материалов. Чтобы получить статистику по материалам, а не набор повторяющихся записей, мы должны использовать группировку (GROUP BY) относительно первичного ключа materials.
Так как при группировке в SELECT-секции должны фигурировать только либо поля, по которым группируем, либо агрегаты (sum,min,...), то чтобы не делать лишнюю группировку по имени материала, я обернул ее в агрегат. Поскольку наименование материала однозначно определяется первичным ключом, то min будет вычисляться для единственной записи.
А вообще Вам очень бы не помешало почитать что-нибудь по SQL - например, М.Грабера (http://www.ibase.ru/develop.htm#books)

А ещё в некоторых случаях в конце почему-то добавляется пустая запись... , так я и не смог понять почему и откуда она берётся - просто тупо фильтрую конечный датасэт!

Это, похоже, моя ошибка - я не учел, что могут быть услуги, не требующие расхода материалов. Для таких, очевидно, не будет записей в material_expenses. Исправить это можно, заменив в запросе left join materials на inner join materials.

[Attid]

Доброго времени суток!

To Attid

Да, запрос действительно неудачный.

я то тут при чем ?? =)

[stix-s]

просто тупо фильтрую конечный датасэт!
Ещё раз СПАСИБО!!!

Кстати, если запрос возвращает тучу записей, рекомендую в опциях используемых датасетов выставить

Код: Выделить всё

роFetchAll=false