Should a stored proc try to do more, in the attempt to avoid php mysql back and forth overhead

Question

I have a REST architecture, running PHP on the server side which store and query a Mysql database.

I am re-evaluating one architecture design decision:

DECISION to re-evaluate:

In an attempt to avoid back and forth communications overhead between PHP and Mysql:

1. For some browser requests (for example: 'GET /sessions/4'), one request requires a big response with related objects links.

2. For these situations, I have designed some big mysql stored procedures to query multiple tables. Therefore, for one browser request, PHP launch this one Mysql stored procedure which execute. On PHP result reception, my PHP code goes through each Packet of query results to create objects and link them logically.

3. At the end of this process, PHP query my internal logical view objects to construct the formatted response returned to the browser request.

4. The alternative would be to request multiple stored procedure and launch each of them within PHP instead of within this one stored procedure.

PROBLEMS: It becomes quite hard to manage changes over time. These stored procedure becomes hard to maintain cause each table change, have an impact on one of them.

QUESTION: I have started this design, because many code generated framework are being criticized for their lack of efficiency on communication between the PHP and Mysql layer. Doing those stored procedure by hand, I expected to have the best performance in production, however, the maintenance start to be annoying. Is real life high efficiency architecture attempts to limit the number of PHP-(Mysql stored procedure) back and forth communication or am I attempting to surround a false problem ???

Thanks

Answer 1

You could send the entire contents of a stored procedure to be executed by the database and return the exact same dataset(s) or do the same code execution without losing any performance, but my guess is that's not your problem since you're still maintaining the code in the MySQL language. There are probably a lot of calculations in your stored procedure and other logic that you would like to execute with your language of choice.

Try and do some testing to see where the bottle-neck would be. No one knows for sure because some things are better done in the database (set stuff) and other type of procedural and repeated things are better done in other programming languages.

You could try and consider where your future hardware needs would be to help with performance. Generally, it's easier to throw multiple web and application servers at an application than it is to build a database cluster of servers. This is why in many instances, the database server just has a lot hardware thrown at it to create one big server.

It really is driven by your app needs, but probably more where yours or your team's skill sets are at.