From: | Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | slow SP with temporary tables, any idea of solution? |
Date: | 2005-02-10 06:33:53 |
Message-ID: | Pine.LNX.4.44.0502100700530.9430-100000@kix.fsv.cvut.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Pitt | 2005-02-10 09:41:32 | Re: libpq API incompatibility between 7.4 and 8.0 |
Previous Message | Michael Fuhr | 2005-02-10 06:16:19 | Re: [SQL] Function .. AS..? |