On Fri, Mar 4, 2011 at 7:29 AM, Richard Quadling <rquadling@xxxxxxxxx>wrote: > On 3 March 2011 18:30, Nathan Nobbe <quickshiftin@xxxxxxxxx> 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, > > > > -nathan > > > > Hello Nathan. > > I develop for and on Windows using IIS7 and MS SQL Server 7/2000/2005/2008. > i love how you preface many of your responses like this. > Almost exclusively I use prepared statements to talk to stored > procedures and views. I use triggers and constraints to enforce RDI. I > _do_ have the occasional hacky script which includes SQL, but only > 'cause I was being lazy and wanted to do a one off change. > this sounds as if you're doing next to 0 query generation from php, is that correct? At a fundamental level, my PHP code isn't concerning itself with any > physical data structures. As much as possible my PHP code treats the > sql data source as a processor ready to supply data in a standardized > form (even hierarchical) and to accept data for storage (again > hierarchical). My PHP code knows next to nothing about the table > structure (why should it - it isn't a database). It does know that a > "customer" object has a set of properties and a set of "instruments of > change" which are passed to the SQL server to effect the data and are > cached locally. PHP deals in objects/entities. Stored procedures > provide the translation between the OOP and the RDBMS. This provides a > nice clean interface between PHP and the data. The stored procedures > and views are all pre-compiled - with their internal usage statistics > to make best use of available indices and are tuned to the actual data > rather than something I thought I knew about the data usage when I > designed the DB. So speed is much more significant. Having every > single SQL statement compiled from scratch for a 1 off use would seem > wasteful. > > Multiple result sets are completely fine (at least for MS SQL Server) > - Admittedly you have to currently process the result sets in > sequential order (i.e. set 1 before moving to set 2 - can't move back > to set 1). But that is something quite easy to work with when you know > the limitation. And is the easiest way to get hierarchical data into > PHP for me. I get all the relevant data in 1 hit rather than getting > the data with potential mis-matching values due to the realtime > multi-user environment. > i understand the ability to consume multiple result sets is available. the issue i think would be raised with an orm would be getting result sets with mixed columns from multiple tables. im not sure how capable an orm like propel (for example) is of mapping those results back to objects. at a glance of google results it appears the result is just an array which sacrifices the abstraction the orm aims to provide. -nathan
