On Sun, Feb 17, 2013 at 10:00 AM, AmirBehzad Eslami <behzad.eslami@xxxxxxxxx> wrote: > We have a bunch of SQL-queries, they should be executed in > a sequential order, with a defensive programming style in mind. > > We were thinking to implement the solution as "Stored Procedures" > instead of a PHP solution that runs SQL queries, but an article in > Coding Horro recommendeds to avoid SP for good reasons: > > http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html As has been said, everything has it's place, every tool has it's use, but we've all used that screwdriver to both open a can of paint and then bang on the lid with handle to get it closed again. Meaning that just because there's a tool to hand, it doesn't always mean it's the right thing. I tend to use stored procs when what I need to get accomplished resides well within the database and utilizes the database engine more effectively, and when such activities might become I/O bandwidth issues if it were all done serially within the application. In other words, very few and far between. If the dataset is large and involves some very complex queries (lots of nested selects and so on), it is often better as a stored proc. But there is a caveat there as well in that often times the skill sets for maintaining complex SQL code can be a lot harder to find and acquire than the skill set for maintaining the same logic in PHP. So, no absolutes, one way or another. > Now we're going to carry on in PHP, and my experience says that > we should write the solution in a procedural-style, instead of OOP. > > Is there any benefit to use OOP in these situations? > > Please share your thoughts. This is actually a very similar question: again, no absolutes. An OO approach is well fit for some aspects of implementation, and a procedural approach to others, and then there is the functional approach, which is yet another way of looking at the problem. Even with that, all three can come into play in any given application. What is to be gained by using an object approach, versus procedural, versus functional? If it's a fairly confined operation, perhaps wrapping it in an object might give some more portability, and if the rest of the application utilizes an object approach, would be a good way to go. If the rest of the application is primarily procedural, then introducing an object approach might become more of a maintenance issue. So, really, it's not a simple answer. There are far too many considerations. Other things to consider: does the entire series of queries need to be performed in a locked state, i.e., if the database records change during the series of queries, will things break? This has very huge performance considerations, and should be taken into account. One of the purported uses of stored procs is that it's easier to implement an atomic operation such as that, but that's really not entirely accurate either. It's quite possible to lock and release while still running the queries sequentially from code. The round-trip factors of I/O do weigh more heavily here. My overall suggestion: Do it the simplest way you can right now, measure, charaacterize, look at how the whole app operates, and refactor what needs to be refactored when you have some actual performance data. Recognize also that "simplest" is pretty subjective based upon you and your fellow devs experiences and points of view. A super DBA person might be able to snap off a stored proc in no time; while an experience PHPer might be able to do the same procedurally. It doesn't mean either one is better than the other. Only putting it into play and actually measuring it will do that. Stumbling over how best to do something is very often less optimal than just implementing and seeing for yourself. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
