Should PHP developers use MySQL's stored procedures? [closed]

Question

I've been writing asp.net apps with SQL Server back ends for the past 10 years. During that time, I have also written some PHP apps, but not many.

I'm going to be porting some of my asp.net apps to PHP and have run into a bit of an issue. In the Asp.net world, it's generally understood that when accessing any databases, using views or stored procedures is the preferred way of doing so.

I've been reading some PHP/MySQL books and I'm beginning to get the impression that utilizing stored procedures in MySQL is not advisable. I hesitate in using that word, advisable, but that's just the feeling I get.

So, the advice I'm looking for is basically, am I right or wrong? Do PHP developers use stored procedures at all? Or, is it something that is shunned?

Answer 1

Whether to use Stored Procedures or not is more of a religious or political discussion at a bar than not.
What needs to be done is clearly define your application layers and not step over those boundaries. Stored procedures have several advantages and disadvantages over doing queries outside of the database.

Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.

Advantage 3: Stored procedures abstract or separate server-side functions from the client-side. It is much easier to code a GUI application to call a procedure than to build a query through the GUI code.

Advantage 4: Stored procedures are usually written by database developers/administrators. Persons holding these roles are usually more experienced in writing efficient queries and SQL statements. This frees the GUI application developers to utilize their skills on the functional and graphical presentation pieces of the application. If you have your people performing the tasks to which they are best suited, then you will ultimately produce a better overall application.

With all that in mind there are several disadvantages.

Disadvantage 1: Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.

Disadvantage 2: Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in Sp language. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.

Disadvantage 3: Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.

I have probably missed some advantages and disadvantages, feel free to comment.

Answer 2

Could also be because MySQL didn't get stored procedures until version 5. If you use prepared statement you should be okay...just don't use inline SQL