Re: Replacing Cursors with Temporary Tables

To answer the question of whether calling a stored procedure adds any significant overhead, I built a test case and the short answer is that it seems that it does:

CREATE OR REPLACE FUNCTION Test1() RETURNS INTEGER AS
$BODY$
DECLARE
    temp INTEGER;
BEGIN
    FOR i IN 1..1000 LOOP
        SELECT 1 AS id INTO temp;
    END LOOP;
    RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Test2A() RETURNS SETOF INTEGER AS
$BODY$
DECLARE
BEGIN
    RETURN QUERY SELECT 1 AS id;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION Test2B() RETURNS INTEGER AS
$BODY$
DECLARE
    temp INTEGER;
BEGIN
    FOR i IN 1..1000 LOOP
        temp := Test2A();
    END LOOP;
    RETURN 1;
END;
$BODY$
LANGUAGE plpgsql;


EXPLAIN ANALYZE SELECT * FROM Test1();
"Function Scan on test1  (cost=0.00..0.26 rows=1 width=4) (actual time=6.568..6.569 rows=1 loops=1)"
"Total runtime: 6.585 ms"


EXPLAIN ANALYZE SELECT * FROM Test2B();
"Function Scan on test2b  (cost=0.00..0.26 rows=1 width=4) (actual time=29.006..29.007 rows=1 loops=1)"
"Total runtime: 29.020 ms"


So, when chasing milliseconds, don't call sub functions if it can realistically and easily be avoided. I only have one operation/algorithm broken out into another stored procedure because I call it in about 8 different places and it is 900+ lines long. While everything else could be broken out into different stored procedures to make it easier to analyze the whole set of code and probably make it easier to maintain, it does not make sense from a performance perspective. Each different logical group of actions that would be in its own stored procedure is only ever used once in the whole algorithm, so there is no good code re-use going on. Further, since the containing stored procedure gets called by itself hundreds or even thousands of times per second on a production system, the nested calls to individual sub-stored procedures would just add extra overhead for no real gain. And, from these tests, it would be significant overhead.






--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero