Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.