Преобразовать три запроса в один, сбор статистики по таблице

[korotkov]

Имею таблицу tblRegions {ID, Parent, Description}, а также tblObjavas {ID, IDRegion, DateTime, ... IDState}
По tblRegions строю дерево и в дополнительных полях хочу иметь статистику по tblObjavas для каждого IDRegion.
Собираю статистику по таблице тремя запросами по каждой записи, получается медленно - избыточные операции с базой

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

  pFIBQuery->SQL->Clear();
  pFIBQuery->SQL->Add("select COUNT(*) as Cnt from tblObjavas where IDRegion = :Parent");
  pFIBQuery->ParamByName("Parent")->AsInteger = data.id;
  pFIBQuery->ExecQuery();
  data.statistic.count = pFIBQuery->FieldByName("Cnt")->AsInteger;
...
  pFIBQuery->SQL->Clear();
  pFIBQuery->SQL->Add("select COUNT(*) as Cnt from tblObjavas where IDRegion = :Parent and IDState = 1");
  pFIBQuery->ParamByName("Parent")->AsInteger = data.id;
  pFIBQuery->ExecQuery();
  data.statistic.active = pFIBQuery->FieldByName("Cnt")->AsInteger;
...
  pFIBQuery->SQL->Clear();
    pFIBQuery->SQL->Add("select first 1 DateTime from tblObjavas where IDRegion = :Parent order by DateTime desc"); //а может MAX использовать?
  pFIBQuery->ParamByName("Parent")->AsInteger = data.id;
  pFIBQuery->ExecQuery();
  data.statistic.date = pFIBQuery->FieldByName("DateTimeRefresh")->AsDateTime;

Подскажите как можно это преобразовать в один запрос к базе!? Я полагаю должно быть быстрее
Требуется получить кол-во записей на конкретный IDRegion, количество активных записей на регион, дата последнего внесения записи.
Записи (все) из tblObjavas на клиента не тащу, открываю по мере надобности, поэтому на клиенте обработать не могу.

[Dimitry Sibiryakov]

Ну, первый и последний запросы объединяются тривиально - как ты сам верно подметил, через MAX(). Второй вливается туда же с помощью SUM(CASE.....).

[korotkov]

пример можно!?

про SUM(CASE.....) ничего не понял что там суммировать? и как можно MAX c Count в одном запросе совместить?

[WildSery]

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

select 
    count(1),
    sum(case when IDState = 1 then 1 else 0),
    max(DateTime)
  from tblObjavas where IDRegion = :Parent

[korotkov]

Спасибо WildSery!!! Время выполнения запроса и построения дерева сократилось в ДВА-ТРИ РАЗА.
Хотя всеравно осталось бльшим
База пока совсем маленькая 100 записей в tblRegions и 1500 в tblObjavas 59с -> 22с
На другой 16 записей в tblRegions и 639 в tblObjavas, сократилось с 1.6с до 0.8с
Буду посмотреть ... давно не брал я в руки лопату

Запрос получился таким, там выборка идет еще и по всем вложенным child, может поэтому такие результаты. Можно конечно переписать ХП чтобы уйти от or.

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

"select count(1)as Cnt, sum(case when IDState = 1 then 1 else 0 end) as Act, max(DateTime) as DateTime from tblObjavas where IDRegion = :Parent or IDRegion in (select ID from GetAllChilds(:Parent))"

[WildSery]

ОЧЕНЬ долго. Покажи GetAllChilds. Она случаем не из этой же самой таблицы таскает?

[korotkov]

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

CREATE PROCEDURE GetAllChilds(Parent INTEGER)
RETURNS(ID INTEGER)
AS 
BEGIN
  FOR SELECT r.ID FROM tblRegions r WHERE r.Parent=:Parent
  INTO :ID
  DO 
    BEGIN

      SUSPEND;

      IF(EXISTS(SELECT * FROM tblRegions WHERE tblRegions.Parent=:ID)) THEN
        BEGIN
         FOR
           SELECT ID FROM GetAllChilds(:ID)
           INTO :ID
           DO
             BEGIN 
               SUSPEND;
             END
        END
    END
END

[Dimitry Sibiryakov]

Ужос. Одно выкидывание EXISTS сэкономит процентов 10-20.

[WildSery]

Убери "IF(EXISTS..." - никакой смысловой нагрузки не несёт.

Если бы GetAllChilds выдавала дополнительно собственно входное значение, можно было бы

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

select
  count(1) as Cnt,
  sum(case when IDState = 1 then 1 else 0 end) as Act,
  max(DateTime) as DateTime
from GetAllChildsPlusParent(:Parent) ids join tblObjavas o on (ids.ID = o.IDRegion)

Но лучше написать отдельную ХП. Типа

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

create procedure GetValuesForAggregation(Parent integer)
returns (IDState integer, DateTime timestamp)
as
  declare variable id integer;
begin
  for select case when IDState = 1 then 1 else 0 end, DateTime
    from tblObjavas
    where IDRegion = :Parent
    into IDState, DateTime
  do
    suspend;

  for select id from tblRegions where Parent=:Parent
    into id
  do
    for select IDState, DateTime from GetValuesForAggregation(:id)
      into IDState, DateTime
    do
      suspend;
end

А затем её вызывать как

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

select count(1) as Cnt, sum(IDState) as Act, max(DateTime) as DateTime
  from GetValuesForAggregation(:Parent)

[avenger]

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

CREATE PROCEDURE GetAllChilds(Parent INTEGER)
RETURNS(ID INTEGER)
AS 
BEGIN
  FOR SELECT r.ID FROM tblRegions r WHERE r.Parent=:Parent
  INTO :ID
  DO 
    BEGIN

      SUSPEND; <=== НЕ НУЖЕН

      IF(EXISTS(SELECT * FROM tblRegions WHERE tblRegions.Parent=:ID)) THEN
        BEGIN
         FOR
           SELECT ID FROM GetAllChilds(:ID)
           INTO :ID
           DO
             BEGIN 
               SUSPEND;
             END
        END
    END
END

Первый SUSPEND не нужен

[WildSery]

Первый SUSPEND не нужен

Нет, нужен.

[korotkov]

Ужос. Одно выкидывание EXISTS сэкономит процентов 10-20.

если 10-20% ужос, то ...

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


P.S. ХП написана по мотивам "Мир Interbase" 3-е издание стр.69 PROCEDURE GETFULLNAME ...