Re: Pros/Cons of using mysqli prepared statments

On Nov 4, 2010, at 6:36 AM, Jay Blanchard wrote:

[snip] If you have a query in your PHP code, which you are going to be executing a lot, even if you are using prepared statements, you can go one further by creating a stored procedure. Now the SQL server will only ever need to compile the statement once. No matter how many times it is used. You only need to supply the data which will be type appropriate. [/snip] I second this, using stored procedures has a lot of advantages. If you need to change your SQL you can do it in one spot. It reinforces MVS or modular coding behavior, the SP becomes very re-usable. Security is improved. Performance can be improved. You can put the bulk of the data handling on the database where it really belongs because SP's can perform complex operations complete with control structures. Lastly it simplifies your PHP code.

(dangit, i sent this from the wrong address initially) I do know about stored procedures and have used them where appropriate (retrieving the entire contents of a table, one record from a table, etc.). It was the prepared statements that I haven't had experience with. I wasn't away that these were precompiled. That does make them more attractive for heavily executed pulls. On the other hand, the seem to require more intense maintenance than just changing some lines of code in a file if need be. (I assume prepared statements don't share the same efficiency of maintenance that stored procedures do across applications.)