Mysql stored procedures

Question

Hello, I have a unique issue that I need advice on.

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 felling 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?

Thanks in advance.

Answer 1

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

Answer 2

Stored procedures are -- often -- a complete waste of effort.

When in doubt, actually measure the performance. You'll often find that stored procedures add complexity for no recognizable benefit. You may have no performance enhancement from your SP's.

Some folks think they're very "Important". It's essential to actually measure performance rather than quibble or debate.

Answer 3

A couple of years ago I ended up writing a fair amount (~3K lines) of stored procedure code for a PHP/MySQL project. In my experience:

• MySQL stored procedures probably aren't going to help you performance-wise.
• Executing SPs through prepared statements with MySQLi can cause headaches.
• It can be hard to abstract out common patterns—I found myself repeating myself more than I liked.
• Depending on the MySQL version and configuration, you might need SUPER privileges to create SPs.

If you're porting code that uses stored procedures, it might be easiest to keep them. It's certainly possible to use them with PHP and MySQL, and I wouldn't personally call it inadvisable, exactly. I just probably wouldn't choose to use them again if I were starting a new PHP project from scratch.

Answer 4

Many (most?) webapps use a database abstraction layer to take care of injection vulnerabilities/etc.

If you want one for your own app, take a look at PDO. Here is a big tutorial about how to use it: http://www.devshed.com/c/a/PHP/Using-PDO-Objects-in-PHP-5/

Answer 5

In general, I very much dislike stored procedures because:

1. It's too easy to slip in business logic that shouldn't be there.
2. Updates to the application that require updates to your stored procedures is a pain to synchronize, especially if you have to revert to a previous build.

For any database manipulation, I recommend going with a PHP ORM framework like http://www.doctrine-project.org or a framework that includes ORM like CakePHP. You'll have an added bonus of being able to more easily switch between SQL Server and MySQL.

Answer 6

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 7

What about SQL injection ? Procedures allow you to do parameter invocation on the WHERE clause, reducing injection hazards