Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
    
Wow. Stored procs debate comes to php. I never thought I'd see the day . . . –  Wyatt Barnett Mar 31 at 21:49
    
I am not a PHP defender, I chose it because it is old and stable, and hopefully with less security issues. Stored procedure was a requirement in the hope of performances. –  Alain Mar 31 at 21:56

1 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.

share|improve this answer
    
I basically set and get stuff. I probably elaborated to much on the history of why I chose this design. However, to simplify my question: Does performance of (S1) executing 1 stored procedure (including many small sp) and interpreting in php all query results at once IS GOOD compared to (S2) executing many stored procedures one at a time each separated by a php interpretation ? Doing (S1) hoping to avoid performance cost of back and forth communication between PHP and mysql... Hope this is clearer. As of hardware, I try to avoid taking this into consideration, for now. –  Alain Mar 31 at 22:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.