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. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
