[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Why are stored procedures looked on so negatively?
From: Bèrto ëd Sèra <berto.d.sera () gmail ! com>
Date: 2013-07-25 8:51:07
Message-ID: CAKwGa_-5+tW0A=z6mVSfqHdm8dN6oskceQqMEk97-GQTFz_mEA () mail ! gmail ! com
[Download RAW message or body]
Hi,
>the whole design of this application is asynchronous in nature.
Then you'll be MUCH better off with SPs, from an architectural POV, as you
can basically design "building blocks" by initially just making SPs that
deliver a mock result, and have the entire development of the app server
being in dependent on the SQL development. This way none of the branches
blocks the other (provided that you can actually freeze the design).
Cheers
Bèrto
On 25 July 2013 09:44, Some Developer <someukdeveloper@gmail.com> wrote:
> On 25/07/13 08:14, Vincenzo Romano wrote:
>
>> 2013/7/25 Luca Ferrari <fluca1978@infinito.it>:
>>
>>> On Thu, Jul 25, 2013 at 2:57 AM, Some Developer
>>> <someukdeveloper@gmail.com> wrote:
>>>
>>>> The added advantage of removing load from the app servers so they can
>>>> actually deal with serving the app is a bonus.
>>>>
>>>
>>> Uhm...I don't know what application you are developing, but I don't
>>> buy your explaination.
>>> While it is true that you are moving CPU cycles from the application
>>> server to the database server, you will probably end with the
>>> application server waiting for the database to acknowledge (and
>>> therefore not serving requests) and usually the computation is not
>>> that heavy for an online transaction (it would be better to do it as
>>> batch if that is really heavy). Therefore this is not an advantage for
>>> me.
>>> Again, the only reason to use database facilities (like stored
>>> procedures) is to arm the database so that even a different
>>> application/connection/user will interact following as much business
>>> rules as possible.
>>>
>>> Moreover, please also note that one reason developers tend to avoid
>>> database facilities is that they are using some kind of
>>> stack/orm/automagical library that does not allow the usage of deep
>>> features in sake of portability.
>>>
>>>
>>>
>>>
>>>> I'm not planning on creating a complex application in the database in
>>>> its
>>>> own right, just augmenting what is already available with a few time
>>>> savers
>>>> and (a couple of) speed optimisations for commonly carried out tasks.
>>>>
>>>>
>>> I don't understand the "time saving" argument: you have to implement
>>> the logic either in the application or the database, so let's say the
>>> time of the implementation is the same. The only advantage of the
>>> database is the code reuse. But take into account that there are
>>> drawbacks, like debugging that is not always so simple.
>>>
>>> Luca
>>>
>>
>> I could be wrong, but the main advantage you gain by using stored
>> procedures is what Luca says: unique data access interface.
>> Just that.
>> I don't think you'll save a single CPU cycle by moving logic from
>> "application" to "DB" (or the other way around).
>> That logic need to be implemented (and run) on either part.
>> The only saving would happen if you push the logic straight to the client.
>> And keep in mind than not all PLs are the same and have the same
>> effectiveness.
>> So, for example, instead of INSERTing rows from program, you could
>> SELECT from a stored procedure which will do the INSERT possibly with
>> the very same checks you would do in the application. Only put
>> together in a single place. The stored procedure.
>>
>> Finally, I fear this is kind of "religion" war. So feel free to follow
>> any or establish your own.
>>
>> The bottom line here is: PLs are OK. It just depends on what you do and
>> how.
>>
>>
> When I was talking about improving speed I was talking about reducing load
> on the app servers by putting more of the work load on the database server.
> I know that it won't actually save CPU cycles (one of the machines has to
> do it) but it will save load on the app servers. As I said above using the
> asynchronous abilities of libpq helps keep the app servers serving requests
> whilst the database gets on with its tasks.
>
> In fact the whole design of this application is asynchronous in nature.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
[Attachment #3 (text/html)]
<div dir="ltr">Hi,<div><br></div><div>><span \
style="font-family:arial,sans-serif;font-size:12.800000190734863px">the whole design \
of this application is asynchronous in nature.</span></div><div><span \
style="font-family:arial,sans-serif;font-size:12.800000190734863px">Then you'll \
be MUCH better off with SPs, from an architectural POV, as you can basically design \
"building blocks" by initially just making SPs that deliver a mock result, \
and have the entire development of the app server being in dependent on the SQL \
development. This way none of the branches blocks the other (provided that you can \
actually freeze the design).</span></div> <div><span \
style="font-family:arial,sans-serif;font-size:12.800000190734863px"><br></span></div><div><span \
style="font-family:arial,sans-serif;font-size:12.800000190734863px">Cheers</span></div><div><span \
style="font-family:arial,sans-serif;font-size:12.800000190734863px">Bèrto</span></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On 25 July 2013 \
09:44, Some Developer <span dir="ltr"><<a href="mailto:someukdeveloper@gmail.com" \
target="_blank">someukdeveloper@gmail.com</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On 25/07/13 08:14, \
Vincenzo Romano wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"> 2013/7/25 Luca Ferrari <<a \
href="mailto:fluca1978@infinito.it" \
target="_blank">fluca1978@infinito.it</a>>:<br> <blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On Thu, Jul \
25, 2013 at 2:57 AM, Some Developer<br> <<a \
href="mailto:someukdeveloper@gmail.com" \
target="_blank">someukdeveloper@gmail.com</a>> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> The added advantage of removing load from the app servers so \
they can<br> actually deal with serving the app is a bonus.<br>
</blockquote>
<br>
Uhm...I don't know what application you are developing, but I don't<br>
buy your explaination.<br>
While it is true that you are moving CPU cycles from the application<br>
server to the database server, you will probably end with the<br>
application server waiting for the database to acknowledge (and<br>
therefore not serving requests) and usually the computation is not<br>
that heavy for an online transaction (it would be better to do it as<br>
batch if that is really heavy). Therefore this is not an advantage for<br>
me.<br>
Again, the only reason to use database facilities (like stored<br>
procedures) is to arm the database so that even a different<br>
application/connection/user will interact following as much business<br>
rules as possible.<br>
<br>
Moreover, please also note that one reason developers tend to avoid<br>
database facilities is that they are using some kind of<br>
stack/orm/automagical library that does not allow the usage of deep<br>
features in sake of portability.<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"> <br>
I'm not planning on creating a complex application in the database in its<br>
own right, just augmenting what is already available with a few time savers<br>
and (a couple of) speed optimisations for commonly carried out tasks.<br>
<br>
</blockquote>
<br>
I don't understand the "time saving" argument: you have to \
implement<br> the logic either in the application or the database, so let's say \
the<br> time of the implementation is the same. The only advantage of the<br>
database is the code reuse. But take into account that there are<br>
drawbacks, like debugging that is not always so simple.<br>
<br>
Luca<br>
</blockquote>
<br>
I could be wrong, but the main advantage you gain by using stored<br>
procedures is what Luca says: unique data access interface.<br>
Just that.<br>
I don't think you'll save a single CPU cycle by moving logic from<br>
"application" to "DB" (or the other way around).<br>
That logic need to be implemented (and run) on either part.<br>
The only saving would happen if you push the logic straight to the client.<br>
And keep in mind than not all PLs are the same and have the same effectiveness.<br>
So, for example, instead of INSERTing rows from program, you could<br>
SELECT from a stored procedure which will do the INSERT possibly with<br>
the very same checks you would do in the application. Only put<br>
together in a single place. The stored procedure.<br>
<br>
Finally, I fear this is kind of "religion" war. So feel free to follow<br>
any or establish your own.<br>
<br>
The bottom line here is: PLs are OK. It just depends on what you do and how.<br>
<br>
</blockquote>
<br></div></div>
When I was talking about improving speed I was talking about reducing load on the app \
servers by putting more of the work load on the database server. I know that it \
won't actually save CPU cycles (one of the machines has to do it) but it will \
save load on the app servers. As I said above using the asynchronous abilities of \
libpq helps keep the app servers serving requests whilst the database gets on with \
its tasks.<br>
<br>
In fact the whole design of this application is asynchronous in nature.<div \
class="HOEnZb"><div class="h5"><br> <br>
<br>
-- <br>
Sent via pgsql-general mailing list (<a href="mailto:pgsql-general@postgresql.org" \
target="_blank">pgsql-general@postgresql.org</a>)<br> To make changes to your \
subscription:<br> <a href="http://www.postgresql.org/mailpref/pgsql-general" \
target="_blank">http://www.postgresql.org/<u></u>mailpref/pgsql-general</a><br> \
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- \
<br>==============================<br><span \
style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12px;line-height:17px">If \
Pac-Man had affected us as kids, we'd all be running around in a darkened room \
munching pills and listening to repetitive music.</span> </div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic