Prepared Statement vs. Stored Procedure

Question

If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc)

Answer 1

Not familiar with php, but in general stored procedures are already "compiled" so may produce marginally faster performance over an sql statement.

Though my preference usually to stick with SQL from code management/deployment and unit testing perspective.

Answer 2

They are not really the same thing - with stored procedures, your database logic resides inside the database. Prepared statements basically avoid re-parsing queries if they are called multiple times - the performance benefit can vary greatly.

The choice to use one or the other is really dependent on your specific situation. I don't really use stored procs anymore as I like having all of my logic in one place.

Answer 3

The substantial advantage of stored procedures is that your data does not cross a layer (in this case it would be the PHP/MySQL layer) before logic can be applied to it. Some queries may require several select statements, which is slower done through PHP than within MySQL.

Now, as tobyhede points out, it is good to have all logic in one place. But I have worked on projects where it was simply unrealistic to query the required data using PHP; it had to be done through a stored procedure.

Answer 4

May not be the case or worthwhile to mention here, but stored procedures also are "portable" in the case that they're language-agnostic. You can call the same stored procedures on your database from within, say, Java as you would with PHP. Because the procedures reside in the database, anything with access to the database can query them the same way.

Answer 5

Some advantages of stored procedures:

• Portable between languages
• Arguably simplified interface and sometimes performance gains for complex queries and especially multi-query transactions (test!)
• By exposing an interface rather than tables, can be used to improve security and integrity

Some disadvantages of stored procedures:

• Puts business logic into the database - complicates design, extra place to track for version control and troubleshooting
• Performance losses in some situations (test!)
• Less portable between databases

I don't think a single generalized answer exists for this question because there are pros and cons depending on the situation. If you follow principles like simplicity, DRY, testing, and avoiding premature optimization, you're likely to end up fine.

Answer 6

I will start by saying that I do not like the idea of stored procedures, I' rather go the prepared statements route. In this particular case I think you are also comparing apples with oranges...they both there to full fill different functions....

I will only consider stored procedure if the application is 95% database driven only then does it make sense to have some of the logic in the db.

Answer 7

Stored procedures make sense for professional-grade (IE enterprise-grade) applications where you:

1. Want to allow your database engineer to optimize queries for performance
2. Want to abstract complexity of queries to simple API's
3. WANT your logic distributed, because some of what happens in the database might be intellectual property that you don't want to expose to other parties
4. WANT your logic distributed, because that is the nature of distributed, n-tier computing
5. you might want the database engineer or DBA to modify schema without modifying application code (stored procs, by virtue of providing API's, provide a layer of abstraction)

There are other reasons.

Prepared statements are better for work done within a session. But if you are taking the time to create a prepared statement, you have essentially done everything necessary to create a stored procedure. The difference is that the stored procedure is available across multiple sessions (subject to GRANTS in the database).

What I can not figure out is that if you have the option for stored proc vs. prepared statement, why you would bother with prepared statements. Most of the SP vs PS discussions seem to focus on what the differences are between them, not on why to use one vs the other. That always appears to boil down to "depends on what you are trying to do." But I haven't seen a well organized description of: use a proc if you need VS use a statement if you need....