SEO

Hi, all.

Here's approximate design with which I have to work. At source I have:
create table source(some_id integer, iterations integer);

And I should make:
create table target(some_id integer, iteration_counter integer);

For example, if I have row (1, 5) in table source, I have to have rows
(1,1)
(1,2)
(1,3)
(1,4)
(1,5)
in target table.

OK this is not actual design, but this is essence of my task.

--------------------------------------------------------------------------------------------
One of my ideas - I make a cursor from source table and go through all
rows in it,
for each row calling procedure, which enters records in target table

FOR iteration AS iteration_cursor CURSOR FOR
select id, iterations
from source
DO
call enter_records(id, iterations);
END FOR;

enter_records procedure:

create procedure enter_records(p_id integer, p_iterations integer);
begin
declare v_iterations integer;
set v_iterations = p_iterations;

WHILE v_iterations>-1 LOOP
insert into target(id, iterations_counter) values (p_id, v_iterations);
set v_months_count=v_months_count-1;
END LOOP;
commit;
end;

--------------------------------------------------------------------------------------------

This works, but unacceptably slow, processing few records per second.


I improved performance by making Java procedure.

FOR iteration AS iteration_cursor CURSOR FOR
select id, iterations
from source
DO
WHILE v_iterations>-1 LOOP
call file_writer.main(id, iterations_counter);
set v_months_count=v_months_count-1;
END LOOP;
END FOR;

Java procedure file_writer.main writes id and iteration_counter to
text file, at end I make one big load, which inserts text file into
target table. Performance slightly improved, now it is about 1000 taget
records per minute, which still is very slow.

Currently I am out of ideas, how to improve performance. Cursor
processing seems to be the bottleneck. Any ideas how to avoid using
cursor? Or anything else, how to improve performance?

Thanks in advance!

A potential approach:

- Prepare a table that has rows with all level of interations that you'll
ever need, e.g. rows with values from 1 to 1000. Let's call this table
t_it (interations int not null).

- Instead of looping n times over the source table, you could then simply
join against that prepared table:

insert into target (id, iterations_counter)
select (source.id, it.interation)
from source, it
where source.id = p_id and it.iteration between 1 and p_iterations

You'll need to do this for all p_id's from your source table.

Jochen

Thanks! It seems to be working!