Обсуждение: Any risks in using FUNCTIONs (stored procedures) instead of raw sql queries?

I am looking to convert all my database access code into stored
procedures in PL/PGSQL.

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.

To me, performance is critical as a heavy web access is critical. This
has to scale too. I use PHP currently to do the following:

1. Create a connection
2. Take the submitted form info and check if it already exists in db (SQL1)
3. If exists, then update db with submitted info and return new values (SQL 2)
4. If not exists, then insert new record (SQL 2.1)
5. If insert/update went well, we get the new values otherwise an
"ERROR" string depending on what the error was
6. Close the connection

All this works very fast for now, and it's in a separate class in PHP
so it's okay in terms of maintenance.

But a DBA told me that it will be much better to do all of these
things in a stored procedure as it may bring some performance
benefits. He's an oracle DBA so I am not sure if the same applies to
PG? Will a "function" that takes input values with 15 column data
fields including two TEXT fields and then outputs perhaps an array of
values to a PHP program be faster than 2-3 separate SQL queries issues
from PHP?

Thanks for any input. Or please point me online to any resource that
discusses this kind of info. I could not find any.

PK

On Wed, 2008-11-19 at 02:18 +0800, Phoenix Kiula wrote:
> I am looking to convert all my database access code into stored
> procedures in PL/PGSQL.

> But a DBA told me that it will be much better to do all of these
> things in a stored procedure as it may bring some performance
> benefits. He's an oracle DBA so I am not sure if the same applies to
> PG? Will a "function" that takes input values with 15 column data
> fields including two TEXT fields and then outputs perhaps an array of
> values to a PHP program be faster than 2-3 separate SQL queries issues
> from PHP?
>

Generally speaking, yes. A stored procedure will be faster, if nothing
else you don't have to deal with TCP delay. You will also want to make
sure you are utilizing a connection pool.

For database design, especially when dealing with web developers I find
it is always good to have them review:

http://www-01.ibm.com/support/docview.wss?uid=nas191f301ccd7abae2f862565c2007cf178

Joshua D. Drake

> Thanks for any input. Or please point me online to any resource that
> discusses this kind of info. I could not find any.
>
> PK
>
--

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.

If your web servers have non-trivial latency to the database servers or
if they do vast numbers of tiny individual queries, it might be
extremely worthwhile wrapping them up in appropriate stored procedures
(set-returning where appropriate), especially if that also helps the
logical organisation of the code.

> 1. Create a connection
> 2. Take the submitted form info and check if it already exists in db (SQL1)
> 3. If exists, then update db with submitted info and return new values (SQL 2)
> 4. If not exists, then insert new record (SQL 2.1)

2, 3 and 4 are perfect candidates for being bundled into a PL/PgSQL
stored procedure.

You can probably get away with ditching step 2 entirely. What you really
want is "Ensure that the form info is in the database and up to date",
ie an UPSERT / REPLACE. There's a fairly convenient way to do that:



-- If the form is already there, update it.
-- If it's not there, this is a no-op.
UPDATE table SET val1 = blah, val2 = blah, etc
WHERE form_identifier = whatever;

-- Otherwise, insert it. If it's already there, this
-- only costs us an index lookup.
INSERT INTO table (form_identifier, val1, val2, etc)
SELECT whatever, blah, blah2
WHERE NOT EXISTS (SELECT 1 FROM table WHERE form_identifer = whatever)



You can of course conveniently bundle this into a PL/PgSQL stored
procedure. If you like you can also use GET DIAGNOSTICS to see whether
the UPDATE did anything and skip the INSERT if it did (allowing you to
structure the INSERT the usual way instead of INSERT ... SELECT ... WHERE).

> 5. If insert/update went well, we get the new values otherwise an
> "ERROR" string depending on what the error was

This should probably be handled by letting any error emitted by the
INSERT or UPDATE propagate out of the PL/PgSQL stored procedure and be
caught by the application.

> But a DBA told me that it will be much better to do all of these
> things in a stored procedure as it may bring some performance
> benefits.

I would tend to agree. It'll probably also be cleaner, and as a bonus
once PostgreSQL supports the UPSERT / REPLACE operation you can probably
just switch to using that instead of your stored procedure.

> He's an oracle DBA so I am not sure if the same applies to
> PG? Will a "function" that takes input values with 15 column data
> fields including two TEXT fields and then outputs perhaps an array of
> values to a PHP program be faster than 2-3 separate SQL queries issues
> from PHP?

Quite possibly. There's a cost to running a PL/PgSQL stored procedure,
but it's not huge. The best way to find out is to test it, since it
sounds like your code is well enough structured to make that fairly fuss
free.

--
Craig Ringer

On Wed, Nov 19, 2008 at 06:40:04PM +0900, Craig Ringer wrote:
> What you really
> want is "Ensure that the form info is in the database and up to date",
> ie an UPSERT / REPLACE. There's a fairly convenient way to do that:
>
> -- If the form is already there, update it.
> -- If it's not there, this is a no-op.
> UPDATE table SET val1 = blah, val2 = blah, etc
> WHERE form_identifier = whatever;
>
> -- Otherwise, insert it. If it's already there, this
> -- only costs us an index lookup.
> INSERT INTO table (form_identifier, val1, val2, etc)
> SELECT whatever, blah, blah2
> WHERE NOT EXISTS (SELECT 1 FROM table WHERE form_identifer = whatever)
>
> You can of course conveniently bundle this into a PL/PgSQL stored
> procedure. If you like you can also use GET DIAGNOSTICS to see whether
> the UPDATE did anything and skip the INSERT if it did (allowing you to
> structure the INSERT the usual way instead of INSERT ... SELECT ... WHERE).

There's a magic pl/pgsql variable called "FOUND" that helps here:

  UPDATE tbl SET x = 1 WHERE id = 10;
  IF NOT FOUND THEN
    INSERT INTO tbl (id,x) VALUES (10,1);
  END IF;

would be the unparameterized version.


  Sam

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.

Joshua D. Drake

--

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