On Wed, Mar 9, 2011 at 3:18 AM, Richard Quadling <rquadling@xxxxxxxxx>wrote: > On 7 March 2011 23:37, Nathan Nobbe <quickshiftin@xxxxxxxxx> wrote: > > 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 > > All my new projects are using stored procedures and views. No direct > access to the tables. This means that if I use PHP or Excel VBA or > Delphi or C or any other language, every single request will be > processed in an identical way. No variation (seen/unseen, > known/unknown). > > Some unknown nuance related to my coding/knowledge, or an unseen side > effect/bug ... more or less disappear. Making data storage and > retrieval a function of the data store and not something to be > "constructed" in PHP (with all the hassles of unicode/escaping/etc.) > and then re-constructed/coded in Excel VBA, Crystal Reports, etc. > > I don't use an ORM as I don't have the same ability to do that in > Delphi/C/Excel. > > Having to work in multiple languages, all talking to the same DB, it > seemed sensible to create a wall between data consumer and data > provider. > > Is this for everyone? Probably not, but it helps me. This makes a lot of sense and actually brings up a point I've not yet considered or heard mentioned. I can think back of a time I was working on an app which had PHP & Java accessing the database. There was a lot of confusion about which language accessed the database, and for what, and I can say there surely was duplicated queries between the 2 app layers. Thanks for this insight Richard. -nathan
