Re: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: jd(at)commandprompt(dot)com
Cc: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?
Date: 2008-11-20 05:08:31
Message-ID: 4924F0CF.6090701@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Joshua D. Drake wrote:
> On Wed, 2008-11-19 at 18:40 +0900, Craig Ringer wrote:
>> Phoenix Kiula wrote:
>>
>>> I have googled but it looks like there's a whole variety of
>>> information from 2003 (when PG must have been quite different) until
>>> now--some people find stored functions slow for web based apps, others
>>> find it is worth the maintenance.
>> If your web servers are very close in network terms to your database
>> server, issue mostly non-trivial queries, and are on a low latency link,
>> it probably doesn't matter *that* much.
>
> For one query no... for a dynamic website that uses 100 - 200 queries to
> draw a page?
>
> ....
>
> 15ms * 200, 3000ms = 3 secs * 2 (both ways) = 6 seconds.

Exactly - that's why I mentioned that it probably didn't matter if the
app issued mostly non-trivial queries. It's unlikely that a couple of
hundred queries will mostly be non-trivial; most will be simple SELECT
something FROM atable WHERE key = value; .

It depends on the app. Some apps don't _need_ a couple of hundred
queries to obtain the data for a page. If you're only issuing five or
ten queries, the latency isn't going to be significant.

--
Craig Ringer

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David 2008-11-20 05:22:24 start/stop error message
Previous Message Joshua D. Drake 2008-11-20 05:04:02 Re: [SQL] date range query help