Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been wondering if perhaps it would be a more efficient technique in the long run. Are they equivalent (due to being roughly the same amount of processing in the end), or are there cases where one presents a distinct advantage over the other?
-
3Good question & long standing point of discussion.. how much will be in APP side, how much will be done in DB side. You might want to rephrase it to "RDBMS to APP-Layer", because the question applies to all sorts of DB and APP interactions.– Sebastian RothJan 5, 2011 at 10:42
-
2I want to add to this question so bad, but I have nothing of interest atm to add. This is a great question, and one I wouldn't mind becoming a canonical faq– jcolebrand ♦Jan 6, 2011 at 5:08
-
I was actually contemplating asking this question, whether it would have been on-topic here for DBAs. I searched for something completely different and found this one by accident. So +1 for reading my mind– Derek DowneyJan 7, 2011 at 1:10
Add a comment
|
3 Answers
It depends a lot on what you're doing with the records, and what your network architecture looks like. For example, if the stored procedure is going to reduce the overall amount of data transmitted from MySQL to PHP, and you have a relatively slow link between the PHP instance and the database, a SP could certainly help you.
I would recommend you start with some of the 'heavier' manipulations you do to your data on the PHP side, see what you can write as a SP, and then benchmark it.
A point in favor of doing more processing in the database is that the database can't optimize what it doesn't see. As an example, setting a property of a persisted class to a constant value for all instances will definitely perform better if done in the database layer.
A point against is that databases are good at set-based operations, but not so good at row-based operations. For example, something like converting a UTC time to local time based on a time zone varying per row may not perform as well in the database as it might in the app layer.
Usually if the result is an aggregation, (sum, avg, count) it is much faster to be computed in the database server, by avoiding unnecessary communication between the server and the client. Also, ordering and grouping can benefit of indexes, reducing the amount of computation.
