On 3 March 2011 18:30, Nathan Nobbe <quickshif...@gmail.com> 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

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

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

Reply via email to