Hi Team
I very much agree with the points shared by Florin (esp. the two rules). But unfortunately, these rules are not standards and that is where the real problem lies. The injudicious use of SPs leads to un-manageable code which is rarely portable (real life situations J which are too common to be overlooked). Hoping that everyone will stick to and follow the best practices while creating code. Regards NetEmp On Sat, Mar 5, 2011 at 6:01 PM, Florin Jurcovici <florin.jurcov...@gmail.com > wrote: > Hi. > > I would always recommend stored procedures, as long as there are a > very few rules obeyed: > - keep them simple - they should mostly implement just CRUD operations > plus application-specific searches, and should not encapsulate any > other logic > - use just portable SQL (well, as long as this is possible) > > My reasoning for using stored procedures and sticking to these rules > is the following: > - no matter what you do, especially with PHP, you can't achieve the > same performance if you generate your SQL on the fly as when you just > call a precompiled stored procedure > - by keeping stored procedures very simple, and sticking to the > convention of packing just CRUD + specialized searches into them, plus > using just portable SQL, inasmuch as possible, you can easily switch > databases - in most cases, just copying over the stored procedures > does the trick > - for the same reasons listed for the previous point, the readability > of your application is much improved - reading > "users_getByLogin(:login)" is IMO easier to comprehend than "SELECT * > FROM USERS WHERE loginName = :login", without sacrificing any > performance or portability, compared to using inline SQL statements as > strings > > The consequences of not sticking to the above listed two criteria can > be very bad: > - packing more than reasonable logic into the database makes your > application incomprehensible - for instance > "company_doMonthEndCalculations()" is likely to include such a huge > quantity of logic, that the PHP code calling it is mostly irrelevant, > and you must actually comprehend both the details of the SQL in the > database (in the stored procedures) and the way PHP is connecting them > to understand an application - obviously harder if you have all your > logic in just one place > - using non-portable SQL may be quite a deterrent from porting to a > new database, or may cause a lot more effort than needed, and isn't in > fact justified in most cases > - whereas if packing only very specific and simple operations into > stored procedures allows you to keep the design of the PHP application > very object-oriented, packing very much logic into stored procedures > may cause your PHP code to be just an adapter to an application > written in SQL, instead of being the application itself; SQL being > procedural, your application will have all the flexibility, > extensibility and maintainability problems that a non-OO design causes > > br, > > flj > > -- > Fine counsel is confusing, but example is always clear. (Edgar A. > Guest, The Light of Faith) > > PS: I'm not trying to be a smart-ass, but IMO stored procedures are > underrated (not just by PHP programmers), and it's a pity (and it > leads to sub-optimal applications, and to the development of cures for > the symptoms instead of the cause, at least on other platforms than > PHP), and not letting a database do what it does best is simply > stupid. That's why I try advertising their use whenever I have an > opportunity. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
