Модераторы: Akella
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Оптимизация кода процедуры 
:(
    Опции темы
Akella
  Дата 17.1.2012, 00:14 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Творец
****


Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329
FB 2.0

Есть таблица объектов, очень много полей.
Основное поле это ID объекта - первичный ключ, остальные поля в данном случае нас не интересуют.

Есть таблица телефонов, где нас интересуют поля TEL, ID_ARENDA и ID_MEDIATOR
Код

CREATE TABLE PHONES (
    ID           "INT" NOT NULL /* "INT" = INTEGER */,
    TEL          STRING20 COLLATE PXW_CYRL /* STRING20 = VARCHAR(20) */,
    ID_ARENDA    "INT" /* "INT" = INTEGER */,
    ID_MEDIATOR  "INT" /* "INT" = INTEGER */
);


Есть таблица посредников. Фактически такая же,  как и таблица объектов, только поля другие.
В таблице посредников нас тоже интересует ID посредника - первичный ключ.

Таблица телефонов связана с таблицей объектов по внешнему ключу (ID - ID_ARENDA)
Также таблица телефонов связана с таблицей посредников по внешнему ключу (ID - ID_MEDIATOR)

user posted image

Само собой, что посредники и объекты могут иметь практически неограниченное число номеров телефонов, хотя, как показывает практика их бывает 1-3 у объекта и значительно больше у посредника.

Что требуется.
В таблицу объектов требуется добавить поле "Посредник по номеру телефона", которое будет вычисляться на ходу - calculated. Т.е. нужно динамически вычислять название посредника, если номер телефона объекта совпал с номером телефона посредника.
Я так подумал, что можно вычислять используя хранимую процедуру, т.к. много разных действий.
Поле "Посредник по номеру телефона" будет отображать название посредника, основываясь на номере(ах) телефона(ов) объекта.

Допустим у объекта есть номер 0667121717. И есть посредник с таким же номером. Т.е. физически в таблице телефонов живет 2 одинаковых телефона, только в полях ID_ARENDA и ID_MEDIATOR разные ID из разных таблиц.
Как мне представляется и как уже реализовано: на вход процедуры получаем ID объекта, по этому ID вычисляем номер телефона объекта, а вернее список номеров телефонов, т.к. у объекта может быть 2-3 телефона:
Код

    for select p.tel from phones p where (p.id_arenda is not null) and (p.id_arenda = :id_object) into :TEL do
    if (TELS_LIST = '') then
      TELS_LIST = coalesce(:TEL, '');
     else
      TELS_LIST = TELS_LIST || coalesce(:delim || :TEL, '');
-- получаем: 0667121717,0995417812



Теперь соединением (inner join) через специальную процедуру ищем эти телефоны в таблице телефонов (опять пробегаемся по таблице телефонов), но с другим условием, чтобы поле ID_MEDIATOR было НЕ пустое,
т.е. ищем посредников по условию: where phones.id_mediator is not null and phones.tel = номер телефона.

Код

for select p.id_mediator from phones p --цикл по таблице телефонов
  inner join unlist(:TELS_LIST, :delim) ul on (1=1) -- TELS_LIST - здесь список телефонов через запятую, delim - это запятая
where
  (p.tel = ul.row)
  and
  (p.id_mediator is not null)

into :MED_ID


теперь внутри вложенного цикла нужно найти название посредника:

Код

--Мы нашли ID посредника и теперь нужно узнать его имя
for select m.name from mediators m where m.id = :MED_ID into :med_name do




В целом последний цикл будет выглядеть так:

Код

--нужно вычислить имена посредников по номерам телефонов
--передаем список телефонов через запятую в переменной TELS_LIST
for select p.id_mediator from phones p --цикл по таблице телефонов
  inner join unlist(:TELS_LIST, :delim) ul on (1=1)
where
  (p.tel = ul.row)
  and
  (p.id_mediator is not null)

into :MED_ID
do
begin
  for select m.name from mediators m where m.id = :MED_ID into :med_name do
  begin
--собираем названия посредников через запятую
    if (MED_NAMES = '') then
      MED_NAMES = coalesce(:MED_NAME, '');
    else
      MED_NAMES = MED_NAME || :delim || coalesce(:MED_NAME, '');
 end

end

  res = :MED_NAMES;
  suspend;


Таким образом получается очень много чтений. smile 
Что можно придумать, чтобы ускорить код?
Структуры таблиц, ключей менять уже поздно. Ну разве что чуть-чуть.

Вот полный код процедуры:
Код

CREATE OR ALTER procedure SP_GET_MED_NAME_BY_PHONES (
    ID_OBJECT integer,
    ID_SUPERTYPE integer)
returns (
    RES varchar(512))
as
declare variable C_PH1 varchar(20);
declare variable C_PH2 varchar(20);
declare variable MED_ID integer;
declare variable DELIM varchar(1);
declare variable TEL varchar(20); /* номер телефона */
declare variable TELS_LIST varchar(512); /* список телефонов */
declare variable MED_NAME varchar(50);
declare variable MED_NAMES varchar(512);
begin
  res = null;

  if (not exists(select tt.id from toptions2 tt where UPPER(tt.name) = 'ENABLE_MED_NAME_BY_PHONES' and tt.val = '1')) then
  begin
    res = null;
    suspend;
    exit;
  end

--  exit;

  IF ((ID_OBJECT is null) OR (ID_SUPERTYPE is null))  THEN exit;

  /*
  res = :id_supertype;
  suspend;
  exit;
*/

  TELS_LIST = '';
  delim = ',';
  MED_NAMES = '';
  MED_NAME = '';
  c_ph1 = '';
  c_ph2 = '';


--первый этап
--получаем список телефонов объекта недвижимости через запятую, телефонов, как правило, 1-3


--аренда
  if (id_supertype = 5) then
  begin
    for select p.tel from phones p where (p.id_arenda is not null) and (p.id_arenda = :id_object) into :TEL do
    if (TELS_LIST = '') then
      TELS_LIST = coalesce(:TEL, '');
     else
      TELS_LIST = TELS_LIST || coalesce(:delim || :TEL, '');
  end


--квартиры, дома, новостройки
  if ((id_supertype = 0) or (id_supertype = 1) or (id_supertype = 4)) then
  begin
    for select p.tel from phones p where p.id_apart = :id_object into :TEL do
    if (TELS_LIST = '') then
      TELS_LIST = coalesce(:TEL, '');
     else
      TELS_LIST = TELS_LIST || coalesce(:delim || :TEL, '');
  end



--коммерческая
  if (id_supertype = 2) then
  begin
    for select p.tel from phones p where p.id_off = :id_object into :TEL do
    if (TELS_LIST = '') then
      TELS_LIST = coalesce(:TEL, '');
     else
      TELS_LIST = TELS_LIST || coalesce(:delim || :TEL, '');
  end




--заявки (покупатели)
  if (id_supertype = 7) then
  begin
    for select c.phone1, c.phone2 from client c where c.id = :id_object into :c_ph1, :c_ph2 do
    if ((c_ph1 = '') or (c_ph1 is null) )then
      TELS_LIST = coalesce(:c_ph2, '');
     else
      TELS_LIST = :c_ph1 || coalesce(:delim || :c_ph2, '');
  end


--если нет совпадений, то выходим из процедуры
  IF (TELS_LIST = '') THEN
  begin
    RES = '';
    suspend;
    exit;
  end





--второй этап


--нужно вычислить имена посредников по номерам телефонов
--передаем список телефонов через запятую
--И ОПЯТЬ цикл по таблице телефонов
    for select p.id_mediator from phones p
      inner join unlist(:TELS_LIST, :delim) ul on (1=1)
    where
      (p.tel = ul.row)
      and
      (p.id_mediator is not null)--это важно, т.к. нужно вычленить только посредников из таблицы телефонов
    
    into :MED_ID
    do
    BEGIN --теперь каждый ID посредника ищем в таблице посредников и названия собираем через запяту

          for select m.name from mediators m where m.id = :MED_ID into :med_name do
          begin
            if (MED_NAMES = '') then
              MED_NAMES = coalesce(:MED_NAME, '');
            else
              MED_NAMES = MED_NAME || :delim || coalesce(:MED_NAME, '');
          end
    
    END

-- выводим результат
  res = :MED_NAMES;
  suspend;
end


Также созданы доп. индексы для полей (ID_MEDIATOR+TEL) и (TEL + ID_ARENDA) в таблице телефонов.
Ну в таблице посредников по полю name.

фух... ну и наворотил.
PM MAIL   Вверх
tishaishii
Дата 17.1.2012, 06:22 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Создатель
***


Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8
Где наворотил?
Кода много, но он весь повторяющийся: простые выборки и описание агрегата list(all field, :separator).

Это сообщение отредактировал(а) tishaishii - 17.1.2012, 06:25
PM MAIL ICQ Skype   Вверх
Frees
Дата 17.1.2012, 06:29 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
****


Профиль
Группа: Завсегдатай
Сообщений: 2233
Регистрация: 2.12.2005
Где: Екатеринбург

Репутация: 1
Всего: 54
Цитата(tishaishii @  17.1.2012,  09:22 Найти цитируемый пост)
агрегата list

в 2.0 его нет


--------------------
Кольцов Виктор Владимирович
PM MAIL ICQ   Вверх
Akella
Дата 17.1.2012, 10:54 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Творец
****


Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329
Цитата(tishaishii @  17.1.2012,  06:22 Найти цитируемый пост)
 list(all field, :separator).

вместо этого я использую inner join c процедурой, которая список превращает в виртуальную таблицу и можно соединиться с этой таблицей

Код

inner join unlist(:TELS_LIST, :delim) ul on (1=1)
    where
      (p.tel = ul.row)


http://forum.vingrad.ru/index.php?showtopi...t&p=2042046

Добавлено через 10 минут и 48 секунд
Меня волнует, что мне приходится 2 раза в процедуре проходить таблицу телефонов. Первый раз для поиска номера телефона, а второй раз для поиска ID посредника :(, а таблица телефонов содержит почти 100 тыщ записей.
PM MAIL   Вверх
tishaishii
Дата 18.1.2012, 07:02 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Создатель
***


Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8
Можно триггерами заполнять обычное поле (не calculated), тогда будут индексы.
Вместо unlist использовать формат

Код

:IN_PHONE:='/0123456789/0123446789/0123556789/';
where position(concat('/', t.phone, '/') in :in_phone)>0


А ещё можно создать таблицу для поисковых запросов и таблицу для списков номеров из запроса, которая будет заполняться из unlist, каждая запись помечаться uuid, например. Индексы.

Это сообщение отредактировал(а) tishaishii - 18.1.2012, 07:09
PM MAIL ICQ Skype   Вверх
tishaishii
Дата 3.3.2012, 23:09 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Создатель
***


Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8
Не понятно для чего нужна unlist. Уж вести в таблице или в массиве.
PM MAIL ICQ Skype   Вверх
Akella
Дата 3.3.2012, 23:28 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Творец
****


Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329
Что/чего вести?
PM MAIL   Вверх
tishaishii
Дата 4.3.2012, 15:55 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Создатель
***


Профиль
Группа: Завсегдатай
Сообщений: 1262
Регистрация: 14.2.2006
Где: Москва

Репутация: нет
Всего: 8
Имею в виду, можно номера телефонов вести в отдельной таблице с кодами и всяческой дополнительной аналитической информацией. Сохранять актуальность записи с помощью системы триггеров.
PM MAIL ICQ Skype   Вверх
Akella
Дата 4.3.2012, 17:25 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Творец
****


Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329
А телефоны и так в отдельной таблице.
PM MAIL   Вверх
  
 Ответ в темуСоздание новой темы Создание опроса
Правила форума "Interbase"
Alex

Обязательно указание:

1. Версию InterBase (Firebird, Yaffil)

2. Способа доступа (ADO, BDE, IBX и т.д.)

  • КАК ПРАВИЛЬНО ОФОРМИТЬ КОД - ЗДЕСЬ
  • КАК ПРАВИЛЬНО УКАЗАТЬ ТЕКСТ ОШИБКИ - ЗДЕСЬ
  • Действия модераторов можно обсудить здесь
  • С просьбами о написании курсовой, реферата и т.п. обращаться сюда
  • FAQ раздела лежит здесь!

Если Вам понравилась атмосфера форума, заходите к нам чаще! С Уважением, Akella.
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | Firebird, Interbase | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.0847 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.