Thread: Reason(s) not to use a stored procedure
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
THANKS
am Tue, dem 22.01.2008, um 8:49:39 -0500 mailte Mag Gam folgendes: > 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? Why not use procedural languages? He told more about the reasons? With plain SQL you can't use control structures like loops, with languages like plpgsql you can map your application logic into the db-server. I can't see any reasons against such procedures. Maybe he knows only old MySQL... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote:
> am Tue, dem 22.01.2008, um 8:49:39 -0500 mailte Mag Gam folgendes:
>> 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?
>
> Why not use procedural languages? He told more about the reasons?
>
I assume that it is true for PostgreSQL as it was for Oracle that it is
less costly (in terms of computer resources) to select and filter data
at the RDBMS level with a single SQL call than at a higher level that
may involve more calls to the RDBMS. That will be even more true if
there's a network in between.
So if you can do something in SQL, that is likely to be the most
efficient way to do it (assuming optimally written code and indexes and
whatever).
But in the real world some things you might want to do may require such
contorted SQL (or may not even be possible in SQL at all) whereas using
a procedure would be simpler to write, understand (important for when it
needs to be revisited in a year's time and by someone else) and
maintain, and you might be able to make it perform better (if needed -
why bust a gut and waste money shaving a few minutes off a nightly run
when there's nobody there to see it finish anyway?).
Maybe the professor just sat in his ivory tower without a foot in the
real world, or hadn't seen it since the early 90s?
If you're doing a real-world job just do a real-world solution!
Nick.
Everything in one place. All new Windows Live!
> am Tue, dem 22.01.2008, um 8:49:39 -0500 mailte Mag Gam folgendes:
>> 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?
>
> Why not use procedural languages? He told more about the reasons?
>
I assume that it is true for PostgreSQL as it was for Oracle that it is
less costly (in terms of computer resources) to select and filter data
at the RDBMS level with a single SQL call than at a higher level that
may involve more calls to the RDBMS. That will be even more true if
there's a network in between.
So if you can do something in SQL, that is likely to be the most
efficient way to do it (assuming optimally written code and indexes and
whatever).
But in the real world some things you might want to do may require such
contorted SQL (or may not even be possible in SQL at all) whereas using
a procedure would be simpler to write, understand (important for when it
needs to be revisited in a year's time and by someone else) and
maintain, and you might be able to make it perform better (if needed -
why bust a gut and waste money shaving a few minutes off a nightly run
when there's nobody there to see it finish anyway?).
Maybe the professor just sat in his ivory tower without a foot in the
real world, or hadn't seen it since the early 90s?
If you're doing a real-world job just do a real-world solution!
Nick.
Everything in one place. All new Windows Live!
On Tue, Jan 22, 2008 at 08:49:39AM -0500, 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? putting application logic in the database is maybe what he does not like about it. not beeing able to switch databases easily might be another. but why not just ask him? -- cu
Attachment
> 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? Those who can't do, teach? Honestly... SQL can't do business logic, auditing, etc. Similarly, by keeping the data local, stored procedures eliminate the network round trips required to do complex logic which is sometimes performed by an external application. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
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.
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:"
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.
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
Probably not the stuff that your professor mentioned, but it's what's been my experience. Other experiences may vary.
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.
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:"
Never miss a thing. Make Yahoo your homepage.