August 4, 2008 at 9:34 am
Stewart Joslyn (7/30/2008)
For trivial inserts and deletes, there can be an argument for avoiding stored procedures but, for anything more complex, there are clear performance gains. The biggest bottleneck is likely to be network bandwidth - you don't want to be downloading volumes of data just so that you can do complex joins less efficiently in your app than you could on the database server (and perhaps return a single value). One has to consider the whole system holistically although this can only be done really well by people who are multi-skilled across dba and developer roles - something of an artificial distinction anyway in my view. Neither end of the spectrum can perform well without an understanding and appreciation of the other.
also, the difference in traffic to the database server when using sps and and sending the whole set of statements everytime across the network will be huge
August 4, 2008 at 10:08 am
I come back from vacation and this thread is still going strong!
I have something to say about the "scalability" argument that's been raised by those who argue against stored procs. I find this ironic because this whole thing started with a blast about stored procs being used only because people heard the words "best practice" and "mindlessly" followed. But some previous posts have betrayed ignorance of the performance issues involved in efficiently querying the database, so I can only assume that these people have read "procs can't scale" and took it as gospel.
Minimizing physical I/O is a key to optimizing db performance. No matter how much business logic is embedded in web objects, eventually you have to retrieve the data. A transaction may be entered only once, but it may be retrieved by various searches a hundred times. If the select statements used in these searches are inefficient, that is going to kill scalability. If network traffic increases because the conversation between the objects and the db are too chatty, that is going to hurt scalability. If a dozen SELECTS (or inserts, updates, deletes) are generated when one would do, that's going to hurt scalability. Stored procedures play an essential part in architectures that address these concerns.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 4, 2008 at 11:38 am
This was a great debate, and I guess still is.
Dex, well put and that's what I'd tend to agree with. Even if you don't have a DBA, learn to write stored procedures get help, and use them as the interface from your DAL.
August 4, 2008 at 11:58 am
Charles Kincaid (8/4/2008)
Having to rewrite code because you can read what it does? What are your writing in? PERL? :w00t: That's about the only one-way language I know of. You get over Joe Celko's nine things limit in a hurry.
You can read Perl, but it takes some work. If you want a true write-only language, check out APL!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 4, 2008 at 2:28 pm
Wayne West (8/4/2008)
Charles Kincaid (8/4/2008)
Having to rewrite code because you can read what it does? What are your writing in? PERL? :w00t: That's about the only one-way language I know of. You get over Joe Celko's nine things limit in a hurry.You can read Perl, but it takes some work. If you want a true write-only language, check out APL!
the granddaddy of all write-only languages has to be Malbolge.
If you can read a Malbolge program, let alone write one, you are a god.
---------------------------------------
elsasoft.org
August 4, 2008 at 5:44 pm
Huh? APL, Malbolge... I'm 51 and can barely recognize APL. These are languages developed before dirt was invented. What a treat hanging out with people that can remember coding in that stuff. I tip my hat to you guys!
--Paul Hunter
August 4, 2008 at 6:01 pm
Malbolge isn't an old language. It's esoteric though. It's designed to be difficult to write. 🙂
---------------------------------------
elsasoft.org
August 4, 2008 at 10:21 pm
Started college with:
semester 1 - LISP
semester 2 - APl
semester 3 - Fortran and assembly
semester 4 - C
Those were the days!
August 4, 2008 at 10:45 pm
my first programming language was Applesoft on the Apple II+
:blush:
---------------------------------------
elsasoft.org
August 4, 2008 at 11:11 pm
My first program was plugging wires into a "program board" for unit record equipment way back when there really were things like bit-buckets. Heh... might be dating myself there, a bit... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2008 at 1:30 am
Algol, Fortran, 8086 assembler !
August 5, 2008 at 4:29 am
I do not see the point with this ( in my opinion ) biased, unreflected comment that seems to come from a person that writes from a client developer or production maintenance ( dba ? )perspective.
I strongly agree that stored procedures should be used carefully from triggers.
But, using them in the business layer, I would say ( having used them for 15+ years in oracle/sybase/ms sql server ), I would say they are ( again my opinion ) often the best cost/effective solution. I do not have many bad experiences with this. Most of the problems I have experienced with bad performance did not result from bad coding. It resulted from lacking dba practices or badly set up database solutions.
The alternatives tp stored procedures are coding the business logic above the db, in a layer between the db and the client, or directly in the client. In general, I would strongly advise Coding the business layer directly on the client.
August 7, 2008 at 2:39 pm
jezemine (8/4/2008)
my first programming language was Applesoft on the Apple II+:blush:
Nothing wrong with that. Apple has always been a pioneer in the technology industry. Steve Job's NEXT Computing was also pioneers back in the day when it came to object-relational mapping (ORM).
October 23, 2009 at 9:44 pm
Glad I came upon this post. I have long suspected that there was someone else who looked at this as I do. I've been programatically constructing database queries (client/server, mainframe) for many years through whatever "dynamic" method was available as I find a higher level language (currently C#) has much better inheritence (for objects that play a role in assembling the SQL to perform) - than having to write a stored procedure for every little thing.
October 26, 2009 at 9:53 pm
DiverKas (7/31/2008)
DBA - Database Administrator. Nothing in that job description even comes close to what an architect does.
Look at the article you cited again... the very first statement in that article starts out with...
A database administrator (DBA) is a person responsible for the [font="Arial Black"]design[/font]...
The first paragraph goes on to say that the role of a DBA is "closely related to the Database Analyst, Database Modeler, Programmer Analyst, and Systems Manager". In fact, in many companies, the DBA's aren't "just" systems DBA's... they're "hybrids" that can and do fill any and all of those roles and they will many times do a better job in any of the given roles because they have a better understanding of all the roles, the system, and the data than just those holding one role.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 136 through 150 (of 160 total)
You must be logged in to reply to this topic. Login to reply