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. 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. 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. And, as I create apps with security levels, I can implement all of that on the server. No way to get around the security as there is no access to the tables directly. Portability is certainly an issue. But I don't use any other DB system. So, for me, it isn't an issue. BUT. Due to the abstraction layer PHP, sees a set of objects related to physical entities and has behaviour to manipulate the entities, re-engineering the best solution for Oracle or mysql would be an appropriate exercise. One size does NOT fit all. Never has. Never will. Having said all of that. One of the departments bought an app a few years ago that provided a set of stored procedures to amend the data, but didn't restrict access to the live data. As a consequence there were bugs where the app has failed to implement the same code as the stored procedures. That took us a while to work out. We just assumed that the stored procedures were in use. -- 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
