 | | From: | - | | Subject: | When to use procedures? | | Date: | Sun, 23 Jan 2005 16:11:57 +0800 |
|
|
 | Does anyone knows when should a procedure be used rather than code the
statement into the application itself?
Like for instance an application needs to know whether a user exists so
the code will be SELECT * FROM table WHERE user.id = id; And if i were
to create a procedure it will be CREATE PROCEDURE is_user_exists .
|
|
| | From: | DA Morgan | | Subject: | Re: When to use procedures? | | Date: | Sun, 23 Jan 2005 10:52:03 -0800 |
|
|
| - wrote:
> Does anyone knows when should a procedure be used rather than code the
> statement into the application itself?
>
> Like for instance an application needs to know whether a user exists so
> the code will be SELECT * FROM table WHERE user.id = id; And if i were
> to create a procedure it will be CREATE PROCEDURE is_user_exists .
When it enhances scalability, performance, and data integrity.
Most of the time code belongs in the back end, written by people that
know how to write code for the back end. Because it is nearly
impossible, in the real world, to debug and tune code written elsewhere.
For example I think the SQL statement you have above is abyssmal if the
point is just to determine whether a user exists in the system. You are
likely returning hundreds of bytes when a single byte would suffice.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
|
|
| | From: | --CELKO-- | | Subject: | Re: When to use procedures? | | Date: | 23 Jan 2005 11:44:20 -0800 |
|
|
| >> Does anyone knows when should a procedure be used rather than coding
the
statement into the application itself? <<
Almost always. Ideally, the shop ought to have a DBA/data manager/SQL
guy who cranks out stored procedures with all the security and access
control for you. This means that you have ONE AND ONLY ONE set of
rules for the data. If you do it in the front end, how do you find out
that Fred thought policy was "age > 18" and Sam thought it was "age >=
18" for the business rules? Want to find this code in hundreds of
assorted front end programs?
The SQL guy writes better SQL than the VB/Java/C++/"app language du
jour" programmers. If the schema changes, he can change the body of
the procedures and the front end never knows anything happened.
This is the whole point of a tiered architecture. Specialization, loose
coupling and high cohesion. Have you ever had a basic software
engineering course?
|
|
| | From: | Troels Arvin | | Subject: | Re: When to use procedures? | | Date: | Sun, 23 Jan 2005 22:09:57 +0100 |
|
|
| On Sun, 23 Jan 2005 11:44:20 -0800, --CELKO-- wrote:
>> Does anyone knows when should a procedure be used rather than coding
>> the statement into the application itself?
>
> Almost always.
They you won't like
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
--
Greetings from Troels Arvin, Copenhagen, Denmark
|
|
| | From: | DA Morgan | | Subject: | Re: When to use procedures? | | Date: | Sun, 23 Jan 2005 15:39:01 -0800 |
|
|
| Troels Arvin wrote:
> On Sun, 23 Jan 2005 11:44:20 -0800, --CELKO-- wrote:
>
>
>>>Does anyone knows when should a procedure be used rather than coding
>>>the statement into the application itself?
>>
>>Almost always.
>
>
> They you won't like
> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
One of the things that makes the internet what it is today is the
lack of filtering for the quality of the content. Bouma's blog is
not worth the electrons from which it was transmitted across the
net.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
|
|
| | From: | Troels Arvin | | Subject: | Re: When to use procedures? | | Date: | Mon, 24 Jan 2005 09:31:33 +0100 |
|
|
| On Sun, 23 Jan 2005 15:39:01 -0800, DA Morgan wrote:
> Bouma's blog is not worth the electrons from which it was transmitted
> across the net.
While I don't agree on Bouma's total rejection of stored procedures, I
think that his weblog - in general - is one of the best tech-blogs out
there. This was just to say: The link was not necessarily an endorsement,
but a link to a discussion.
--
Greetings from Troels Arvin, Copenhagen, Denmark
|
|
| | From: | DA Morgan | | Subject: | Re: When to use procedures? | | Date: | Mon, 24 Jan 2005 01:10:49 -0800 |
|
|
| Troels Arvin wrote:
> On Sun, 23 Jan 2005 15:39:01 -0800, DA Morgan wrote:
>
>
>>Bouma's blog is not worth the electrons from which it was transmitted
>>across the net.
>
>
> While I don't agree on Bouma's total rejection of stored procedures, I
> think that his weblog - in general - is one of the best tech-blogs out
> there. This was just to say: The link was not necessarily an endorsement,
> but a link to a discussion.
How can someone that doesn't even understand or appreciate the hard-won
lessons of the largest and most successful web application projects be
"one of the best?"
He is flogging a proposal that has been proven time and again to be the
underlying cause of each of the following:
1. Data integrity compromised by persons accessing the data that have
not used "the" application.
2. Security compromised by persons using and not using "the" application
3. Poor performance due to the fact that the statements can not be tuned
on-the-fly as data quantity and skew change over time
4. An almost universal lack of real understanding of those factors that
maximize performance and scalability in databases by those that don't
work in them regularly.
It isn't one of the best ... it is just an opinion ... and most likely
the opinion of someone with little appreciation of the mess he is likely
leaving in his wake.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
|
|
| | From: | - | | Subject: | Re: When to use procedures? | | Date: | Mon, 24 Jan 2005 16:21:57 +0800 |
|
|
| --CELKO-- wrote:
>
> This is the whole point of a tiered architecture. Specialization, loose
> coupling and high cohesion. Have you ever had a basic software
> engineering course?
:-) not formally...
|
|
