Hello
I have very slow SP this type:
BEGIN CREATE TEMP TABLE xxx ON COMMIT DROP(); WHILE n > 0 LOOP -- n >> 0 FOR _r IN EXECUTE 'SELECT ...' LOOP
RETURNNEXT _r; EXECUTE 'UPDATE xxx SET item = 1 WHERE id = '||_r.id; n := n - 1; END LOOP; END LOOP;
END;
The main problem is too much EXECUTE commands. My first idea wos
substitute it PREPARED plans. But PREPARED plans are compiled when
procedure is first time lunched, and I can't easy use it form temp tables.
I can change PREPARE upd UPDATE item SET .. to EXECUTE 'PREPARE upd
UPDATE..' and I really saved some time. But this method isn't possible for
cmd FOR
I have two possibility solution (before ending successfull solution for SP
and temporary tables). First, the time for really preparing command will
be really time of executing PREPARE command. Second, PREPARE can accept
string parametr like EXECUTE command. But all is inpossible now.
Can you help me other possibilities?
Thank you
Pavel Stehule