Skip site navigation (1) Skip section navigation (2)

Re: Efficiency of stored procedure vs large join

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Malcolm Hutty <msah-postgres(at)hutty(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Efficiency of stored procedure vs large join
Date: 2002-11-15 17:29:03
Message-ID: 20021115172903.GB3260@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, Nov 08, 2002 at 17:56:33 +0000,
  Malcolm Hutty <msah-postgres(at)hutty(dot)com> wrote:
> 
> On the one hand (with pure SQL), I'm sending a much larger length of
> query text across the network from PHP to the database, and I might be
> constructing a very large joined resultset before it gets trimmed down
> (I don't know how the optimisation works). On the other hand (with a
> stored procedure) I'm performing several queries, taking the
> resultsets out of the Postgres optimiser and iteritively querying each
> result from PL/pgSQL. Is that a bad thing? Or is it no worse than the
> joined subselects in the big query? Does it make a performance
> difference at all?

What about using views? There you could use the sql method, but not
have to pass the extra sql around. This also gives you another option.
If the application authenticates to the database as the end user,
you can enforce the security in the database instead of in the
application (by giving normal users only access to the views).

In response to

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-11-15 17:53:41
Subject: Re: using functions to generate custom error messages
Previous:From: Bruno Wolff IIIDate: 2002-11-15 17:22:23
Subject: Re: Return serial from insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group