On Thu, Mar 3, 2011 at 12:59 PM, Paul M Foster <paulf@xxxxxxxxxxxxxxxxx>wrote: > On Thu, Mar 03, 2011 at 11:30:49AM -0700, Nathan Nobbe wrote: > > > Hey gang, > > > > (Yes Tedd, I like your style, when it pertains to how you address the > list > > :)) > > > > I have a new curiosity that's arisen as a result of a new contract I'm > > working on, I'd like to bounce around some thoughts off the list and see > > what you folks think if interested. > > > > The topic at hand is stored procedures. Frankly, I've hardly ever even > seen > > these in use, and what I'm trying to figure out are good rules of thumb > as > > to where / when / how they are best used in application development. > > > > Also, bear in mind that personally I tend to favor OO paradigms for > > application development so would prefer feedback that incorporates that > > tendency. > > > > Initial thoughts are > > > > Bad: > > . Not well suited for ORM, particularly procedures which return multiple > > result sets consisting of columns from multiple tables > > . Greater potential for duplicated logic, I think this comes down to a > well > > defined set of rules for any given application, read: convention required > > for success > > . Scripting languages are vendor specific, and likely most application > > developers have a limited understanding thereof > > > > Good: > > . Better performance > > . <Fill in blank on convincing bullets here> > > > > I've also done some reading on MSSQL vs. MySQL and found that the former > > offers much more features. I've also read that most databases only see > > roughly 40% of the feature sets being used for typical applications in > the > > wild, and would agree from personal experience it is accurate. > > > > >>From my standpoint MySQL is popular because the features it offers are > the > > features folks are really looking, one of those 80/20 things... > > > > I stumbled into this link on a google search, it's from '04 but looks to > be > > relevant to this day > > > > > http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html > > > > Your thoughts appreciated, > > I've done a lot of work with databases, and never used stored > procedures. I'm not quite sure why anyone would graft a bunch of > computational gear on top of a database engine designed to store and > retrieve data. Let the engine do what it does best. Leave PHP or C to > Python to do the other stuff. > Paul, I tend to agree with you here. The one exception that makes sense to me is super huge data sets. I used to ride the bus w/ a guy who was scripting Oracle for a big insurance company. > Another point: I once had a boss tell me that programmers were typically > weak on database. I suspect they would gain some expertise if they were > forced to consider database architecture and SQL in writing apps. Stored > procedures would tend to make the database more opaque. > I think there is merit to this. But by the time you've got some triggers and views going I'm not so sure the procedures are worth it. It seems to me like things are starting to get heavy on the db side of the fence at that point, but of course that opinion is arbitrary. A couple companies back we were pretty heavy into cascading deletes and triggers, and I gained an appreciation for them in terms of application development. It seems that part of the impetus for stored procedures is the horror > that DBAs have of random programmers issuing hack-laden SQL to their > precious databases. My question: you've got backups, right? And any bad > SQL should be taken care of before an app goes out the door. It's the > programmer's responsibility to ensure that nothing he does can hack up > the database. That includes parameterizing queries and vetting user data > properly. > I think it's nice to have a full-fledged DBA if the biz can afford one. I tend to also worry about the other way round on this one, namely if the DBA folks are getting their hands into application logic albiet stored procedures. That sort of arrangement is nothing short of disturbing to me. Anyway, just some thoughts. Also, please consider PostgreSQL in addition > to MySQL and MSSQL. > I've often discussed Postgre, but end up considering it for specific needs more than anything. Best use I ever had for it was a backend for Bind with a php front-end http://antdns.sourceforge.net/ Needed a little hacking to get it going, but it was pretty sweet! We also chose Postgre for a data warehouse at one company with a large dataset. I had nothing to do w/ that one though ;) -nathan
