Thread: speed of server side languages

I am new to PostgreSQL and I'm looking to port a MSSQL database. I am
currently working on the stored procedures, and I'm trying to
understand when and why I should choose SQL vs. PL/pgSQL vs. all the
others, as far as performance goes.

Is there a document that addresses when to use what, if compatibility
is not an issue? I would probably be rewriting the gist of the stored
procedures, instead of trying to port them line by line, so I don't
care how different it is from T/SQL.

My searches turned up nothing yet... I've seen the question asked a
few times, but google ate the replies (if any).

darkcity@houston.rr.com (Val Ventura) writes:
> I am new to PostgreSQL and I'm looking to port a MSSQL database. I am
> currently working on the stored procedures, and I'm trying to
> understand when and why I should choose SQL vs. PL/pgSQL vs. all the
> others, as far as performance goes.

For stuff that involves database accesses, plpgsql will almost always
win, because it's the only one we have that caches query plans.

If you are doing simple calculations, text-mashing, etc, then you might
be best off with plperl or pltcl (choose according to taste).  SQL and
plpgsql are overkill for simple arithmetic and don't have very extensive
text-manipulation capabilities.

            regards, tom lane

Val Ventura wrote:
> I am new to PostgreSQL and I'm looking to port a MSSQL database. I am
> currently working on the stored procedures, and I'm trying to
> understand when and why I should choose SQL vs. PL/pgSQL vs. all the
> others, as far as performance goes.
>
> Is there a document that addresses when to use what, if compatibility
> is not an issue? I would probably be rewriting the gist of the stored
> procedures, instead of trying to port them line by line, so I don't
> care how different it is from T/SQL.
>
> My searches turned up nothing yet... I've seen the question asked a
> few times, but google ate the replies (if any).

PL/pgSQL is probably the fastest because it gets compiled on first
execution.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:
>
> darkcity@houston.rr.com (Val Ventura) writes:
> > I am new to PostgreSQL and I'm looking to port a MSSQL database. I am
> > currently working on the stored procedures, and I'm trying to
> > understand when and why I should choose SQL vs. PL/pgSQL vs. all the
> > others, as far as performance goes.
>
> For stuff that involves database accesses, plpgsql will almost always
> win, because it's the only one we have that caches query plans.

It's the only one that does the caching automatically. PL/Tcl has
separate commands for preparing and executing plans, and the programmer
has to put all the logic into his procedure.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #