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 RETURN NEXT _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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster