Обсуждение: 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';
On 8 Sep 2006 11:57:54 -0700, Karen Hill <karen_hill22@yahoo.com> 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
"Merlin Moncure" wrote: > On 8 Sep 2006 11:57:54 -0700, Karen Hill <karen_hill22@yahoo.com> 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.
On 8 Sep 2006 14:39:54 -0700, Karen Hill <karen_hill22@yahoo.com> 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
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