Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вместо циклов / 20 сообщений из 20, страница 1 из 1
18.03.2020, 09:17
    #39938530
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
Друзья, подскажите пожалуйста как оптимизировать такую задачу. Есть таблица с полем VARCHAR2 и двумя полями типа VARRAY. В первом поле храниться имя, во втором - 512 исходных значений, в третье поле записываю результат функции. Функция делает преобразование Фурье и возвращает массив VARRAY. Такой подсчет идет по каждой строке. Строк очень много. Все работает и считает, но по причине использования циклов все это работает очень медленно.
Подскажите пожалуйста, возможно ли решить эту задачу SQLем (аналитическими функциями, конвеерными функциями)? В каком направлении копать?

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
NAM VAL         поле с результатом
A	1                    ?
	4                    ?
	8
	7
	9
	3
B	7
	4
	1
	2
	3
	9
......


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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 (массив)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 09:48
    #39938552
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow432,

На вскидку: нормализовать, убратв varray, и добавив number(10,3). Это уберёт цикл. Двойной цикл получится в результате декартова произведения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 09:54
    #39938559
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
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);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:01
    #39938565
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow432
но я ума не приложу как мне прикрутить это декартово произведение к подсчетам Фурье

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

Не прокатит - пили трехзвенку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:19
    #39938578
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
crutchmaster,

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


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

A 1
A 4
A 8
........
512 строк
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:27
    #39938583
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow432
развернуть этот массив в общей таблице?

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

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

Да. Будет мегабайт 5-10 на запись. Херня, конечно, лучше сразу трехзвенка. Хотя, если это всё сразу сгрупировать или вставить, то должно быть норм. Но оверхед всё равно дикий.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:38
    #39938591
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
crutchmaster,

Должно быть решение SQLем, BULK COLLECT/FORALLы. Просто нужно очень широкое видение возможностей ORACLE. Слишком большие объемы чтобы передавать во внешку. Не думаю что циклы в том же PYTONе будут работать гораздо быстрее. А с учетом передачи данных туда сюда...
В любом случае, спасибо за помощь! Буду копать дальше. Может асы подскажут еще.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:52
    #39938599
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow432
Слишком большие объемы чтобы передавать во внешку.

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

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

Если не по одной строчке, то тоже не должно быть особых тормозов в этом месте. Передаешь метров 100 сырых данных, считаешь, передаёшь обратно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 10:53
    #39938600
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow432
Может асы подскажут еще

Оракл-спецефичное может что и есть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 11:55
    #39938642
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 12:00
    #39938648
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
вдогонку

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 12:41
    #39938668
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
што-то такое получилось

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 13:27
    #39938705
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
Stax,


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

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

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

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


Сейчас попробую с декартовым переварить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 13:45
    #39938713
ln123
ln123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
shadow4321. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.


cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt) детерминировано для k, n, l_rows_cnt а т.к. у вас эти значения повторяются от вычисления к вычислению, то логично посчитать их один раз и при следующих вызовах функции обращаться к уже сохраненным значениям.
Так же не очень хорошо что у вас вычисления идут с типом number, стоит от этого уйти и попробовать Native compilation.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 14:34
    #39938745
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 14:37
    #39938748
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
ln123,

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 16:59
    #39938828
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
ln123, Спасибо за ценное замечание. Поправил этот баг. В начале выполнения программы считаю отдельными циклами и засовываю эти значения в двумерный ассоциативный массив. Скорость выросла в 10 раз.
Native compilation никогда не использовал. Где почитать про это? Нужны будут права SYS я так понимаю?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 17:17
    #39938836
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 17:50
    #39938849
shadow432
shadow432
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
Stax, по всей видимости именно расчет синусов и косинусов жрал большую часть времени. Вынес их расчет в двухмерный ассоциативный массив и код "задышал". Конечно, наверное можно еще оптимизировать, но такой результат для меня уже приемлем. Спасибо за помощь!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2020, 18:20
    #39938862
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql вместо циклов
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 периодические...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вместо циклов / 20 сообщений из 20, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 415ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1881448]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 15ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 265ms
total:  415ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]