Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
Друзья, подскажите пожалуйста как оптимизировать такую задачу. Есть таблица с полем VARCHAR2 и двумя полями типа VARRAY. В первом поле храниться имя, во втором - 512 исходных значений, в третье поле записываю результат функции. Функция делает преобразование Фурье и возвращает массив VARRAY. Такой подсчет идет по каждой строке. Строк очень много. Все работает и считает, но по причине использования циклов все это работает очень медленно.
Подскажите пожалуйста, возможно ли решить эту задачу SQLем (аналитическими функциями, конвеерными функциями)? В каком направлении копать?

Вот пример данных

NAM VAL         поле с результатом
A	1                    ?
	4                    ?
	8
	7
	9
	3
B	7
	4
	1
	2
	3
	9
......

В принципе я развернул данные без массива (varchar2 и number) и попробовал так, но опыта не хватает построить алгоритм подсчета

NAM VAL     поле с результатом
A	1              ?
A	4              ?
A	8
A	7
A	9
A	3
B	7
B	4
B	1
B	2
B	3
B	9


в функцию передаю поле VAL (массив)

CREATE OR REPLACE TYPE val_curr IS VARRAY (1024) OF number (10,3)

function give_fure(p_val_varr val_curr) return val_curr is
  l_val_re   val_curr := val_curr();
  l_val_im   val_curr := val_curr();
  l_val_out  val_curr := val_curr();
  l_pi       number(15, 14) default 3.14159265358979;
  l_rows_cnt pls_integer default p_val_varr.count;
begin
  l_val_re.extend(l_rows_cnt);
  l_val_im.extend(l_rows_cnt);
  l_val_out.extend(l_rows_cnt);
  for k in 1 .. l_rows_cnt loop
    for n in 1 .. l_rows_cnt loop
      l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
      l_val_im(k) := nvl(l_val_im(k), 0) - p_val_varr(n) * sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
    end loop;
  end loop;

  for n in 1 .. p_val_varr.count  loop
    l_val_out(n) := sqrt(power(l_val_re(n), 2) + power(l_val_im(n), 2));
  end loop;

  return l_val_out;
end;


Сообщение было отредактировано: 18 мар 20, 15:33
18 мар 20, 09:17    [22101069]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 948
shadow432,

На вскидку: нормализовать, убратв varray, и добавив number(10,3). Это уберёт цикл. Двойной цикл получится в результате декартова произведения.

Сообщение было отредактировано: 18 мар 20, 09:49
18 мар 20, 09:48    [22101097]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
crutchmaster, при таком раскладе получается на каждую строку из 512 записей декартово произведение дает 262144 записи. И я бы рад проверить такой алгоритм (скорее всего он будет работать быстрее), но я ума не приложу как мне прикрутить это декартово произведение к подсчетам Фурье l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
18 мар 20, 09:54    [22101104]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 948
shadow432
но я ума не приложу как мне прикрутить это декартово произведение к подсчетам Фурье

Надо id массива, значение элемента, номер элемента, количество элементов. А потом связать таблицу саму с собой по id массива и посчитать.
shadow432
дает 262144 записи

Не прокатит - пили трехзвенку.
18 мар 20, 10:01    [22101111]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
crutchmaster,

Такую структуру с массивом я выбрал сам для удобства и наглядности (это промежуточная временная таблица), но если использовать декартово произведение, то может имеет смысл сразу развернуть этот массив в общей таблице? Не понял что значит трехзвенка?


Если развернуть так, тогда декартово произведение даст 262144 записей.

A 1
A 4
A 8
........
512 строк
18 мар 20, 10:19    [22101130]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 948
shadow432
развернуть этот массив в общей таблице?

Каким образом?
shadow432
Не понял что значит трехзвенка?

Вытащить данные из субд куда-нибудь (java/пистон/жс/c#/etc), посчитать, засунуть обратно.
shadow432
Если развернуть так, тогда декартово произведение даст 262144 записей

Да. Будет мегабайт 5-10 на запись. Херня, конечно, лучше сразу трехзвенка. Хотя, если это всё сразу сгрупировать или вставить, то должно быть норм. Но оверхед всё равно дикий.

Сообщение было отредактировано: 18 мар 20, 10:27
18 мар 20, 10:27    [22101136]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
crutchmaster,

Должно быть решение SQLем, BULK COLLECT/FORALLы. Просто нужно очень широкое видение возможностей ORACLE. Слишком большие объемы чтобы передавать во внешку. Не думаю что циклы в том же PYTONе будут работать гораздо быстрее. А с учетом передачи данных туда сюда...
В любом случае, спасибо за помощь! Буду копать дальше. Может асы подскажут еще.
18 мар 20, 10:38    [22101146]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 948
shadow432
Слишком большие объемы чтобы передавать во внешку.

Передавай частями.
shadow432
Не думаю что циклы в том же PYTONе будут работать гораздо быстрее.

В питоне не будет. Во всём остальном - надо смотреть.
shadow432
А с учетом передачи данных туда сюда...

Если не по одной строчке, то тоже не должно быть особых тормозов в этом месте. Передаешь метров 100 сырых данных, считаешь, передаёшь обратно.
18 мар 20, 10:52    [22101158]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 948
shadow432
Может асы подскажут еще

Оракл-спецефичное может что и есть.
18 мар 20, 10:53    [22101159]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
shadow432,

Вы что каждый раз считаете?
раз посчитать и результат сохранить

1)
мож
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки


2)
l_val_out(n) := sqrt(power(l_val_re(n), 2) + power(l_val_im(n), 2));
понять в for k

3)
вместо l_val_out использовать re/im (p_val_varr сделать in/oit)


ps
врядли декартовое + группировка будут быстрее,
хотя пробовать надо
.....
stax
18 мар 20, 11:55    [22101224]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
вдогонку

делеть на l_rows_cnt можно за циклом
sum(a(i)/n)=sum(a(i))/n

.....
stax
18 мар 20, 12:00    [22101233]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
што-то такое получилось

with t(k,nam,val) as (
select  1,'A',	1 from dual union all
select  2,'A',	4 from dual union all
select  3,'A',	8 from dual union all
select  4,'A',	7 from dual union all
select  5,'A',	9 from dual union all
select  6,'A',	3 from dual union all
select  1,'B',	7 from dual union all
select  2,'B',	4 from dual union all
select  3,'B',	1 from dual union all
select  4,'B',	2 from dual union all
select  5,'B',	3 from dual union all
select  6,'B',	9 from dual)
select 
  t.*
-- ,(select sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))
,(select sqrt(
   power(sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
  -power(sum(t2.val*sin(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
  )
from t t2 where t2.nam=t.nam) res
from t
SQL> /

         K N        VAL        RES
---------- - ---------- ----------
         1 A          1 5.33333333
         2 A          4 5.33284082
         3 A          8 5.33136324
         4 A          7 5.32890051
         5 A          9 5.32545248
         6 A          3 5.32101892
         1 B          7 4.33333333
         2 B          4  4.3329257
         3 B          1 4.33170284
         4 B          2 4.32966489
         5 B          3 4.32681203
         6 B          9 4.32314456

12 rows selected.


.....
stax
18 мар 20, 12:41    [22101285]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
Stax,


1. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.

2. Поднял. Производительности не дало ни секунды.

3. Сделал IN OUT. Выигрыша в производительности нет.

4. Делить на cnt за циклом нельзя (п.1) Хотя опять же не исключаю что я что то не догоняю.


Сейчас попробую с декартовым переварить.
18 мар 20, 13:27    [22101361]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
ln123
Member

Откуда:
Сообщений: 60
shadow432
1. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.


cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt) детерминировано для k, n, l_rows_cnt а т.к. у вас эти значения повторяются от вычисления к вычислению, то логично посчитать их один раз и при следующих вызовах функции обращаться к уже сохраненным значениям.
Так же не очень хорошо что у вас вычисления идут с типом number, стоит от этого уйти и попробовать Native compilation.
18 мар 20, 13:45    [22101381]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
shadow432
Stax,


1. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.

2. Поднял. Производительности не дало ни секунды.

3. Сделал IN OUT. Выигрыша в производительности нет.

4. Делить на cnt за циклом нельзя (п.1) Хотя опять же не исключаю что я что то не догоняю.


Сейчас попробую с декартовым переварить.


1) раз посчитать для имени, пересчитивать токо когда поменяется (Вам виднее)

4) так нельзя?
for k in 1 .. l_rows_cnt loop
for n in 1 .. l_rows_cnt loop
l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) ;
l_val_im(k) := nvl(l_val_im(k), 0) - p_val_varr(n) * sin((2 * l_pi * (k - 1) * (n - 1)) ;
end loop;
l_val_re(k) :=l_val_re(k) / l_rows_cnt;
l_val_im(k) :=l_val_im(k)/ l_rows_cnt;

end loop;

ps
,(select sqrt(
power(sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
-power(sum(t2.val*sin(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
)
проверте результат с пл/скл (мож как всегда что-то напутал)

pss
на sin/cos времени тратится немного?
.....
stax
18 мар 20, 14:34    [22101452]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
ln123,

вычислить один раз и сохранить,а не для каждой строки

.....
stax
18 мар 20, 14:37    [22101456]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
ln123, Спасибо за ценное замечание. Поправил этот баг. В начале выполнения программы считаю отдельными циклами и засовываю эти значения в двумерный ассоциативный массив. Скорость выросла в 10 раз.
Native compilation никогда не использовал. Где почитать про это? Нужны будут права SYS я так понимаю?
18 мар 20, 16:59    [22101597]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2260
shadow432
Поправил этот баг.




1)
мож
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки

.....
stax
18 мар 20, 17:17    [22101608]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
shadow432
Member

Откуда:
Сообщений: 88
Stax, по всей видимости именно расчет синусов и косинусов жрал большую часть времени. Вынес их расчет в двухмерный ассоциативный массив и код "задышал". Конечно, наверное можно еще оптимизировать, но такой результат для меня уже приемлем. Спасибо за помощь!
18 мар 20, 17:50    [22101648]     Ответить | Цитировать Сообщить модератору
 Re: sql вместо циклов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 17833
Stax
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки

...а если еще вспомнить, что sin и cos периодические...
18 мар 20, 18:20    [22101674]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить