SEO

In addition to all of these good points, there is also an issue of scalability. When the application
logic sits in the database, it limits your options for load balancing across potentially large
numbers of database servers and application servers. In a typical enterprise environment there
are all kinds of legacy datastores that all have to be accessible, and you really don't want to
port your code base individually to all of them. Who was it who said that computer science is
the business where we solve every problem with one more layer of indirection?

If you're in that sort of environment, those considerations tend to reduce overall complexity;
if you're not, then they tend to increase it.

----- Original Message ----
From: Ilan Volow <listboy@clarux.com>
To: pgsql-novice@postgresql.org
Sent: Tuesday, January 22, 2008 9:19:18 AM
Subject: Re: [NOVICE] Reason(s) not to use a stored procedure



IMHO, like most everything dealing with any kind of software development, it's an engineering tradeoff.


When I write plpgsql stored procedures, I am trading:


- Easy portability between databases (to the extent that SQL is portable between databases)


- Niceties like IDE's with useful IDE stuff like being able to search through code (I had to write a custom application to do this with plpgsql).


- Ease of SCM (I am having to write a custom application to do this with plpgsql)



- Ease of debugging (via using a real debugger, that I have to do absolutely no work at all to install and that I don't have to buy)


- Maintainability. I personally find maintaining large bunches of plpgsql to be a major PITA. Code can easily get lost if you restore a dump in the wrong way.


- A good, clean separation between application layers and storage layers


for:


- Using loops and variables so I don't have to write and debug a cluster**** of SQL spaghetti.


- Avoiding getting involved with introducing a separate middle-ware server.


- Not having to change the Objective-C code for my rich-client, recompile it, and redeploy it to get the change in behavior I want. Updates are instantaneous and centralized.




Probably not the stuff that your professor mentioned, but it's what's been my experience. Other experiences may vary.

On Jan 22, 2008, at 8:49 AM, Mag Gam wrote:

I was in an academic meeting where a professor was preaching the reasons why not to use a stored procedure. He advised to always use SQL instead of a procedural language. Can someone please shed some light on this?

THANKS



Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"















__________________________________________________ __________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i...Dypao8Wcj9tAcJ

the_andrew_winkler@yahoo.com (Andrew Winkler) writes:
> In addition to all of these good points, there is also an issue of
> scalability. When the application logic sits in the database, it
> limits your options for load balancing across potentially large
> numbers of database servers and application servers.* In a typical
> enterprise environment there are all kinds of legacy datastores that
> all have to be accessible, and you really don't want to port your
> code base individually to all of them. Who was it who said that
> computer science is the business where we solve every problem with
> one more layer of indirection? If you're in that sort of
> environment, those considerations tend to reduce overall complexity;
> if you're not, then they tend to increase it.

Scalability is actually a place where I would expect stored procedures
to come out smelling pretty sweet.

After all, if your application logic is forced to run outside the
DBMS, then you have several costs that are not imposed if running
logic inside the DBMS:

- Data marshalling
- Data transmission

For example, let us consider the case where a particular application
logic requires examining the contents of 8 columns from 32 tuples,
those 32 tuples being the entries that match a particular order
number.

A stored function might invoke this like:

perform sp_process_order(1321441);

and return the ID number of the tuple that was generated. It read the
32 tuples, but had no need to return anything other than the ID value.

In contrast, the "non-SP" methodology requires that all of the same
data be marshalled and read across the network.

In this case, the "no SPs" camp requires 256 times as much work out of
the DBMS in terms of data marshalling and transmission.

Marshalling/transmission aren't the ONLY work done, but they are by no
means immaterial.

Furthermore, the "load balancing across database servers" seems an
awful lot like a mirage, to me. For that to generally work requires
that you have either:

a) Solved the multimaster replication problem, which smart people
have explained is extremely expensive to do correctly, or

b) Decided to "solve" the multimaster replication problem via
some kludge that will work until it stops working, and eats
your data, or

c) Have data that is already sufficiently corrupt and non-trustable
that you don't *care* that your replication system doesn't
really work.

I have seen vanishingly few cases where "simplification" didn't
involve having *one* DB server rather than 4.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/advocacy.html
Roses are red
Violets are blue
Some poems rhyme
But this one doesn't.