Last night, I was doing some amateurish benchmarking and found that,
contrary to my (admittedly uninformed) expectation, sql functions seem
*slower* than plsql functions. Even for very simple things, like
'SELECT CASE WHEN $1 RETURN $1 ELSE $2 END'
were slower than the plsql
begin
if $1 then
return $1;
else
return $2;
end if;
end;
by about 15%.
However, my benchmarking was the type usually dreaded: a single person,
playing around in a scripting language, running the test one a time
seqeuentially, and just timing the results.
Is there any real data on this?
I also tried plperl v plpgsql, and found that, probably not surprisingly,
there was about a 15% advantage to plpgsql. Of course, many things can be
written much simpler in perl (such as string handling functions). Even so,
though, a find-the-first-letter-of-all-significant-words function written
about 1.5 years ago in plpgsql (a pretty awful, nested, letter-by-letter
parser) was only about twice as slow as the perl
split() replacement. Looks like our little plpgsql is quite a speed demon.
(as always, IANAPB [ I am not a professional benchmarker ], YMMV)
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington