Menu
We intend to use mysql stored procedures for handling all database work for one project. There are several applications(different languages) using same database and I am fairly sure that there is nice amount of repeated SQL code across those apps.
And then while reading something about mysq I stumbled upon
http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/
Article basically says that stored procedures are compiled each time when new db connection is made.
Will mysql compile ALL(3 digits number at least) stored procedures on database?
426
MySQL stored procedures are pre-compiled SQL statements stored in a database. They are subroutines containing a name, a parameter list, and SQL statements.
When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes.
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
MySQL supports stored routines (procedures and functions). A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.
No. Query and stored procedures are compiled on demand and then kept in a cache for a while. If you use a specific stored procedure repeatedly the version compiled in the cache will be used, but if it is no longer in cache it must be compiled first, just like any other query you send to a database.
What MySQL certainly will not do is compile all x-hundred stored procedures at once - in case that was what you were afraid of.
But all this is somewhat irrelevant The advantage of stored procedures is that you can make sure that multiple applications using the same database, use it in the same way. When your performance is such that you start looking at the compilation cache for speed, you are looking at the wrong place. Speed optimization is better done through either query or database redesign.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
