MySQL Stored Procedure vs. complex query

Question

How is the performance of a Stored Procedure? Is it worth using them instead of implementing a complex query in a PHP/MySQL call?

Stagecraft

Answer 1

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

Look to indexes and proper table design to get better performance.

Efficacious

Answer 2

Yikes I'd hate for someone to read these answers and get the wrong impression. There are some really important differences between the "Stored Whatever" implementations on "MySQL" vs "SQL server/Oracle".

See: http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

Every person that asks this question assumes something about MySQL’s stored procedure implementation; they incorrectly believe that stored procedures are compiled and stored in a global stored procedure cache, similar to the stored procedure cache in Microsoft SQL Server[1] or Oracle[2].

This is wrong. Flat-out incorrect.

Here is the truth: Every single connection to the MySQL server maintains it’s own stored procedure cache.

Take a minute to read the rest of the article and comments. It's short and you'll have a much better understanding of the issues.

Clangor

Answer 3

As was pointed out to me in a previous answer courtesy of JohnFX:

"The performance benefit of stored procedures is dubious and minimal at best. Some reading material on this point:

http://statestreetgang.net/post/2008/04/My-Statement-on-Stored-Procedures.aspx

http://betav.com/blog/billva/2006/05/are_stored_procedures_faster_t.html

Enjoy.

Kerry

Answer 4

Grossly simplified - Stored procedure performance is equal to or marginally better than code at the cost of db server load. Since most db systems are concerned with multi-user access and are using commodity hardware for the db server, using code offloading the db server will probably win overall. With high end DB servers, > 4 cores, > 32gb ram, SP load is often not an issue.

Stored procedures;

1. transfer less data in the query - minimal speed improvement for well written code
2. parsing & caching is "slightly better" - minimal speed improvement for well written code
3. move the execution load to the db server vs. client(s) (web servers), potentially spreading the load over many systems. - speed improvements are very dependant on the actual code and amount of data including "excess" data transferred. Quite a bit of code transfers more data than is actually used (db libraries, poorly written queries, select *, etc.)

Don't optimize early.

Stored procedures have many other benefits than speed, security being high on the list.

In a single programmer environment, benefits may be offset by SP programming learning curve, SP testing framework, multiple methods of revision control - SP and code, etc..

Learning and using a testing and profiling framework will answer this definitively and will guide you in providing better "performance" to your app than simply choosing SP or ad-hoc queries.

Answer to "is it worth it" - If you don't have a testing/profiling framework in place you'll only be guessing. Any answer based on my code and hardware is probably irrelevant on yours.

My real world experience on many Perl/TCL/PHP/C web apps using DB's (Sybase,Oracle,MS SQL,MySQL,Postgres) stored procs DO NOT greatly improve performance over all. But I still use them often, just for other reasons than performance. They can greatly improve a specific complex query, but that's rarely the bulk of the code and overall processing time.

Cockatrice

Answer 5

In MySQL or any other SQL server as MSSQL or Oracle, stored procedures increase dramatically the speed of the queries involved because this are already compiled. Stored procedures are more secure than direct queries and as object in the database they can be administered by the owner, giving the right access to each user.

Using stored procedures you can also hide the logic of the queries and procedures and give to the development team and other programmers a "black box" in wich they insert params and receive results.

Definitively stored procedures rocks!!!!

From MySQL 5.1 Documentation: Stored routines can be particularly useful in certain situations:

When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

Stored routines also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.

Delineator

MySQL does NOT compile stored procedures prior to executing them in an established session as of current version – Thurlough