Re: [GENERAL] Stored Procedure performance / elegance question
Karen Hill wrote: x-no-archive:yes Hello. I have a stored procedure which returns a setof record. The function takes a few arguments, and if a couple of specific input values are null, it is required that the stored procedure perform different actions. I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. Karen, My particular opinion on this is to only use execute if you need it. If you don't need it don't use it. A example where you would need execute is if you wanted to create a new user from inside a function with a passed in username. Another example where you would need EXECUTE is if you are working with temp tables in a function. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Stored Procedure performance / elegance question
x-no-archive:yes Hello. I have a stored procedure which returns a setof record. The function takes a few arguments, and if a couple of specific input values are null, it is required that the stored procedure perform different actions. I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. Which is better? fooA or fooB? : -- this one looks less elegant but is it faster because the planner stores the query? CREATE OR REPLACE FUNCTION fooA (value date , out myval) RETURNS SETOF RECORD $$ DEFINE rec RECORD; BEGIN IF value IS NULL THEN FOR rec IN SELECT * FROM test LOOP myval := rec.x RETURN NEXT; END LOOP; ELSE FOR rec IN SELECT * FROM test WHERE mydate $1 LOOP myval := rec.x RETURN NEXT; END LOOP; RETURN; END IF; END ; $$ LANGUAGE 'plgsql'; Here is fooB: --code looks cleaner especially when there are more null values to account for. Is it slower though? CREATE OR REPLACE FUNCTION fooB(value date , out myval) RETURNS SETOF RECORD $$ DEFINE rec RECORD; str varchar; BEGIN IF value IS NULL THEN str := SELECT * FROM test; ELSE str := SELECT * FROM test WHERE mydate ' || quote_literal($1); END IF; FOR rec IN EXECUTE str LOOP myval := rec.x RETURN NEXT; END LOOP; END ; $$ LANGUAGE 'plgsql'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Procedure performance / elegance question
On 8 Sep 2006 11:57:54 -0700, Karen Hill [EMAIL PROTECTED] wrote: I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. my general rule is use static when you can, dynamic when you have to. this is a very trivial case which does not get into some of the problems with dynamic sql. however, if you are taking parameters that alter the actual structure of the query, dynamic might be appropriate. FOR rec IN SELECT * FROM test WHERE mydate $1 LOOP myval := rec.x RETURN NEXT; END LOOP; RETURN; END IF; you could of course do: FOR rec IN SELECT * FROM test WHERE $1 is null or mydate $1 loop [...] or some such. also, you will get much better performance if you pass back a refcursor from the function instead of a setof record. return next is not advisable except for very small result sets. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure performance / elegance question
Merlin Moncure wrote: On 8 Sep 2006 11:57:54 -0700, Karen Hill [EMAIL PROTECTED] wrote: I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. my general rule is use static when you can, dynamic when you have to. this is a very trivial case which does not get into some of the problems with dynamic sql. however, if you are taking parameters that alter the actual structure of the query, dynamic might be appropriate. FOR rec IN SELECT * FROM test WHERE mydate $1 LOOP myval := rec.x RETURN NEXT; END LOOP; RETURN; END IF; you could of course do: FOR rec IN SELECT * FROM test WHERE $1 is null or mydate $1 loop [...] or some such. This was a simple example. In reality, the structure of the query is altered, but there are about 4 different query possibilities in the real problem depending on which values are null or not. My question was is it worth it to use Execute and suffer possible performance issues of having the planner make a new plan every time the Execute command was run? The alternative was to enumerate all 4 possible code execution paths in the store procedure using conditionals. I assume this is faster in execution but it looks ugly from a code point of view. also, you will get much better performance if you pass back a refcursor from the function instead of a setof record. return next is not advisable except for very small result sets. Don't refcursors consume a lot of database server resources? I wish to avoid that so in practice I use LIMIT and OFFSET to control results. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Stored Procedure performance / elegance question
On 8 Sep 2006 14:39:54 -0700, Karen Hill [EMAIL PROTECTED] wrote: Merlin Moncure wrote: you could of course do: FOR rec IN SELECT * FROM test WHERE $1 is null or mydate $1 loop [...] or some such. This was a simple example. In reality, the structure of the query is altered, but there are about 4 different query possibilities in the real problem depending on which values are null or not. My question was is it worth it to use Execute and suffer possible performance issues of having the planner make a new plan every time the Execute command was run? you can extend the above to an aribtrary number of parameters: select * from t where (in_a is null or in_a a) and (in_b is null or in_b b) [...] the server is very smart at optimizing the above. however there are some cases where this is inconvenient, for example a parameter that adds a group by clause. in this case 'execute' is sometimes better. The plan generation is only relevant if the function is quick and generated frequently, or has extremely complex plans (not likely). There is an interesting recent thread in -hackers discussing plan generation you might want to check out. Generally, I don't like dynamic sql because it is more complex to write and can get very tedius, especially with large queries with lots of strings. The major time saver with plpgsql, IMO, is that your queries are first class and dont have to be run through a separate parser. Don't refcursors consume a lot of database server resources? I wish to avoid that so in practice I use LIMIT and OFFSET to control results. refcursors are almost always better so long as you remember they can only be held open for the duration of a transaction. OFFSET on the other hand is notoriously inefficient as the offsets grow large. It is fine for small browses however. However, you can just do relative querying to browse large sets which is probably the fastest way of all. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
