On Sat, Mar 5, 2011 at 5:31 AM, Florin Jurcovici <florin.jurcovici@xxxxxxxxx > 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 > i dont see the value in this approach, read more below. > - 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 > is this performance advantage enough to merit pushing all the queries to stored procedures? id love to see some benchmarks. > - 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 > i doubt it takes the database long to compile trivial crud queries. i doubt these would see much performance gain implemented as stored procedures rather than generated by a php script. > - 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 > this doesn't make much sense, since in php i can easily write a function users_getByLogin($login) which provides the same readability advantage. after all this would be invoked via the function name throughout the source. > 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 > this makes a lot of sense and gets into the issue of how much application logic if any would be encapsulated in a stored procedure layer. doMonthEndCalculations does sound really scary lol! > - 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 > this is a definite issue. when deciding to implement large chunks of domain logic in stored procedures, it must be considered that the expressiveness of these db scripting languages are not as extensive as the languages used to implement the application layer. from a performance perspective i think pre-compiling large complex queries may be advantageous, but i suspect for trivial queries the difference may be marginal. some benchmarks would be helpful in this area if anyone knows where to find them. -nathan
