Unanswered: Why Not Use Stored Procedures In MySQL
MySQL v5.x, just starting working with a group of PHP developers and am responsible for database creation. One of the first items I talked about was using 'stored procedures' to database access as I have years of experience with them on other products. I was responded to with No, No, No, it can't do this, it can't do that, etc.
I have read an article about SP's not being compiled into the database which takes away my performance argument, if it's true. I don't trust everything I read and wanted to get as many opinions as possible as I personally like stored procedures for any database interaction.
@pinkfloyd43 - Your question intrigued me a little because I was always told that they increase performance because the data processing was being done by the server. After looking around a bit here is a snipit I got from MySQL that supports your argument that they should look into Stored Procedures.
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.
In your case if it would be say a 1 to 1 ratio then I would think the DB server would be more than cable at producing the results faster.
I agree 100%. There is an article out on the web somewhere, found it yesterday, in regards to mySQL and it NOT precompiling SQL statements and saving on the server. If I recall correctly is indicated that they are stored associated with a connection? Which did not make alot of sense to me.
Performance improvement is one aspect but if you have all your business logic in stored procedures it means that we can then use different programming languages PHP, JSP, C ... that focuses purely on the presentation.