Re: OOP to run sequential SQL queries?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux